869
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
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
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.
- Restore the latest full backup
- 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
- 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'
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.
- Restore the full backup
- Restore to the differential backup last taken before 03:15 AM
- 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 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
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.
- Simple script to backup all SQL Server databases
- SQL Server Backup Options and Commands Tutorial
- Striping SQL Server Database Backups
- Copy Only Backup for SQL Server
- COPY_ONLY Backups with SQL Server
- SQL Server Backup Tutorial
- SQL Server Recovery Models
- Invoking SQL Server Database Backups with PowerShell
