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?
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.
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.
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.
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.
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.
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.
Database setup script
To test this out on your system, use this script below to create the test database for this tip demo.
Once the test database is created, below are the complete scripts to simulate each point-in-time described above.