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]
You may also like
