Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Different Ways to Restore a SQL Server Database


By:   |   Last Updated: 2013-10-29   |   Comments (25)   |   Related Tips: More > Restore

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.

SQL Server point in time backup visual

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

Option 1: F1 > D2 > T5
Option 2: F1 > D1 > T3 > T4 > T5
Option 3: F1 > T1 > T2 > T3 > T4 > T5

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

Option 1: F2 > T6
Option 2: F1 > D2 > T5 > T6
Option 3: F1 > D1 > T3 > T4 > T5 > T6
Option 4: F1 > T1 > T2 > T3 > T4 > T5 > T6

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

restore SQL Server backups

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

restore sql backups

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

restore backups using stopat

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

sql server restore with standby

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

sql server restore with recovery

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


Last Updated: 2013-10-29


next webcast button


next tip button



About the author
MSSQLTips author Simon Liew Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Masterís Degree in Distributed Computing.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, September 07, 2016 - 12:41:26 AM - Rahul Back To Top

Hi Simon,Very Good Article about different recovery scenerio. Definately it will help others to understand and planning to recover Database.


Monday, September 21, 2015 - 1:51:25 PM - Stephen Back To Top

Good article. Great explanation.


Sunday, September 13, 2015 - 11:46:48 PM - Thomas Guo Back To Top

It is one of the best articles about restoration. It is well explained with great example. Thank you for sharing!


Tuesday, August 11, 2015 - 1:18:42 AM - khalil Back To Top

well explained step by step article, thanks for sharing.


Thursday, July 23, 2015 - 8:19:39 PM - Simon Liew Back To Top

Hi OG,

If you want to look for the specific timestamp or LSN of an UPDATE operation, there options. You can interrogate the transaction log using fn_dblog. If the transaction log has been backup to a file, you can use fn_dump_dblog to interrogate the transaction log backup file or a database file backup.

It is advisable not to perform fn_dblog on a production database, and definitely do not run fn_dump_dblog on a production SQL instance as there's a bug which will create a hidden SQLOS scheduler every time fn_dump_dblog is executed. This bug is resolved in SQL 2012 SP2.

I hope this helps.

Simon Liew


Wednesday, July 22, 2015 - 12:18:07 PM - Oscar G Back To Top

First of all Congrats Simon, Excellent Article.

I have a question, How can I restore my db from the UNDO File, imagine the scenario in where you don't know when the update happen, and during restore you go further, and you need to restored from undo a try another STOPAT?

Thanks for your help.

OG


Wednesday, April 30, 2014 - 8:40:32 AM - Prakash Back To Top

It's really a great article. I have learned something new that we could use STOPAT option...

Please keep up the good work....educate us like newbie's


Monday, January 20, 2014 - 12:58:25 PM - vinoth Back To Top

 

Hi Simon, Back with a bang :-) really great article. Hey guys I had an opportunity to work with this SQL Genius:-)

 


Saturday, November 23, 2013 - 9:55:08 PM - Simon Liew Back To Top

Thank you for the positive feedback.


Tuesday, November 19, 2013 - 4:27:44 AM - Josť Luis Toro Serrano Back To Top

Great post!!!


Friday, November 08, 2013 - 4:11:58 AM - deepak Back To Top

very good article


Thursday, November 07, 2013 - 12:30:06 AM - ch Back To Top

Great post, thanks.

select * from tfn


Wednesday, November 06, 2013 - 4:23:47 AM - saurabh Back To Top

Excellent,your Explanation is fabulous.


Tuesday, November 05, 2013 - 10:06:11 PM - Mazhar Samo Back To Top

Great post, keep up the good work


Tuesday, November 05, 2013 - 8:18:35 PM - Brent Back To Top

Great post, thanks.


Tuesday, November 05, 2013 - 3:40:30 PM - Fraz Malik Back To Top

An excellent writeup on recovery process with examples. Well done Simon. Keep sharing.


Tuesday, November 05, 2013 - 7:14:17 AM - Paulo CorrÍa Back To Top

Very well written and your example is excellent too.

Congratulations


Friday, November 01, 2013 - 7:17:42 AM - Simon Liew Back To Top

Thank you everyone for the positive feedback.


Wednesday, October 30, 2013 - 1:14:56 PM - Golam Back To Top

Very clearly written - great! Please keep posting.


Wednesday, October 30, 2013 - 2:58:13 AM - Gopalakrishnan Arthanarisamy Back To Top

Excellent and Very well written. Kudos to You.


Tuesday, October 29, 2013 - 8:51:15 PM - murali; Back To Top

*** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting

You Rock :)


Tuesday, October 29, 2013 - 6:26:20 PM - Tim Lenz Back To Top

The diagram is excellent for helping management to understand the process and sequence of events. It also makes a good presentation when explaining the time constraints when developing RTO expectations and  the need to have checks done regularly on backups to verify their value.

By not giving me the necessary time to verify backups on a regular basis I will need to increase my RTO expectations in case one of the backups is also corrupt and I must rely on more transactions to get back to a point in time.

 


Tuesday, October 29, 2013 - 5:38:25 PM - Paul Tormey Back To Top

Must agree - a very well written article demonstrating a variety of solutions to get to the same results.

 

Thank you.


Tuesday, October 29, 2013 - 5:25:59 PM - Simon Liew Back To Top

You're most welcome John.


Tuesday, October 29, 2013 - 10:27:13 AM - John Wood Back To Top

Extremely well written article. I hope to never use it but I now know exactly what to do. Well done and thank you for providing this information.


Learn more about SQL Server tools