All 7 Backup & Recovery questions
More in Backup & Recovery
What are the three main types of backups in SQL Server, and what does each one capture? How do you restore a SQL Server database from a full backup file? What are the three recovery models available in SQL Server, and how do they affect your backup strategy? How do you automate regular backups in SQL Server so they run consistently without manual effort? How would you design a comprehensive backup and recovery strategy for a large, business critical database with strict recovery time and recovery point objectives? How do you verify that a backup file is not corrupted and can actually be restored successfully?
Advertisement
SQL Server › Backup & Recovery

How would you perform a point in time restore to recover a database to the exact moment just before an accidental data deletion?

Intermediate

Answer

You restore the most recent full backup with the NORECOVERY option, then restore any differential backup if one exists, and finally restore the transaction log backups in order, using the STOPAT option on the final log restore to specify the exact date and time just before the accidental deletion occurred.
RESTORE DATABASE MyDatabase FROM DISK = 'Full.bak' WITH NORECOVERY;
RESTORE LOG MyDatabase FROM DISK = 'Log1.trn' WITH NORECOVERY;
RESTORE LOG MyDatabase FROM DISK = 'Log2.trn' WITH STOPAT = '2026-09-06 14:30:00', RECOVERY;
Real-world example A finance team accidentally deletes an important table of transactions at 2:35pm, and the database administrator restores the database to 2:30pm using a point in time restore, recovering all the lost data with minimal impact.

Common follow-ups: What happens if you specify a STOPAT time that is after the last available transaction log backup?;Why is the order of restoring full, differential, and log backups important?

Transactions & ACID;Isolation & Locking
All 7 Backup & Recovery questions