SQL Server Interview Question #192

How do you restore a SQL Server database?

Security, Backup, Recovery, Administration & Real-World Scenarios Senior Advanced

Detailed Explanation

A restore sequence depends on the recovery model and available backup chain. A common point-in-time sequence restores the full backup WITH NORECOVERY, optionally restores the appropriate differential WITH NORECOVERY, restores required transaction log backups in sequence, and finally recovers the database.

WITH NORECOVERY keeps the database ready to accept additional restore operations. WITH RECOVERY brings it online and ends the restore sequence.

Restore procedures must be tested regularly; having backup files is not proof that recovery objectives can actually be met.

Code Example

RESTORE DATABASE SalesDb
FROM DISK = 'D:\SqlBackups\SalesDb_Full.bak'
WITH NORECOVERY;

RESTORE LOG SalesDb
FROM DISK = 'D:\SqlBackups\SalesDb_Log.trn'
WITH RECOVERY;