Different Ways to Restore a SQL Server Database

Problem

Can you describe SQL Server database restore principles for full backups, differential backups and transaction log backups and how I would need to perform the restores to get to a particular point in time?

Solution

This tip describes SQL Server database restore principles on a database that is using the FULL recovery model.

The diagram below will be used to explain the solution. It is a simple visual, but I find that in many cases it is an effective method to visualize and describe SQL Server database restores.

In the diagram, I have 3 typical types of SQL Server backups

• full database backup denoted by F”x”
• differential database backup denoted by D”x” and
• transaction log backup denoted by T”x”.

The “x” represents an incrementing number that corresponds to a point-in-time when the specific backup type is taken. The character F, D and T denotes the type of backup taken.  The point-in-time (P) denotes the point-in-time a backup is taken.

For example, F1 refers to a full database backup taken at a point-in-time P1. Some time later, another full backup F2 is taken at point-in-time P9. Similarly T1 refers to a transaction log backup taken at point-in-time P2 which happens after the full database backup F1, then a second transaction log backup T2 is taken. Subsequently a differential database backup D1 occurred at point-in-time P4 and so on.

Point-in-time P13 is a visual indicator for a committed transaction that has occurred, but a transaction log backup was not taken until point-in-time P14. This will be used in example 3 which will describe a technique to recover the database to point-in-time P13.

Below are 3 examples of common database restore scenarios to learn the SQL Server restore behavior.

Example 1 – restore to point in time P8

Recovery path options to restore the database backup to point-in-time P8

In this example, the fastest database recovery path to point-in-time P8 would be Option 1.

Differential backups are cumulative (this means that any differential backup after the last full backup contains all of the changes) and therefore only one can be restored after the base full backup has been restored.  Hence, option F1 > D1 > D2 > T5 is not required nor supported.

Example 2 – restore to point in time P10

Recovery path options to restore a database backup to point-in-time P10

The fastest database recovery path to point-in-time P10 would be Option 1.

For whatever reason, if full database backup F2 is missing, it is still possible to restore from full backup F1, with a combination of differential and transaction log backups to get to P10. A database full backup does not break the transaction log backup chain, which means transactions logs can be restored for earlier full backups that have occurred.

One of the top reasons to have full database backup F2 available is for the Recovery Time Objective (RTO). This demo only contains a few transaction log backups, but we could expect hundreds of transaction log backup for a production database. Applying a large number of transaction log restores is very time consuming and can have a major impact on your system RTO.

Example 3 – restore to point in time P13

Assume a situation where you need to restore transactions to point-in-time P13, but your transaction log backup is only taken at point-in-time P14.

In real-life, a DBA is unlikely given an exact recovery point-in-time. Imagine a bug in code that updates a whole table without a WHERE clause and the DBA is told to restore the database to right before the update.

It is important to note that SQL Server does not provide the ability to restore to any point-in-time by restoring from a full or differential backup. The only way to do this is to use the transaction log backup and to specify the STOPAT clause. Only transaction log backups allow recovery to any point-in-time.

So, the RESTORE technique here is to utilize an UNDO file, so you can restore the database in STANDYBY mode and specify the STOPAT clause. The trick is knowing that you can execute a RESTORE from the same transaction log over and over again until you get to the correct point.

Also to prove Example 2 by not using full database backup F2, I will use the following restore path F1 > D2 > T5 > T6 > T7 > T8 as evidence that another full database backup does not break the transaction log backup chain.

You can specify to use an UNDO file in the middle of a RESTORE sequence. In my case, the UNDO file is specified when restoring from transaction log T7.

