SQL Server Restore Database Options and Examples


By:   |   Updated: 2021-06-21   |   Comments   |   Related: More > Restore


Problem

What is the difference between full, transaction log, and differential SQL Server database backups? And how do I restore from each backup type? What database options are available? What are common restore operations?

Solution

We will look at the differences between each backup type and how they are used together, then demonstrate with some examples with the SQL Server database engine.

What are the differences between each SQL Server backup type?

Full Database Backup

  • As the name implies, a full backup is a full backup of everything in the database, which is generally a *.bak file
  • This backup can be restored by itself
  • It’s the foundation for restoring transaction log and differential backups

Transaction Log Backup

  • Backup of database transaction log file since the last full backup or transaction log backup, which is generally a *.trn fie
  • Restored after a full backup has been restored

Differential Backup

  • A differential backup is a backup of the data that has changed since the last full backup, which is generally a *.dif fie
  • Restored after a full backup has been restored

Why do we need different SQL Server database backup types?

Say you have a very small database and your recovery requirements are that you cannot lose more than 1 hour of data. It’s conceivable you could just do a full backup of the database every hour. However, it’s highly unlikely that’s the case. It’s more likely you have a much larger database and storing that many full backups would just not be practical due to the size of the backup files. In addition, you would not have the ability to stop a restore at a specific point in time or restore an individual data page without transaction log backups.

SQL Server Backup and Restore Database Examples

Backups Schedule

We’re backing up MyDatabase (database name) to D:\Backups (file location) according to the following schedule:

Backup Type Schedule
Full Every night at 12 midnight
Transaction Log Every hour starting at 12:15AM
Differential Every 8 hours starting at 01:30AM

Backup Files / History

 To demonstrate the differences between the backups types we’ll look at examples of restoring the backups of MyDatabase (existing database) to another database named MyDatabaseTest (new database).

The following table has a list of all the backup files for a 24-hour period starting at midnight. There is 1 full, 24 transaction log, and 3 differential backups.

Backup Time Backup Type Backup File Path \ Name
06/01/21 12:00 AM Full D:\Backups\MyDatabase_backup_2021_06_01_000011_4175616.bak
06/01/21 12:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_001502_1580793.trn
06/01/21 01:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_011501_4325128.trn
06/01/21 01:30 AM Differential D:\Backups\MyDatabase_backup_2021_06_01_013001_9294408.dif
06/01/21 02:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_021501_5352741.trn
06/01/21 03:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_031501_5822593.trn
06/01/21 04:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_041501_8351922.trn
06/01/21 05:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_051502_0940555.trn
06/01/21 06:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_061501_5368917.trn
06/01/21 07:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_071502_5161373.trn
06/01/21 08:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_081501_5644279.trn
06/01/21 09:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_091501_6308147.trn
06/01/21 09:30 AM Differential D:\Backups\MyDatabase_backup_2021_06_01_093001_4951166.dif
06/01/21 10:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_101502_4623997.trn
06/01/21 11:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_111502_5178928.trn
06/01/21 12:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_121503_0205342.trn
06/01/21 01:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_131503_3755175.trn
06/01/21 02:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_141501_8916226.trn
06/01/21 03:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_151501_2361858.trn
06/01/21 04:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_161501_4941559.trn
06/01/21 05:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_171501_2457229.trn
06/01/21 05:30 PM Differential D:\Backups\MyDatabase_backup_2021_06_01_173001_7071188.dif
06/01/21 06:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_181501_5856632.trn
06/01/21 07:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_191501_6847568.trn
06/01/21 08:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_201501_2697377.trn
06/01/21 09:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_211502_6835717.trn
06/01/21 10:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_221502_3743339.trn
06/01/21 11:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_231502_6227036.trn

Restore to the Latest Full Backup

In our first example we’ll restore MyDatabaseTest from the latest full backup of MyDatabase by running the T-SQL code below in SQL Server Management Studio (SSMS):

Backup Time Backup Type Backup File Path \ Name
06/01/21 12:00 AM Full D:\Backups\MyDatabase_backup_2021_06_01_000011_4175616.bak
/* restore from the latest full backup */
 
-- restore database
RESTORE DATABASE [MyDatabaseTest] FROM  DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_000011_4175616.bak' 
WITH MOVE N'MyDatabase' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabaseTest.mdf',  
     MOVE N'MyDatabase_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabaseTest_log.ldf',  
RECOVERY, -- 'with recovery' is optional here - it's the default if not specified - database will be available
REPLACE;
Restore to the Latest Full Backup

