Reading the content of the databse while restoring backup-sets: Restore with STANDBY

Assume that you have multiple transaction log backups that have been taken every hour, starting from 9am to 5pm, and you need to restore the database up to a certain set of transactions that have been happened during the day, how do you do it? If you do not know the exact time-tag of the transactions and no name given for the transactions, then restore with STOPAT or STOPATMARK is not possible. Since we cannot read the content of the database during the restore process, only way to figure it out is, restore one set (full database backup and 9am transaction log backup), complete and read the content. If the required content is not there, then restore the again with another set (full database backup, 9am transaction log backup and 10am transaction log backup). Again, If the required content is not there, then restore the again with another set (full database backup, 9am transaction log backup, 10am transaction log backup, and 11am transaction log backup). This unnecessary, time-consuming process has to be continued until you see your transactions in the database. What if SQL Server allows you to see the content during the restoration that helps you to identifying the last transaction log backup to be restored?

If you have more than one backup-set to be restored, all backup-sets should be restored with NORECOVERY option except the last one. Last one should be restored with RECOVERY option. Until that database remains Recovering state which blocks users connecting with it. Simply NORECOVERY option does not allow users to access the database until a backup-set is restored with RECOVERY. However, using STANDBY option instead NORECOVERY allows you to read the content of the database between transaction log restores.

Generally, SQL Server rolls back uncommitted transactions at the end of restore process with RECOVERY option to make the database readable. The STANDBY option also rolls back uncommitted transactions but keeps all the information needed for "undo" process which will allow for further restore. This basically allows you to read the content of the database after one transaction log restoration, without completing the restore process. Since it keeps necessary information for "undo" process, if required content is not available in the database, you can continue the restore process with additional transaction log backups.

This option is the best way of restoring the database for above-mentioned situation.

Here is a sample code for understanding restore operation with STANDBY option.

Backing up the database with full and multiple transactions log backups.

-- Creating a database for testing
USE master;
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'TestDatabase')
 DROP DATABASE TestDatabase;
GO

CREATE DATABASE TestDatabase
ON PRIMARY
(
 NAME = N'TestDatabase_Data'
 , FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\TestDatabase_Data.mdf'
 , SIZE = 10240KB, FILEGROWTH = 1024KB
)
LOG ON
(
 NAME = N'TestDatabase_Log'
 , FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\TestDatabase_Log.ldf'
 , SIZE = 5120KB, FILEGROWTH = 10%
);
GO

-- Changing the recovery model to full
ALTER DATABASE TestDatabase SET RECOVERY FULL;
GO

-- Take the initial backup
BACKUP DATABASE TestDatabase
 TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\TestDatabase.bak'
WITH INIT;
GO

-- create a table for testing purposes
USE TestDatabase;
GO

CREATE TABLE dbo.TestTable
(
 Id int identity(1,1) PRIMARY KEY
 , StringColumn nvarchar(600)
 , IntColumn bigint
);
GO

-- Insert the first record
INSERT INTO dbo.TestTable
 (StringColumn, IntColumn)
VALUES ('This is the first data', 12345);

-- Check records. You should see one record
SELECT * FROM dbo.TestTable

-- Perform first transaction log backup
-- This backup contains our first record inserted
BACKUP LOG TestDatabase
 TO DISK =
 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\TestDatabase_log.trn'
WITH INIT;
GO

-- Insert the second record
INSERT INTO dbo.TestTable
 (StringColumn, IntColumn)
VALUES ('This is the second data', 12345);

-- Check records. You should see two records now
SELECT * FROM dbo.TestTable

-- Perform the second transaction log backup
-- This backup contains first and second records inserted
BACKUP LOG TestDatabase
 TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\TestDatabase_log.trn'
WITH NOINIT;
GO

-- Insert the third record
INSERT INTO dbo.TestTable
 (StringColumn, IntColumn)
VALUES ('This is the third data', 12345);

-- Check records. You should see three records now
SELECT * FROM dbo.TestTable

-- Perform the third transaction log backup
-- This backup contains first, second and third records inserted
BACKUP LOG TestDatabase
 TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\TestDatabase_log.trn'
WITH NOINIT;
GO

Assume that we need to restore up to the second record and we do not know the exact time which record was inserted, and we do not know which transaction log backup holds it. Then only option is, using STANDBY option, here is the way of doing it.

-- Restoring the full backup
USE master;
GO

RESTORE DATABASE TestDatabase
 FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\TestDatabase.bak'
WITH REPLACE, NORECOVERY;
GO

-- Check the state of the database
-- This will show as "Restoring"
SELECT 
 name, state_desc
FROM sys.databases
WHERE name = 'TestDatabase';
GO

-- Restore first log with STANDBY instead NORECOVERY
RESTORE DATABASE TestDatabase
 FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\TestDatabase_log.trn'
WITH FILE =1, STANDBY = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\ROLLBACK_UNDO_TestDatabase_.bak';
GO

-- Read the state of the database
-- Now you will see that it is "online".
-- But remember, now the database is read-only.
-- Have a look on it with Object Explorer, you will the state.
SELECT 
 name, state_desc
FROM sys.databases
WHERE name = 'TestDatabase';
GO

-- Read the table
USE TestDatabase;
GO

SELECT * FROM dbo.TestTable;
-- We see the first record, but we need the second record.
-- Now let's try with our second log backup.


USE master;
GO

RESTORE LOG TestDatabase
 FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\TestDatabase_log.trn'
WITH FILE =2, STANDBY = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\ROLLBACK_UNDO_TestDatabase_.bak';
GO

-- Again, let read the table
USE TestDatabase;
GO

SELECT * FROM dbo.TestTable;
-- As you see, we have the required record.
-- No need to continue the restore operation.
-- Let's complete the restore process

USE master;
GO

RESTORE DATABASE TestDatabase WITH RECOVERY;

-- Done.