Differential backup of all the Databases on SQL Server

Based on the most recent, previous full data backup, a differential backup is created. Data that has changed since the full backup is the only data that is captured in a differential backup. The base of the differential refers to the entire backup that a differential backup is built upon.

Take any database differential backups you like. You can include or remove a database from differential backup by simply making changes to the select query for the database name. 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