We’ve now restored MyDatabaseTest to the point in time the full backup of MyDatabase was taken.

Restore to the Latest Point in Time with Full and Transaction Log Backups

For this example, we’ll forget about the differential backups for a moment and restore MyDatabase to the latest point in time with the full and transaction log backups. This list starts with the latest full backup and the transaction logs backups to restore which overwrite the database.

Backup Time Backup Type Backup File Path \ Name
06/01/21 12:00 AM Full D:\Backups\MyDatabase_backup_2021_06_01_000011_4175616.bak
06/01/21 12:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_001502_1580793.trn
06/01/21 01:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_011501_4325128.trn
06/01/21 02:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_021501_5352741.trn
06/01/21 03:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_031501_5822593.trn
06/01/21 04:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_041501_8351922.trn
06/01/21 05:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_051502_0940555.trn
06/01/21 06:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_061501_5368917.trn
06/01/21 07:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_071502_5161373.trn
06/01/21 08:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_081501_5644279.trn
06/01/21 09:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_091501_6308147.trn
06/01/21 10:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_101502_4623997.trn
06/01/21 11:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_111502_5178928.trn
06/01/21 12:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_121503_0205342.trn
06/01/21 01:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_131503_3755175.trn
06/01/21 02:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_141501_8916226.trn
06/01/21 03:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_151501_2361858.trn
06/01/21 04:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_161501_4941559.trn
06/01/21 05:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_171501_2457229.trn
06/01/21 06:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_181501_5856632.trn
06/01/21 07:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_191501_6847568.trn
06/01/21 08:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_201501_2697377.trn
06/01/21 09:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_211502_6835717.trn
06/01/21 10:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_221502_3743339.trn
06/01/21 11:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_231502_6227036.trn
/* restore to the latest point in time with full and transaction log backups */
 
-- restore full backup - foundation for restoring transaction log backups
RESTORE DATABASE [MyDatabaseTest] 
FROM  DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_000011_4175616.bak' 
WITH MOVE N'MyDatabase' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabaseTest.mdf',  
     MOVE N'MyDatabase_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabaseTest_log.ldf',  
NORECOVERY, -- 'restore with norecovery' allows additional backups to be applied - database will be unavailable 
REPLACE;    
 
-- restore transaction log backups
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_001502_1580793.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_011501_4325128.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_021501_5352741.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_031501_5822593.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_041501_8351922.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_051502_0940555.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_061501_5368917.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_071502_5161373.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_081501_5644279.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_091501_6308147.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_101502_4623997.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_111502_5178928.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_121503_0205342.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_131503_3755175.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_141501_8916226.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_151501_2361858.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_161501_4941559.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_171501_2457229.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_181501_5856632.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_191501_6847568.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_201501_2697377.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_211502_6835717.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_221502_3743339.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_231502_6227036.trn' WITH NORECOVERY;
 
-- make database available and no more backup files can be applied
RESTORE LOG [MyDatabaseTest] WITH RECOVERY; -- 'restore with recovery' so no additional transaction logs will be applied
Restore to the Latest Point in Time with Full and Transaction Log Backups

In this example, the transaction logs are backed up every hour, so we needed to run 24 separate RESTORE LOG statements. If they were backed up every 15 minutes, it would be necessary to restore 96 separate transaction log backup files. While this is a perfectly valid way to restore a database, it is cumbersome and prone to mistakes. How can it be simplified?

Restore to the latest point in time with Full, Differential and Transaction Log Backups

This is where the differential backups are very useful. This time, we’ll repeat restoring MyDatabaseTest to the latest point in time with the full, differential, and transaction log backups.

  1. Restore the latest full backup
  2. As differential backups have the data that’s changed since the last full backup, we can omit the transaction log backups between the full backup and the latest differential backup
  3. We’ll only need the transaction log backups after latest differential backup

Here are the backup files to be restored.

Backup Time Backup Type Backup File Path \ Name
06/01/21 12:00 AM Full D:\Backups\MyDatabase_backup_2021_06_01_000011_4175616.bak
06/01/21 05:30 PM Differential D:\Backups\MyDatabase_backup_2021_06_01_173001_7071188.dif
06/01/21 06:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_181501_5856632.trn
06/01/21 07:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_191501_6847568.trn
06/01/21 08:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_201501_2697377.trn
06/01/21 09:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_211502_6835717.trn
06/01/21 10:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_221502_3743339.trn
06/01/21 11:15 PM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_231502_6227036.trn
/* restore options - restore to the latest point in time with full, differential and transaction log backups */
 
