Restore SQL Database WITH STANDBY (read-only and restorable!)

DBA, TSQL / By

Nearly everyone and their dog have run SQL Server database restores, but very few have heard of RESTORE WITH STANDBY. Here’s a quick overview of what it is, and how to use it!

Docs.Microsoft.com says it pretty well:

“The standby file allows a database to be brought up for read-only access between transaction log restores and can be used with either warm standby server situations or special recovery situations in which it is useful to inspect the database between log restores.”

To put it more succinctly: When you’re restoring log backups, you can put the database in STANDBY (read-only) mode, query the database, then continue restoring log backups.

How to restore the database WITH STANDBY

Let’s assume we’re restoring a database: the full backup, followed by the logs. Our DB is now in the RESTORING state, which means we remembered to restore with the NORECOVERY keyword.

To put the database into a readable state, we use the RESTORE LOG … WITH STANDBY statement:

RESTORE LOG [MyDatabase]
WITH STANDBY = N'\\SQLshare\Serv1\Standby_MyDatabase.tuf';

Data consistency demands that any unfinished business has to be rolled back before we start reading. We want to be able to read some, then restore more logs…so we can’t throw away that “unfinished business”. The standby file we provide lets SQL Server save off those incomplete operations, and put the database into a consistent, readable format.

After we execute the STANDBY statement, we can use the database and run read-only queries against it – SELECT only, no INSERT/UPDATE/DELETE, or other changes! Once we’re done with that, we’ll either continue restoring log backups, or recover the database:

RESTORE DATABASE [MyDatabase] WITH RECOVERY;

Why would I want to restore WITH STANDBY?

There are more or less two situations where we’d want to restore a database WITH STANDBY:

  1. Someone made a change in the database, and we need to find out when it happened.
  2. We’re using log shipping, but I want to keep the log ship target DB readable most of the time.

Scenario 1: Sleuthing

We’re searching for the point in time where someone made a change* to the database:

  1. Restore a copy of the database to a test server.
  2. Restore log files up to the point just before the change may have happened
  3. Run RESTORE WITH STANDBY.
  4. Determine whether the change was made at that point in time, or not. (E.g., “Was the XCRS table dropped at this point?”)
  5. If you haven’t reached the point in time of the change, restore the next log file, and repeat from step 3.

*A change, like, I dunno…someone setting all configurations in a table to NULL. Did it happen before or after the Tuesday deployment, Jonathan? HMM???

Scenario #2: The readable secondary DB

Our shop wants a warm, read-only copy of the database. The excellent news is, they’re fine with the data being up to date as of yesterday’s close of business…log shipping is perfect* for this! Here’s how it goes:

  1. At night, the log restores happen.
  2. Before the business day, the RESTORE WITH STANDBY runs automatically.
  3. Happy business users query the read-only database all day long.

*Log shipping is far simpler than replication, and much less taxing on the network than backing up and restoring a whole database nightly.

Cheers!

Want to work with us? Contact us here!