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.

view plainprint?

1. -- Creating a database for testing  
2. USE master;  
3. GO  
4.   
5. IF EXISTS (SELECT * FROM sys.databases WHERE name = 'TestDatabase')  
6.  DROP DATABASE TestDatabase;  
7. GO  
8.   
9. CREATE DATABASE TestDatabase  
10. ON PRIMARY  
11. (  
12.  NAME = N'TestDatabase_Data'  
13.  , FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\TestDatabase_Data.mdf'  
14.  , SIZE = 10240KB, FILEGROWTH = 1024KB  
15. )  
16. LOG ON  
17. (  
18.  NAME = N'TestDatabase_Log'  
19.  , FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\TestDatabase_Log.ldf'  
20.  , SIZE = 5120KB, FILEGROWTH = 10%  
21. );  
22. GO  
23.   
24. -- Changing the recovery model to full  
25. ALTER DATABASE TestDatabase SET RECOVERY FULL;  
26. GO  
27.   
28. -- Take the initial backup  
29. BACKUP DATABASE TestDatabase  
30.  TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\TestDatabase.bak'  
31. WITH INIT;  
32. GO  
33.   
34. -- create a table for testing purposes  
35. USE TestDatabase;  
36. GO  
37.   
38. CREATE TABLE dbo.TestTable  
39. (  
40.  Id int identity(1,1) PRIMARY KEY  
41.  , StringColumn nvarchar(600)  
42.  , IntColumn bigint  
43. );  
44. GO  
45.   
46. -- Insert the first record  
47. INSERT INTO dbo.TestTable  
48.  (StringColumn, IntColumn)  
49. VALUES ('This is the first data', 12345);  
50.   
51. -- Check records. You should see one record  
52. SELECT * FROM dbo.TestTable  
53.   
54. -- Perform first transaction log backup  
55. -- This backup contains our first record inserted  
56. BACKUP LOG TestDatabase  
57.  TO DISK =  
58.  'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\TestDatabase_log.trn'  
59. WITH INIT;  
60. GO  
61.   
62. -- Insert the second record  
63. INSERT INTO dbo.TestTable  
64.  (StringColumn, IntColumn)  
65. VALUES ('This is the second data', 12345);  
66.   
67. -- Check records. You should see two records now  
68. SELECT * FROM dbo.TestTable  
69.   
70. -- Perform the second transaction log backup  
71. -- This backup contains first and second records inserted  
72. BACKUP LOG TestDatabase  
73.  TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\TestDatabase_log.trn'  
74. WITH NOINIT;  
75. GO  
76.   
77. -- Insert the third record  
78. INSERT INTO dbo.TestTable  
79.  (StringColumn, IntColumn)  
80. VALUES ('This is the third data', 12345);  
81.   
82. -- Check records. You should see three records now  
83. SELECT * FROM dbo.TestTable  
84.   
85. -- Perform the third transaction log backup  
86. -- This backup contains first, second and third records inserted  
87. BACKUP LOG TestDatabase  
88.  TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\TestDatabase_log.trn'  
89. WITH NOINIT;  
90. GO  

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

view plainprint?

1. -- Restoring the full backup  
2. USE master;  
3. GO  
4.   
5. RESTORE DATABASE TestDatabase  
6.  FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\TestDatabase.bak'  
7. WITH REPLACE, NORECOVERY;  
8. GO  
9.   
10. -- Check the state of the database  
11. -- This will show as "Restoring"  
12. SELECT   
13.  name, state_desc  
14. FROM sys.databases  
15. WHERE name = 'TestDatabase';  
16. GO  
17.   
18. -- Restore first log with STANDBY instead NORECOVERY  
19. RESTORE DATABASE TestDatabase  
20.  FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\TestDatabase_log.trn'  
21. WITH FILE =1, STANDBY = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\ROLLBACK_UNDO_TestDatabase_.bak';  
22. GO  
23.   
24. -- Read the state of the database  
25. -- Now you will see that it is "online".  
26. -- But remember, now the database is read-only.  
27. -- Have a look on it with Object Explorer, you will the state.  
28. SELECT   
29.  name, state_desc  
30. FROM sys.databases  
31. WHERE name = 'TestDatabase';  
32. GO  
33.   
34. -- Read the table  
35. USE TestDatabase;  
36. GO  
37.   
38. SELECT * FROM dbo.TestTable;  
39. -- We see the first record, but we need the second record.  
40. -- Now let's try with our second log backup.  
41.   
42.   
43. USE master;  
44. GO  
45.   
46. RESTORE LOG TestDatabase  
47.  FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\TestDatabase_log.trn'  
48. WITH FILE =2, STANDBY = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\ROLLBACK_UNDO_TestDatabase_.bak';  
49. GO  
50.   
51. -- Again, let read the table  
52. USE TestDatabase;  
53. GO  
54.   
55. SELECT * FROM dbo.TestTable;  
56. -- As you see, we have the required record.  
57. -- No need to continue the restore operation.  
58. -- Let's complete the restore process  
59.   
60. USE master;  
61. GO  
62.   
63. RESTORE DATABASE TestDatabase WITH RECOVERY;  
64.   
65. -- Done.  

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