How to take Differential backup of all the Databases on SQL Server Instance - SQL Server Tutorial

Scenario:

You are working as SQL Server DBA or developer and you need to prepare script that can take the differential backup of all the databases on SQL Server Instance. 

Below script can be used to take the differential backup of all the database you like. By simply making changes to select query for database name, you can include of exclude the database you take part or not take part in differential backup. I have excluded system database by using database_id>4 in where clause.


USE MASTER
GO
DECLARE @BackupPath varchar(100)
--Provide the backup path 
SET @BackupPath = 'C:\Backup\'
DECLARE @DatabaseName AS varchar(128)

DECLARE Cur CURSOR FOR
--Change the select query for the DBs you like to backup
SELECT
  name
FROM sys.databases
WHERE database_id>4


OPEN Cur
FETCH NEXT FROM Cur INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
  DECLARE @SQL varchar(max) = NULL
  DECLARE @DBNamewithDateTime varchar(128) = NULL
  SET @DBNamewithDateTime = @DatabaseName + '_' + REPLACE(CAST(GETDATE()
       AS date), '-', '') + '_' + REPLACE(CAST(CAST(GETDATE() AS time) 
       AS char(8)), ':', '')

  SET @SQL = 'BACKUP DATABASE [' + @DatabaseName + '] TO  DISK = N''' +
              @BackupPath + '' + @DBNamewithDateTime + '.DIF''
              WITH DIFFERENTIAL ,NOFORMAT, NOINIT,  NAME = N''' +
              @DBNamewithDateTime 
              + '-Diff Backup'',
                 SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10'

  PRINT @SQL
  EXEC (@SQL)
  FETCH NEXT FROM Cur INTO @DatabaseName
END
CLOSE Cur
DEALLOCATE Cur


I execute above script and it took differential backup of all user databases to Backup Folder. You can also use this script in SQL Server Agent and schedule it as per your need.

No comments:

Post a Comment