SQL Differential backups

Differential backups are cumulative backups that contain the data that has changed since the last Full backup (the active portion of the transaction log). Regardless of the number of Differential backups taken after a Full backup, you can still restore any differential backups on top of the last Full backup.

A Differential (Diff) backup tracks the changed extents at the time of the backup. The time needed to restore a Diff backups increases as the size of the backup becomes bigger. It is best practice to take a new Full backup to reset a new differential base when using Diff backups.

Below script shows example of Differential backup:  

-- Create Database BackupDemoDB
CREATE DATABASE [BackupDemoDB]
ALTER DATABASE [BackupDemoDB] SET RECOVERY FULL

-- Create Full backup
BACKUP DATABASE [BackupDemoDB] TO DISK = N'C:\DiffExample\BackupDemoDB_Full01.bak'
WITH NOFORMAT, NOINIT, NAME = N'BackupDemoDB-Full', SKIP, NOREWIND, NOUNLOAD, STATS = 10

-- Differential backup
BACKUP DATABASE [BackupDemoDB] TO DISK = 'C:\DiffExample\BackupDemoDB_Diff01.bak' WITH DIFFERENTIAL

-- Create Table in Database with Transaction
USE BackupDemoDB
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[RealTempTable]') AND TYPE IN (N'U'))
	DROP TABLE [dbo].[RealTempTable]

-- Create data
CREATE TABLE RealTempTable (ID INT)
INSERT INTO RealTempTable (ID)
SELECT TOP 50000 ROW_NUMBER()
OVER (ORDER BY a.name) RowID
FROM sys.all_objects a
CROSS JOIN sys.all_objects b

-- Differential backup
BACKUP DATABASE [BackupDemoDB] TO DISK = 'C:\DiffExample\BackupDemoDB_Diff02.bak' WITH DIFFERENTIAL

-- Restore Diff database after database drop
DROP DATABASE [BackupDemoDB]
RESTORE DATABASE [BackupDemoDB] FROM DISK = 'C:\DiffExample\BackupDemoDB_Full01.bak' WITH NORECOVERY
RESTORE DATABASE [BackupDemoDB] FROM DISK = 'C:\DiffExample\BackupDemoDB_Diff01.bak' WITH RECOVERY
RESTORE DATABASE [BackupDemoDB] FROM DISK = 'C:\DiffExample\BackupDemoDB_Diff02.bak' WITH RECOVERY
GO
Differential backup ‘Diff02.bak’ can be restored without restoring ‘Diff01.bak’ first. ‘database_backup_lsn’ of value ‘0’ occurs due to a new instance of the database.

Differential backup chain

After each Full backup, the differential base resets and any Differential backups are taken before become incompatible with the latest Full backup. To make an extra Full backup without affecting existing differential backups, use the ‘WITH COPY_ONLY’ option.

BACKUP DATABASE your_database TO DISK = 'full.bak' WITH COPY_ONLY

source – Differential backup

Leave a Reply

Your email address will not be published. Required fields are marked *