Restoring WITH STANDBY

What? Not WITH NORECOVERY??

Catching up on my own training this week, I've learnt a new thing.

While restoring databases, the WITH STANDBY option rolls transactions back and then forward between transaction log restores.

Let me explain...

Lets setup a demo

                    Use [master]

-- Create Database
CREATE DATABASE TestRestoreDB;

-- Make sure it's in Full Recovery mode
ALTER DATABASE TestRestoreDB SET RECOVERY FULL;

USE [TestRestoreDB]

-- Create a dummy test table
CREATE TABLE dbo.TestTable (Col1 INT);

-- Create some rows from last week
INSERT INTO dbo.TestTable (Col1) VALUES (1)
INSERT INTO dbo.TestTable (Col1) VALUES (2)
INSERT INTO dbo.TestTable (Col1) VALUES (3)
                

We have a new database 'TestRestoreDB' in Full Recovery mode and a table 'TestTable' with some dummy data.

Nightly Backup

Like good, honest DBAs, we do a full backup of our database overnight.

                    
-- Nightly Full backup
BACKUP DATABASE TestRestoreDB
TO DISK = 'C:\SQLBackups\TestRestoreDB_Full.bak'
WITH INIT, FORMAT, COMPRESSION
                    
                

Just ignore that I'm backing up to the C:\ drive. It's not a fun place to do backups to.

I'm using my laptop to run the demo and it's only got a C:\ drive.

As a smart DBA, you'll be backing up to a much smarter location!!

Someone changed Something.

Around 8:10am and 8:20am, someone in our company added some data to our table:

                    
-- Make some changes in the morning
INSERT INTO dbo.TestTable (Col1) VALUES (810)
INSERT INTO dbo.TestTable (Col1) VALUES (820)
                    
                

9am Transaction Log Backup

Because we don't trust hardware, software, users or anything else, we backup our TLogs hourly

                    
-- Hourly Log backup
BACKUP LOG TestRestoreDB
TO DISK = 'C:\SQLBackups\TestRestoreDB_Monday9am.trn'
WITH INIT, FORMAT, COMPRESSION
                    
                

More Changes

What do you know, those darn users keep changing things.

But this time, the user's transaction doesn't complete before we take our hourly TLog backup

                    
-- Make some more changes
INSERT INTO dbo.TestTable (Col1) VALUES (910)
INSERT INTO dbo.TestTable (Col1) VALUES (920)

-- Start a transaction that doesn't complete before the backup happens
BEGIN TRANSACTION TestInsert
INSERT INTO dbo.TestTable (Col1) VALUES (931)
INSERT INTO dbo.TestTable (Col1) VALUES (932)
                    
                

10am Transaction Log Backup

Our hourly TLog backup runs on time

                    
-- Hourly Log backup
BACKUP LOG TestRestoreDB
TO DISK = 'C:\SQLBackups\TestRestoreDB_Monday10am.trn'
WITH INIT, FORMAT, COMPRESSION
                    
                

Transaction Completes

A split-second later, the user's transaction completes

                    
-- Complete that transaction
COMMIT TRANSACTION TestInsert
                    
                

11am Transaction Log Backup

Just for completeness, our hourly TLog backup runs again

                    
-- Hourly Log backup
BACKUP LOG TestRestoreDB
TO DISK = 'C:\SQLBackups\TestRestoreDB_Monday11am.trn'
WITH INIT, FORMAT, COMPRESSION
                    
                

Where We're At

• Full backup with last week's rows 1, 2 & 3
• 9am TLog backup with rows 810 & 820
• 10am TLog backup with rows 910, 920 and the uncommitted transaction for rows 931 and 932
• 11am TLog backup with 931 & 932 committed

Disaster Strikes!!!

Some sysadmin buffoon decides the core business database isn't worth the storage it's sitting on, and deletes it.

Who in their right mind would delete a database called TestRestoreDB??

                    
---------------------------
-- Catastrophic FAILURE
USE [master]
ALTER DATABASE TestRestoreDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE TestRestoreDB 
---------------------------
                    
                

Let's RECOVER this Situation

We have a couple of options: WITH NORECOVERY or WITH STANDBY between TLog restores.

What's the difference?

Most notably, a Transaction Log restore WITH NORECOVERY leaves the database unreadable, WITH STANDBY leaves the database READ-ONLY between restores.

If you need to roll forward through Transaction Log backups to find when a user started deleting a bunch of data from your database and the best estimates they can give you are: 'sometime yesterday', WITH STANDBY becomes a good option.

You can check the database after each restore to narrow down the time frame you're looking at.

Lets look at both restores...

WITH NORECOVERY

                    
-- Restore Full WITH NORECOVERY
RESTORE DATABASE TestRestoreDB
FROM DISK = 'C:\SQLBackups\TestRestoreDB_Full.bak'
WITH REPLACE, NORECOVERY

--Restore Tlog WITH NORECOVERY
RESTORE DATABASE TestRestoreDB 
FROM DISK = 'C:\SQLBackups\TestRestoreDB_Monday9am.trn' 
WITH NORECOVERY
                    
                

Can we see our missing data?

                    
-- Can we see the data?
SELECT tt.Col1 FROM TestRestoreDB.dbo.TestTable tt
                    
                

What State is the database in?

                    
-- What state is the database in?
SELECT name, user_access_desc, state_desc, is_in_standby FROM sys.databases WHERE name = 'TestRestoreDB'
                    
                