USE [master]
GO
-- Restore path F1 > D2 > T5 > T6 > T7
RESTORE DATABASE [TestRestore] FROM DISK = N'C:\Temp\F1.BAK' WITH FILE = 1, 
NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
GO
RESTORE DATABASE [TestRestore] FROM DISK = N'C:\Temp\D2.BAK' WITH FILE = 1, 
NORECOVERY, NOUNLOAD, STATS = 5
GO
RESTORE LOG [TestRestore] FROM DISK = N'C:\Temp\T5.TRN' WITH FILE = 1, 
NORECOVERY, NOUNLOAD, STATS = 5
GO
RESTORE LOG [TestRestore] FROM DISK = N'C:\Temp\T6.TRN' WITH FILE = 1, 
NORECOVERY, NOUNLOAD, STATS = 5
GO
-- Specify an UNDO file when restoring transaction log T7
RESTORE LOG [TestRestore] FROM DISK = N'C:\Temp\T7.TRN' WITH FILE = 1, 
STANDBY = N'C:\Temp\TestRestore_RollbackUndoFile.tuf', NOUNLOAD, STATS = 5
GO

Once the database is restored to point-in-time P11 at transaction log T7, the DBA can peek inside the table to confirm the required data exists. As expected, we can see record “T7 – Log backup” in the table.

SELECT * FROM TestRestore.dbo.TranRecord
GO

Now you can continue to restore transaction log T8 utilizing the same UNDO file. The DBA can perform the same transaction log restore repeatedly until they arrive at the required point-in-time by incrementing the time specified in the STOPAT clause.

In the restore operation below, the STOPAT time is specified at 1 second after point-in-time P11.

USE [master]
RESTORE LOG [TestRestore] FROM DISK = N'C:\Temp\T8.TRN' WITH FILE = 1, 
STANDBY = N'C:\Temp\TestRestore_RollbackUndoFile.tuf', 
NOUNLOAD, STATS = 10, STOPAT = N'2013-10-25T19:55:26'
GO
SELECT * FROM TestRestore.dbo.TranRecord
GO

Since I know there is a record inserted every 5 seconds, I will increase the STOPAT interval by a few seconds when restoring from transaction log T8. When incrementing the STOPAT time, we will encounter point-in-time P12 along the way.

USE [master]
RESTORE LOG [TestRestore] FROM DISK = N'C:\Temp\T8.TRN' WITH FILE = 1, 
STANDBY = N'C:\Temp\TestRestore_RollbackUndoFile.tuf', 
NOUNLOAD, STATS = 10, STOPAT = N'2013-10-25T19:55:33'
GO
SELECT * FROM TestRestore.dbo.TranRecord
GO

In our case, we wanted to get to point-in-time P13 which contains record “Restore to here”. Once this record is reached, we can set the database to RECOVERY and we have achieved our restore to point-in-time P13.

USE [master]
RESTORE LOG [TestRestore] FROM DISK = N'C:\Temp\T8.TRN' WITH FILE = 1, 
STANDBY = N'C:\Temp\TestRestore_RollbackUndoFile.tuf', 
NOUNLOAD, STATS = 10, STOPAT = N'2013-10-25T19:55:37'
GO
SELECT * FROM TestRestore.dbo.TranRecord
GO

Now that we have the records we want, we can use the RESTORE WITH RECOVERY command which will end the restore sequence and make the database available for read-write activity. By ending the restore sequence this means no further transaction logs can be restored.

USE [master]RESTORE DATABASE TestRestore WITH RECOVERY
GO
SELECT * FROM TestRestore.dbo.TranRecord
GO

Database setup script

To test this out on your system, use this script below to create the test database for this tip demo.

USE master
GO
CREATE DATABASE TestRestore
GO
CREATE TABLE TestRestore.dbo.TranRecord (col1 varchar(3), Descr varchar(100), 
TransactionTimeStamp datetime default GETDATE())
GO
ALTER DATABASE TestRestore SET RECOVERY FULL
GO

Once the test database is created, below are the complete scripts to simulate each point-in-time described above.

