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

Memory-Optimized Table Types

Rob Farley hates spelling “optimized” the best way: Let me start by saying that if you really want to get the most out of this feature, you will dive deep into questions like durability and natively-compiled stored procedures, which can really make your database fly if the conditions are right. Arguably, any process you’re doing […]

Read More

Test Those Restores

Bob Pusateri wants you to test your restores, probably right now: I had a client that was hit by a ransomware virus, encrypting several of their systems including the database server. Not to worry, though, they had “full backups” of all the affected machines, done by a third-party backup utility. After taking a day to […]

Read More

Categories

December 2016
MTWTFSS
« Nov Jan »
 1234
567891011
12131415161718
19202122232425
262728293031