Backing up all databases using TSQL Script/Code

Often we need to backup all the databases of an instance for various reasons. While we can use maintenance plans to perform this operation, there are many obvious reasons for preferring a TSQL Script/code to complete this operation in more transparent way than using a plan. 

Please use below codes to automatically backup databases on an instance. You may also schedule the code as a job for routine backup operations

Performing automated full backup of all databases using TSQL Script/Code:

Note:

1. This code will take full backup of all databases unless they are explicitly mentioned to be excluded. For below code, only model and tempdb will not be backed up.
2. Backups will be saved as databasename_yearmonthdate.Bak format (eg. msdb_20100507_diff.BAK)
3. If you take multiple backup of same database using this script in a same day, backup files will be appended. This ensures that backups are not lost.
4. You should have a archiving strategy and delete purge old backups routinely as this code does not delete any backup files.
5. Follow the comments written within the below code to change the backup path and/or change the list of databases which will be excluded from the backup list.

DECLARE @name VARCHAR(50)
DECLARE @path VARCHAR(256)
DECLARE @fileName VARCHAR(256)
DECLARE @fileDate VARCHAR(20)
SET @path = 'S:\Backup\'  -- Here example path is S:\Backup. Use your backpath here.
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR 
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('tempdb','model')  -- Add other database whose backup you do not want to take. Here we are excluding only tempdb
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  
WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
       print @filename
       BACKUP DATABASE @name TO DISK = @fileName
       FETCH NEXT FROM db_cursor INTO @name  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

Performing differential automated backup of all databases using TSQL Script/Code:

Note:

1. This code will take differential backup of all databases unless they are explicitly mentioned to be excluded. For below code, only model and master will not be backed up.
2. Backups will be saved as databasename_yearmonthdate_diff.Bak format (eg. model_20100507_diff.BAK)
3. If you take multiple backup of same database using this script in a same day, backup files will be appended. This ensures that backups are not lost.
4. You should have a archiving strategy and delete purge old backups routinely as this code does not delete any backup files.
5. Follow the comments written within the below code to change the backup path and/or change the list of databases which will be excluded from the backup list.

DECLARE @name VARCHAR(50)
DECLARE @path VARCHAR(256)
DECLARE @fileName VARCHAR(256)
DECLARE @fileDate VARCHAR(20)
SET @path = 'C:\Backup\'  -- Here example path is S:\Backup. Use your backpath here.
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR 
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('tempdb','master')  -- Add other database whose backup you do not want to take. Here we are excluding only tempdb
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  
WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '_diff.BAK' 
       print @filename
       BACKUP DATABASE @name TO DISK = @fileName WITH DIFFERENTIAL 
       FETCH NEXT FROM db_cursor INTO @name  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

Comments

Follower said…
Good writing :)

Popular Posts