To restore a Differential Backup, you must first restore the Full Backup on which the differential is based.
Example Backup Schedule
01-Jun 12:00 AM Full Backup
02-Jun 12:00 AM Differential Backup
03-Jun 12:00 AM Differential Backup
04-Jun 12:00 AM Differential Backup
Suppose the database crashes on 04-Jun at 10:00 AM.
You only need:
1. Full Backup (01-Jun)
2. Latest Differential Backup (04-Jun)
You do NOT restore the 02-Jun and 03-Jun differential backups.
Using SSMS GUI
Step 1: Restore Full Backup
Right-click Databases
Restore Database
Device → Select Full Backup (.bak)
In Options:
Check Restore WITH NORECOVERY
This leaves the database in a restoring state.
Step 2: Restore Differential Backup
Restore Database again
Select Differential Backup file
In Options:
Check Restore WITH RECOVERY
This brings the database online.
Using T-SQL
Restore Full Backup
RESTORE DATABASE MyDB
FROM DISK = 'F:\Backup\MyDB_Full.bak'
WITH NORECOVERY;
Restore Differential Backup
RESTORE DATABASE MyDB
FROM DISK = 'F:\Backup\MyDB_Diff.bak'
WITH RECOVERY;
If You Also Have Transaction Log Backups
Example:
01-Jun 12:00 AM Full Backup
04-Jun 06:00 AM Differential Backup
04-Jun 07:00 AM Log Backup
04-Jun 08:00 AM Log Backup
04-Jun 09:00 AM Log Backup
Crash at 09:30 AM
Restore sequence:
-- Full
RESTORE DATABASE MyDB
FROM DISK='Full.bak'
WITH NORECOVERY;
-- Differential
RESTORE DATABASE MyDB
FROM DISK='Diff.bak'
WITH NORECOVERY;
-- Log backups
RESTORE LOG MyDB
FROM DISK='Log_0700.trn'
WITH NORECOVERY;
RESTORE LOG MyDB
FROM DISK='Log_0800.trn'
WITH NORECOVERY;
RESTORE LOG MyDB
FROM DISK='Log_0900.trn'
WITH RECOVERY;
Restore order is always:
Full Backup
↓
Latest Differential Backup
↓
All Log Backups after that Differential
↓
Recovery
Important
A Differential Backup is tied to the most recent Full Backup that existed when it was created. If you lose the Full Backup file, the Differential Backup alone cannot be restored.
Sign up here with your email
ConversionConversion EmoticonEmoticon