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]
