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