We can see that the database is unreadable. The database is also showing as 'RESTORING'.

Our only option is to complete the restore process:

                    
-- Complete the retore process
RESTORE DATABASE TestRestoreDB FROM DISK = 'C:\SQLBackups\TestRestoreDB_Monday10am.trn' WITH NORECOVERY
RESTORE DATABASE TestRestoreDB FROM DISK = 'C:\SQLBackups\TestRestoreDB_Monday11am.trn' WITH RECOVERY

-- Read our Data
SELECT tt.Col1 FROM TestRestoreDB.dbo.TestTable tt

-- What state is the database in?
SELECT name, user_access_desc, state_desc, is_in_standby FROM sys.databases WHERE name = 'TestRestoreDB'
                    
                

Once we have issued the WITH RECOVERY option on our last restore, we can now read the data.

WITH STANDBY

                    
-- Restore WITH STANDBY
RESTORE DATABASE TestRestoreDB
FROM DISK = 'C:\SQLBackups\TestRestoreDB_Full.bak'
WITH REPLACE, STANDBY = 'C:\SQLBackups\TestRestoreDB.Standby'
                    
                

Can we see our missing data?

                    
-- We see last week's data - 1, 2, 3
SELECT tt.Col1 FROM TestRestoreDB.dbo.TestTable tt
                    
                

What State is the database in?

                    
-- What state is the database in?
SELECT name, user_access_desc, state_desc, is_in_standby FROM sys.databases WHERE name = 'TestRestoreDB'
                    
                

Can we change data?

                    
-- Can we change data?
INSERT INTO TestRestoreDB.dbo.TestTable (Col1) VALUES (999)
                    
                

We can see that the database is READ-ONLY. The database is also showing as 'ONLINE'.

If the data we're looking for isn't in this restore we can try the next TLog restore:

                    
-- Restore 9am
RESTORE DATABASE TestRestoreDB 
FROM DISK = 'C:\SQLBackups\TestRestoreDB_Monday9am.trn' 
WITH STANDBY = 'C:\SQLBackups\TestRestoreDB.Standby'

-- We now see 810 & 820 from the morning
SELECT tt.Col1 FROM TestRestoreDB.dbo.TestTable tt
                    
                

Still not the data we're looking for? Lets restore the next TLog backup.

                    
-- Restore 10am
RESTORE DATABASE TestRestoreDB 
FROM DISK = 'C:\SQLBackups\TestRestoreDB_Monday10am.trn' 
WITH STANDBY = 'C:\SQLBackups\TestRestoreDB.Standby'		-- Same Standby file

-- We now see 910 & 920 from between 9am and 10am
-- But NOT 931 & 932 from the open transaction
SELECT tt.Col1 FROM TestRestoreDB.dbo.TestTable tt
                    
                

Whoa!!! We're missing 931 and 932. They were inserted, although the transaction wasn't committed.

Turns out, the uncommitted transaction was rolled into the STANDBY file ready for the COMMIT in the the next TLog restore.

                    
-- Restore 11am
RESTORE DATABASE TestRestoreDB 
FROM DISK = 'C:\SQLBackups\TestRestoreDB_Monday11am.trn' 
WITH STANDBY = 'C:\SQLBackups\TestRestoreDB.Standby'

-- We now see 931 & 932 from the rolled forward transaction
SELECT tt.Col1 FROM TestRestoreDB.dbo.TestTable tt
                    
                

And there they are.

Just for completeness, lets finish our restore WITH RECOVERY to make the database READ-WRITE again and confirm the database status.

                    
-- Finally complete the restore process
RESTORE DATABASE TestRestoreDB WITH RECOVERY

-- To confirm
SELECT name, user_access_desc, state_desc, is_in_standby FROM sys.databases WHERE name = 'TestRestoreDB'
                    
                

And everything's looking good in the world again.

Conclusion

When you restore WITH STANDBY, you won't see uncommitted transactions in the READ-ONLY version of the database. When you restore the next Transaction Log backup in turn, those committed transactions get rolled forwards.

A restore WITH NORECOVERY doesn't have to deal with uncommitted transactions so may provide a faster recovery time if you know where you need to stop. Especially if you have long-running or large transactions.

Addendum

I completed this blog post, and then started thinking... Can you mix and match WITH NORECOVERY and WITH STANDBY restores if you know roughly when you need to find your missing data?

Yes you can!

                    
-- Restore Full WITH NO RECOVERY
RESTORE DATABASE TestRestoreDB
FROM DISK = 'C:\SQLBackups\TestRestoreDB_Full.bak'
WITH REPLACE, NORECOVERY

-- Restore TLog WITH NO RECOVERY
RESTORE DATABASE TestRestoreDB 
FROM DISK = 'C:\SQLBackups\TestRestoreDB_Monday9am.trn' 
WITH NORECOVERY

-- Restore TLog WITH STANDBY
RESTORE DATABASE TestRestoreDB 
FROM DISK = 'C:\SQLBackups\TestRestoreDB_Monday10am.trn' 
WITH STANDBY = 'C:\SQLBackups\TestRestoreDB.Standby'

-- We have Data!
SELECT tt.Col1 FROM TestRestoreDB.dbo.TestTable tt

-- Complete the restore WITH RECOVERY
RESTORE DATABASE TestRestoreDB 
FROM DISK = 'C:\SQLBackups\TestRestoreDB_Monday11am.trn'
WITH RECOVERY
                    
                

Nice!