How do the differential backups work from a restore perspective?
Depending on your backup schedule and the time when the failure occurred would dictate the detailed steps that would need to be taken. If you need to have your current SQL Server databases back up and running, then the full backup would be restored followed by the last differential and then transaction logs if you are issuing them.
If you are trying to keep 2 SQL Servers in sync via differential backups, then at a high level, the full backup would need to be restored followed by the differential backups.
- To take this down a notch, the full backup would need to be restore using the WITH NORECOVERY option.
- The differential backups would also need to be restored using the WITH NORECOVERY option with the exception of the last differential backup (if no transaction log backups need to be restored) which would use the WITH RECOVERY option.
BACKUP DATABASE AdventureWorks TO DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Full.bak'
BACKUP DATABASE AdventureWorks TO DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Diff_1.bak' WITH DIFFERENTIAL
BACKUP DATABASE AdventureWorks TO DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Diff_2.bak' WITH DIFFERENTIAL
BACKUP DATABASE MyTechMantra
TO DISK = 'C:\DBBackups\MyTechMantra.DIF'
WITH DIFFERENTIAL, NOFORMAT, NOINIT,
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 25
BACKUP DATABASE [MyDB]
TO DISK = N'D:\DBBackups\MyDB_20200729_01.DIF' WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'Differential Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
- After a full database backup, the differential database backup should be relatively quick.