Important Scripts

Backup All User and System Databases

If you want to backup all system and user databases together you can do the same with below SQL script.

DECLARE @DB VARCHAR(20)
DECLARE @BkpFName VARCHAR(100)
DECLARE @BkpFDate VARCHAR(50)

SELECT @BkpFDate = REPLACE(CONVERT(VARCHAR(50), GETDATE(), 102),'.','') + REPLACE(CONVERT(VARCHAR(50), GETDATE(), 108),':','')

DECLARE Cursor_DBs CURSOR FOR

SELECT name
FROM master.sys.DATABASES
WHERE name != 'tempdb'

OPEN Cursor_DBs
FETCH NEXT FROM Cursor_DBs INTO @DB

WHILE @@FETCH_STATUS = 0

BEGIN
SET @BkpFName = 'F:\Rajendra\BackupFolder\' + @DB + '_FullBackup_' + @BkpFDate + '.bak'
BACKUP DATABASE @DB TO DISK =  @BkpFName  WITH COMPRESSION
FETCH NEXT FROM Cursor_DBs INTO @DB
END

CLOSE Cursor_DBs
DEALLOCATE Cursor_DBs

Backup All User Databases

If you want to backup only user databases and don’t want to include system databases then you can use below SQL script.

DECLARE @DB VARCHAR(20)
DECLARE @BkpFName VARCHAR(100)
DECLARE @BkpFDate VARCHAR(50)

SELECT @BkpFDate = REPLACE(CONVERT(VARCHAR(50), GETDATE(), 102),'.','') + REPLACE(CONVERT(VARCHAR(50), GETDATE(), 108),':','')

DECLARE Cursor_DBs CURSOR FOR

SELECT name
FROM master.sys.DATABASES
WHERE name NOT IN ('master','model','msdb', 'tempdb')

OPEN Cursor_DBs
FETCH NEXT FROM Cursor_DBs INTO @DB

WHILE @@FETCH_STATUS = 0

BEGIN
SET @BkpFName = 'F:\Rajendra\BackupFolder\' + @DB + '_FullBackup_' + @BkpFDate + '.bak'
BACKUP DATABASE @DB TO DISK =  @BkpFName  WITH COMPRESSION
FETCH NEXT FROM Cursor_DBs INTO @DB
END

CLOSE Cursor_DBs
DEALLOCATE Cursor_DBs

Backup All System Databases

If you want to backup all system databases master, model and msdb then use below SQL script.

DECLARE @DB VARCHAR(20)
DECLARE @BkpFName VARCHAR(100)
DECLARE @BkpFDate VARCHAR(50)

SELECT @BkpFDate = REPLACE(CONVERT(VARCHAR(50), GETDATE(), 102),'.','') + REPLACE(CONVERT(VARCHAR(50), GETDATE(), 108),':','')

DECLARE Cursor_DBs CURSOR FOR

SELECT name
FROM master.sys.DATABASES
WHERE name IN ('master','model','msdb')

OPEN Cursor_DBs
FETCH NEXT FROM Cursor_DBs INTO @DB

WHILE @@FETCH_STATUS = 0

BEGIN
SET @BkpFName = 'F:\Rajendra\BackupFolder\' + @DB + '_FullBackup_' + @BkpFDate + '.bak'
BACKUP DATABASE @DB TO DISK = @BkpFName WITH COMPRESSION
FETCH NEXT FROM Cursor_DBs INTO @DB
END

CLOSE Cursor_DBs
DEALLOCATE Cursor_DBs