USE master
GO
INSERT INTO [TestRestore].dbo.TranRecord VALUES ('P1', 'F1 - Full backup', GETDATE())
-- Take full backup F1
BACKUP DATABASE [TestRestore] TO DISK = 'C:\TEMP\F1.BAK'
WAITFOR DELAY '00:00:05:00'
INSERT INTO [TestRestore].dbo.TranRecord VALUES ('P2', 'T1 - Log Backup', GETDATE())
-- Take transaction log backup T1
BACKUP LOG [TestRestore] TO DISK = 'C:\TEMP\T1.TRN'
WAITFOR DELAY '00:00:05:00'
INSERT INTO [TestRestore].dbo.TranRecord VALUES ('P3', 'T2 - Log Backup', GETDATE())
-- Take transaction log backup T2
BACKUP LOG [TestRestore] TO DISK = 'C:\TEMP\T2.TRN'
WAITFOR DELAY '00:00:05:00'
INSERT INTO [TestRestore].dbo.TranRecord VALUES ('P4', 'D1 - Differential Backup', GETDATE())
-- Take differential backup D1
BACKUP DATABASE [TestRestore] TO DISK = 'C:\TEMP\D1.BAK' WITH DIFFERENTIAL
WAITFOR DELAY '00:00:05:00'
INSERT INTO [TestRestore].dbo.TranRecord VALUES ('P5', 'T3 - Log Backup', GETDATE())
-- Take transaction log backup T3
BACKUP LOG [TestRestore] TO DISK = 'C:\TEMP\T3.TRN'
WAITFOR DELAY '00:00:05:00'
INSERT INTO [TestRestore].dbo.TranRecord VALUES ('P6', 'T4 - Log Backup', GETDATE())
-- Take transaction log backup T4
BACKUP LOG [TestRestore] TO DISK = 'C:\TEMP\T4.TRN'
WAITFOR DELAY '00:00:05:00'
INSERT INTO [TestRestore].dbo.TranRecord VALUES ('P7', 'D2 - Differential Backup', GETDATE())
-- Take differential backup D2
BACKUP DATABASE [TestRestore] TO DISK = 'C:\TEMP\D2.BAK' WITH DIFFERENTIAL
WAITFOR DELAY '00:00:05:00'
INSERT INTO [TestRestore].dbo.TranRecord VALUES ('P8', 'T5 - Log Backup', GETDATE())
-- Take transaction log backup T5
BACKUP LOG [TestRestore] TO DISK = 'C:\TEMP\T5.TRN'
WAITFOR DELAY '00:00:05:00'
INSERT INTO [TestRestore].dbo.TranRecord VALUES ('P9', 'F2 - Full backup', GETDATE())
-- Take full backup F2
BACKUP DATABASE [TestRestore] TO DISK = 'C:\TEMP\F2.BAK'
WAITFOR DELAY '00:00:05:00'
INSERT INTO [TestRestore].dbo.TranRecord VALUES ('P10', 'T6 - Log Backup', GETDATE())
-- Take transaction log backup T6
BACKUP LOG [TestRestore] TO DISK = 'C:\TEMP\T6.TRN'
WAITFOR DELAY '00:00:05:00'
INSERT INTO [TestRestore].dbo.TranRecord VALUES ('P11', 'T7 - Log Backup', GETDATE())
-- Take transaction log backup T7
BACKUP LOG [TestRestore] TO DISK = 'C:\TEMP\T7.TRN'
WAITFOR DELAY '00:00:05:00'
INSERT INTO [TestRestore].dbo.TranRecord VALUES ('P12', 'D3 - Differential backup', GETDATE())
-- Take differential backup D3
BACKUP DATABASE [TestRestore] TO DISK = 'C:\TEMP\D3.BAK' WITH DIFFERENTIAL
WAITFOR DELAY '00:00:05:00'
-- Insert a record but skips the transaction log backup
INSERT INTO [TestRestore].dbo.TranRecord VALUES ('P13', 'Restore to here', GETDATE())
WAITFOR DELAY '00:00:05:00'
INSERT INTO [TestRestore].dbo.TranRecord VALUES ('P14', 'T8 - Log Backup', GETDATE())
-- Take transaction log backup T8
BACKUP LOG [TestRestore] TO DISK = 'C:\TEMP\T8.TRN'

Next Steps

• Tune in for my next tip on how SQL Server correlates between full backup, differential backup and transaction log backup set
• More on SQL Server database restore commands