-- restore full backup 
RESTORE DATABASE [MyDatabaseTest] 
FROM  DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_000011_4175616.bak' 
WITH MOVE N'MyDatabase' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabaseTest.mdf',  
     MOVE N'MyDatabase_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabaseTest_log.ldf',  
NORECOVERY, -- 'restore with norecovery' allows additional backups to be applied - database will be unavailable 
REPLACE;  
 
-- restore most recent differential backup
RESTORE DATABASE [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_173001_7071188.dif' WITH NORECOVERY; 
 
-- restore transaction log backups taken after latst differential
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_181501_5856632.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_191501_6847568.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_201501_2697377.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_211502_6835717.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_221502_3743339.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_231502_6227036.trn' WITH NORECOVERY;
 
-- make database available and no more backup files can be applied
RESTORE LOG [MyDatabaseTest] WITH RECOVERY;-- 'restore with recovery'
Restore to the latest point in time with Full, Differential and Transaction Log Backups

MyDatabaseTest has been restored to the same point in time as in the previous example.

Restore to an Earlier Point in Time When a Transaction Log Was Backed Up

Let’s look at an example of restoring to 03:15 AM.

  1. Restore the full backup
  2. Restore to the differential backup last taken before 03:15 AM
  3. Restore the transaction log backups taken between 01:30 AM and 03:15 AM

Here are the backup files to be restored.

Backup Time Backup Type Backup File Path \ Name
06/01/21 12:00 AM Full D:\Backups\MyDatabase_backup_2021_06_01_000011_4175616.bak
06/01/21 01:30 AM Differential D:\Backups\MyDatabase_backup_2021_06_01_013001_9294408.dif
06/01/21 02:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_021501_5352741.trn
06/01/21 03:15 AM Transaction Log D:\Backups\MyDatabase_backup_2021_06_01_031501_5822593.trn
/* restore options - restore to a point in time when a transaction log was backed up */ 
 
-- restore full backup 
RESTORE DATABASE [MyDatabaseTest] 
FROM  DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_000011_4175616.bak' 
WITH MOVE N'MyDatabase' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabaseTest.mdf',  
     MOVE N'MyDatabase_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabaseTest_log.ldf',  
     NORECOVERY,  -- 'with norecovery' allows additional backups to be applied - database will be unavailable 
     REPLACE;  
 
-- restore differential backup
RESTORE DATABASE [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_013001_9294408.dif' WITH NORECOVERY;
 
-- restore transaction log backups
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_021501_5352741.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_031501_5822593.trn' WITH NORECOVERY;
 
-- make database available and no more backup files can be applied
RESTORE LOG [MyDatabaseTest] WITH RECOVERY;
Restore to a Point in Time When a Transaction Log Was Backed Up

Restore to a Particular Point in Time Between Transaction Log Backups

This example will show how to stop restoring the same transaction log backup, but stopping at 02:30 AM in the backup using STOPAT.

/* restore to a point in time when a transaction log was backed up */ 
 
-- restore full backup 
RESTORE DATABASE [MyDatabaseTest] 
FROM  DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_000011_4175616.bak' 
WITH MOVE N'MyDatabase' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabaseTest.mdf',  
     MOVE N'MyDatabase_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabaseTest_log.ldf',  
     NORECOVERY,  -- 'with norecovery' allows additional backups to be applied - database will be unavailable 
     REPLACE;  
 
-- restore differential backup
RESTORE DATABASE [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_013001_9294408.dif' WITH NORECOVERY;
 
-- restore transaction log backups
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_021501_5352741.trn' WITH NORECOVERY;
RESTORE LOG [MyDatabaseTest] FROM DISK = N'D:\Backups\MyDatabase_backup_2021_06_01_031501_5822593.trn' WITH NORECOVERY, 
        STOPAT = '2021-06-01 02:30:00 AM';
 
-- make database available and no more backup files can be applied
RESTORE LOG [MyDatabaseTest] WITH RECOVERY;
Restore to a Particular Point in Time Between Transaction Log Backups
Next Steps

We now know how to restore to any point in time from the oldest full backup. Here are some links to some further information.



Last Updated: 2021-06-21


get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips
Related Resources



Comments For This Article





download





Recommended Reading

Identify when a SQL Server database was restored, the source and backup date

How to migrate a SQL Server database to a lower version

SQL Server Database RESTORE WITH MOVE or not WITH MOVE

Make Network Path Visible For SQL Server Backup and Restore in SSMS

SQL Server Database Stuck in Restoring State














get free sql tips
agree to terms