Tags

, , ,


Today in the morning I received a call from one of my new DBA friend who wanted to confirm if Differential Backups can be restored in Simple Recovery mode. The answer is “YES”.

I realized that many DBAs don’t understand that we can actually restore differential backups in Simple Recovery Mode. Differential backup is simply the data pages that have changed since the last full backup.

No transaction log is needed.

Let’s see an example:

–Restoring Full Backup in NORECOVERY Mode
RESTORE DATABASE AdventureWorksLT2008
FROM DISK=‘D:\SQLBackups\AWFull.bak’
WITH NORECOVERY

— Restoring Differential Backup in NORECOVERY Mode

RESTORE DATABASE AdventureWorksLT2008
FROM DISK=‘D:\SQLBackups\AWDiff.dif’
WITH NORECOVERY

— Bringing the database ONLINE

RESTORE DATABASE AdventureWorksLT2008
WITH RECOVERY

Thanks

Subhro Saha

 

Advertisements