How to Perform Point-in-Time Recovery of a SQL Server Database
In a previous post in the backup and restore series, How to Restore Databases From Native SQL Server Backups, Tim mentioned some more advanced options when restoring a database backup, including performing point-in-time recovery of a SQL Server database (sometimes known as PITR).
In this tutorial, I’ll build on the information in Tim’s post by showing you how to use backups to perform point-in-time recovery and a more advanced way to determine an exact point to restore to.
What Are the Use Cases for Point-in-Time Recovery?
Usually, when a disaster happens and a restore is required, the database is restored to the most recent possible time to minimize the amount of data lost. Occasionally, though, there’s a need to restore a database to a different point in time. The most common cause of this, in my experience, is someone has made a mistake—such as accidentally dropping a table or deleting data—and needs help from the database administrator to rectify it.
Another reason could be the need to examine data at a certain point in time, such as for month-end processing. In this case, however, it may be better to plan and create a database snapshot at the desired time, which then preserves a “snapshot” view of the database until the database snapshot is dropped... but this is a topic for a future post.
Restoring a SQL Server Database to a Known Point in Time
As with any restore sequence, you must first restore a full backup, so the full backup should be one from before the specific point in time and restored using the NORECOVERY option. A differential backup can also be part of the point-in-time restore sequence and again should be from before the specific point in time and restored using NORECOVERY.
The actual point-in-time restore is performed using transaction log backups and the STOPAT option. Here’s an example:
Notice I didn’t use the NORECOVERY option. When performing point-in-time recovery, the database automatically remains in the restoring state if the specific time isn’t included in the log backup. The database will recover only if the desired point in time is reached. Incidentally, the exact point in time the database is recovered to is the most recent transaction commit point before the desired point in time. Any transactions not committed by the specified point in time go through rollback, and their effects won’t be present in the recovered database.
Example Restore Sequence
As an example, let’s pretend someone accidentally deleted a table sometime between 02:25 and 02:30 on Monday, November 22, there’s no activity in the database between midnight and 4:00 a.m. every day, and the backup strategy is as follows:
- Weekly full database backup every Sunday at 01:00
- Daily differential database backup every day at 01:00
- Hourly transaction log backups
As we know, the database is inactive during the time in question. To retrieve the table, we can restore to a point a few minutes before the accidental drop, say 02:20. If you’re restoring on the same server, you don’t want to overwrite the existing database, so you’ll need to change the name of the database being restored. You can use the WITH MOVE option described in this previous post on how to restore databases from SQL Server backups to specify new locations for the data files and log files. Let’s assume we’re restoring on a different server with the same drive layout as the production server to keep our example simple.
I always prefer to do things manually instead of using the wizards in Microsoft SQL Server Management Studio. Here’s the restore sequence, based on the backup strategy described above:
You’ll notice I included the STOPAT option when restoring the two database backups, although they don’t have any effect—these backups are always restored in their entirety. I put the STOPAT clause on the database backups to make sure they’re the correct ones, as SQL Server will report an error if not. For instance, if the full backup used is after the desired point in time, the error will be as follows:
Msg 4338, Level 16, State 1, Line 158
The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.
Msg 3013, Level 16, State 1, Line 158
RESTORE DATABASE is terminating abnormally.
And if you don’t use the STOPAT clause on every transaction log backup, you might accidentally go past the desired point in time. In this case, if you decided only to use the STOPAT clause on the final log backup and you chose it incorrectly, you’ll see this error:
Msg 4335, Level 16, State 1, Line 164
The specified STOPAT time is too early. All or part of the database is already rolled forward beyond that point.
Msg 3013, Level 16, State 1, Line 164
RESTORE LOG is terminating abnormally.
If this happens, you have no choice but to start the restore sequence again.
For each transaction log backup you restore not containing the desired point in time, SQL Server will let you know with the following message:
This backup set contains records logged before the designated point in time. The database is left in the restoring state, so more roll forward can be performed.
Restoring a SQL Server Database to an Unknown Point in Time
It’s more common someone dropped a table when the database had activity in it or at a time they can only roughly estimate. In our scenario above, how can we find the exact time between 02:25 and 02:30 the table was dropped?
The most obvious solution is to restore the database to, say, 02:25 and see if the table is there. If it is, restore again to 02:26 and see if it’s there. If so, restore again, moving forward another minute. Eventually, you’ll find the one-minute interval where the table was dropped. Then repeat the process using 10-second intervals. And so on.
I call this tedious process “inching through the log.” This process can take a long time because you have to restart the restore sequence each time, and it can take even longer if you have to find the exact time the table was dropped.
However, using the STANDBY option can help speed the process up by temporarily recovering the database without using the transaction log. Instead, the log records necessary to perform the temporary recovery are written to a specified, separate file. For example:
The STANDBY option temporarily recovers the database and allows read-only access to it. You can then check to see if the table is there or not. If it is, you can restore forward a little more without restarting the restore sequence. SQL Server essentially reverses the temporary recovery and then continues restoring more transaction logs, allowing you to automate the process of finding when the table was dropped. For instance, you could do something like the following:
… and so on, using whatever granularity you want as the time increment moves forward. This can also be made into a script designed to increment the time in a loop, build the restore string, and use EXEC to execute it.
Finding the Exact Point in Time When a Table Was Dropped
Even with the method above, you still have to build the script, find the time to within a few seconds, and then run the script, incrementing by a millisecond at a time to find the exact time.
There’s a much simpler solution. If you know the time interval when the table was dropped down to, say, 10 seconds, you can restore to the end of this time period and look in the transaction log to find the exact point in the log (called a Log Sequence Number, or LSN) where the table drop transaction started. Instead of using STOPAT with a time, you can then use STOPBEFOREMARK with the LSN of the drop table transaction to recover the database up to, but not including, the table drop transaction, which guarantees you have the table exactly at the point it was dropped.
Using our scenario again, let’s say you determine the table was dropped between 02:26:10 and 02:26:20. First, you restore the database to 02:26:20, and then run code to look in the log for drop table transactions. SQL Server automatically (and conveniently) labels all such transactions with the name “DROPOBJ,” so you can search using the following code:
And you’ll get a result like this:
Then, instead of using STOPAT with a time on each RESTORE, you can use the LSN as the parameter for STOPBEFOREMARK in the same restore sequence. Here’s an example of the final log restore to show you the syntax:
You must copy the exact output from the log query and put “lsn:0x” before it for the command to work. This neat trick can save you a lot of time during your data recovery process.
Importance of Using Advanced Options for Point-In-Time Recovery
Though any DBA should be able to perform backups and construct a restore sequence when a disaster occurs to minimize data loss, knowing how to use some of the more advanced options during a restore operation can set you apart from your peers. The ability to perform point-in-time recovery of a database to a point in the past is another reason to think carefully about your backup retention policy, so you don’t discard backups that could be useful if a problem is discovered weeks later.
SolarWinds
SQL Sentry is built to provide the insights you need to inform your backup strategy, such as generating a report to show which databases in your environment don’t have recent backups. With more than 25 report templates included out-of-the-box, SQL Sentry can help you more easily define baselines, spot bottlenecks, and stay on top of your database performance.