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 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