When restoring a database there are times when it would nice to restore a bit, check what’s been restored so far, restore a bit more etc. However, traditionally most people only know RECOVERY and NORECOVERY. This means that you restore to a point in time. Check the data and start from scratch again with a FULL recovery. Repeat until happy (well depending on the DB size you probably won’t be all that happy).

So first a couple of definitions:

• NORECOVERY – Leave the database in a restoring state. You can’t read or write to the database but you can continue restoring additional backups.
• RECOVERY – Complete the restore. The database is now ready for normal use but nothing else can be restored to it without starting over with a full backup.

Here’s the difference: One of the final steps in restoring a database is to roll back all incomplete transactions. The transactions that weren’t committed at the time of the backup. This way when the database becomes available it’s in a consistent state. When you include NORECOVERY in your restore it tells SQL not to roll forward the incomplete transactions. That way when the remainder of the transactions in the new backup are written to the database everything is correct.

For example a transaction contains four rows, but only one of them was written to disk at the time of the full backup. The next log backup catches the remaining three rows. Here are the options we’ve talked about so far:

• The full backup is run with RECOVERY. That one row in the incomplete transaction is rolled back. If at this point we were able to restore the log backup (don’t worry, we can’t) we would have a problem. Only the last three rows of the transaction would exist leaving us with bad data.
• The full backup is run with NORECOVERY. The one row in the incomplete transaction is not rolled back. What if we were able to read from the database at this point (still don’t worry, we can’t do this either)? We would be able to see the single row of the transaction. Again bad data. But now if restore the log backup we add the remaining three rows of the transaction and have a whole transaction (this we can do, thank goodness).

So we want something in the middle. We don’t want to be able to see partial transactions, but we need them there so we can continue to restore additional log files.

This is where we use the STANDBY option. Per BOL:

The standby file is used to keep a “copy-on-write” pre-image for pages modified during the undo pass of a RESTORE WITH STANDBY.

It then leaves the database in a read-only state. So now you can restore the database back to a point in time (even mid log backup). Check the data. Restore a few minutes into the future. Check the data again. Over and over again until you are where you need to be. It’s still going to be tedious but better than doing the full restore over again each time you need to check, right?

On top of that we can use the same idea and combine it with log shipping. Now you not only have a spare in case of a DR situation but that spare can be read only most of the time (except when actually restoring). You can use it to run reports, ad-hoc queries, etc. (Don’t use it for CHECKDBs.)

A couple of other important notes about STANDBY.

• Standby will not work during an online restore option.
• Standby will not work if an upgrade is required on the database.
• If the standby file is deleted then the only option is another full restore. The standby file is not locked except during the actual restores so this is easy to do if you aren’t careful.

Filed under: Backups, SQLServerPedia Syndication Tagged: backups, microsoft sql server, restores