Backup All User and System Databases with Compression by T-SQL

In previous article we have seen how to backup system databases by creating maintenance plan. Here we will use T-SQL to backup all user and system databases with compression. You can also create SQL job and set the schedule to backup these databases.

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 = 'C:\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 = 'C:\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 = 'C:\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

Reference: Manzoor Siddiqui [www.SQLServerLog.com]