Restoring SQL Server Databases With Standby

Here's a scenario. A user has made several modifications to a database and now needs to restore the database back to a particular point. The problem is that they don't know the particular time to restore back to, just that they need the database back to before a particular change was made.

If the database is in simple recovery then there's no options to play with, the database can only go back to the last available full backup (and maybe differential backup if they've been taken). If the database is using full recovery (I'm skipping over BULK-LOGGED for this post) then we can then apply the transaction log backups taken after the full backup to get back to a point in time by restoring the database with NORECOVERY and then restoring the necessary log backup files until we reach a particular point either by using .

But one of the disadvantages of NORECOVERY is that it doesn't give us a readable database until we restore with RECOVERY and at that point we can't restore further log backups to our database so if we have missed anything we'd need to start the whole restore process from the beginning.

Thankfully SQL Server gives us an option to restore databases and log files and enable us to read the database between restores using the STANDBY option.

Standby maintains the ability to restore additional transaction logs or differential backups similar to NORECOVERY but also puts the database into read-only mode enabling us to view our database content. This means after each restore we can look into the database, check for our changes and if necessary apply another transaction log restore and when we're happy we can then restore the database with recovery.

Here's a very simple example where I'll restore a full backup of my imaginatively titled Test Database to an even more imaginatively named database called TestCopy. The magic as it were is all in the STANDBY clause highlighted below.

RESTORE DATABASE TestCopy FROM DISK = N'C:\Backup\Test_Full_1312.bak' 

WITH MOVE 'Test' TO 'C:\Data\TestCopy.mdf',

MOVE 'Test_Log' TO 'C:\Logs\TestCopy.ldf',

STANDBY = 'C:\Backup\Test_Standby_1312.bak'

Here's what BOL has to say about the STANDBY file we've just specified in our command:

"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. 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."

Now the database has restored an in SSMS and the database the name has (Standby / Read-Only) next to it which means I can jump right in and query data (updates aren't allowed though) but in addition if I want to restore the next log file I can without having to go back and start again with the full backup.

RESTORE LOG TestCopy FROM DISK = N'C:\Backup\Test_Log_1312_0700.trn' WITH 

STANDBY = 'C:\Backup\Test_Standby_1312.bak'

After the log restore I can check the database content again and if I'm happy I can restore the database with recovery (RESTORE DATABASE TestCopy WITH RECOVERY) to bring it fully online or if further log backups are needed to be restored I can keep using STANDBY like in the example above until my I'm happy the database can be recovered fully. Worth mentioning that the standby file is removed when the database is fully recovered.

Standby is a great feature of SQL Server database restores and if you're thinking that's great but how do I do it in PowerShell because that's what all the cools kids are using these days then you're in luck as Stuart Moore has a great guide to restoring databases including with standby using DBA Tools here!