Restoring To A Specific Time

Derik Hammer shows one of the most useful features of database restores:

In order to restore point-in-time, you need to restore the full backup with NORECOVERY. This tells SQL Server not to initiate crash recovery which is a process that performs the redo and undo operations on your database to roll back the uncommitted transactions and roll forward the committed ones.

Once the full backup is restored you will need to restore the rest of the LSN (log sequence number) chain in the appropriate order. If you are not using differential backups, this means that you need to restore each log file until you cover the point-in-time that you are targeting. If you do have one or more differential backups, just restore the most recent differential which was taken before your target point-in-time and then all log backups between then and the target.

Derik also discusses restoring to marked transactions, something I’ve never used before but which could be very useful for known, major changes (like database code rollouts).

Related Posts

Puzzling Through Older Problems

Kenneth Fisher shares a couple of interview puzzles: The year is 2004. You’re taking a tech test as an interview for a SQL development job. They have a page in their application that displays up to 20 rows of information. They need a piece of code that will return the rows from a given page. […]

Read More

The SSMS Magic 8 Ball

Bert Wagner has fun with SSMS: As a kid, I found Magic 8 Balls alluring. There is something appealing about a who-knows-how-many-sides die emerging from the depths of a mysterious inky blue fluid to help answers life’s most difficult questions. I never ended up buying a magic eight ball of my own though, so today […]

Read More


December 2016
« Nov Jan »