A customer had a question about restoring with standby, so I wrote a quick post to explain how this works.
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
The Scenario
Sometimes you want to restore part of your data, but you still want the option to continue restores. A classic example of this is when you are restoring a number of transaction logs and want to check the data to find a place where certain values haven’t been changed.
Suppose someone deletes a bunch of data between 10am and 11am from the supplier table. You know that they added “Acme” to this table before the delete. You might restore up to 10am and check the supplier table for the old data and look for Acme. If it’s not there, maybe you restore the 10:05am log backup and check again. If it’s not there, then the 10:10am log, etc.
SQL Server lets you query a restored, but not recovered database with the STANDBY option. If you use NORECOERY, you can’t query the data. I won’t delve into the technical process in this blog, but for now, this is what we want to do: query a restored db, but not prevent future restores.
Setup
First, create a database and then take a backup. I created the “sandbox” database in my system and backed it up. I won’t cover that. Now, let’s set up a restore. I’ll choose a new name, since I’m looking for data. Here’s the statement I picked:
USE [master]
RESTORE DATABASE [sandbox4] FROM DISK = N'D:SQLBackupNew foldersandbox_20210308.bak' WITH FILE = 1,
MOVE N'Sandbox' TO N'D:SQLServerDataSQL2017Sandbox4.mdf',
MOVE N'Sandbox_log' TO N'D:SQLServerDataSQL2017Sandbox4_log.ldf',
STANDBY = N'D:SQLBackupNew foldersandbox_RollbackUndo_2023-08-07_11-51-39.bak', NOUNLOAD, STATS = 5
GO
This is complex, and I haven’t memorized this syntax. Plus I don’t want to type all those paths. Instead, I use SSMS. I’ll set up the restore there. Here are the three screens. Note in the first, one I’ve pointed to the “Script button”, which is what I pushed to get this command.
I’ve picked the device here and changed the restore to database name.
File options. Make sure the filenames don’t conflict with existing ones.
Set the standby option and remove the tail log backup if this is the same instance as the original database.
Restoring and Querying
When I run this command, I’ll get this type of output. I like getting stats, in case something sticks.
In the object explorer, I’ll also see the database as standby/read-only.
If I open a query window, I can get data from this database.
However, I can’t update tables.
Summary
This is a quick look at how to get a database in standby. I covered one use case above, but not all the ways in which you use standby or what’s happening behind the scenes. If you need to query a database in the middle of the restoring state, use the standby option.
SQLNewBlogger
This post took me about 15 minutes to assemble. I set this up and tested it for a client, then I had to redo the work with screen shots and add the text. It’s not too long, but this is a good example of how I set up a post that leads to others. I need to create these posts:
- what happens with the standby option
- restoring more backups to this database with standby
- restoring more backups to this database without standby
- coming out of standby mode
- automating this to look for a data change
You could do this and showcase your knowledge of this feature and how you might use it