SQL101: Why restoring from a backup is slower than creating it

SQLskills launches new record placement initiative with basic knowledge, we called it SQL101. We will write about things that, as we often see, are done incorrectly, technologies that are used incorrectly, and many misunderstandings that lead to serious problems. If you want to find all the records in this series, check out the link SQLskills.com/help/SQL101 (English).

One of the questions that I constantly get asked is why it takes longer to restore a database from a full backup than to create a full backup. The answer is that the recovery process almost always requires more work.

Creating a full backup includes the following main stages:

1. Create a checkpoint.
2. Reading all the data used from the data files (technically, reading all the placed extents, no matter how many of the 8 pages in the extent are actually used).
3. Reading the transaction log from the beginning of the oldest uncommitted transaction from the initial checkpoint to the moment when the second stage was completed. This is necessary so that the database can be restored to a consistent state at the point in time belonging to the backup period (see this article (English) for a detailed explanation).
4. (Optionally testing the checksums of all pages, optionally performing backup compression and optionally encrypting the backup).

Recovery from a full backup includes the following main stages:
')

1. Creating data files (and filling them with zeros if instant initialization of files is not allowed (English, a good alternative in Russian ).
2. Copy data from backup to data files.
3. Creating a transaction log file and filling it with zeros. The transaction log file must always be filled with zeros when it is created (see this article (English) for a detailed explanation).
4. Copy transaction log from backup to log file.
5. Starting a database crash recovery.
6. (Optional testing of the checksums of all pages during the second phase, unpacking, if the backup was compressed, decryption, if the backup was encrypted.)

Stage 3 is often the longest stage in recovery, and it is proportional to the size of the transaction log. This process is carried out in a separate stage, instead of being performed in parallel with stage 1-2, and for in-depth study, see the recent entry in the Bob Ward blog .

Stage 5 can be the longest stage in the recovery process if there were long uncommitted transactions in the backup creation process. And it may be even longer if there are a large number of virtual log files (thousands) in the transaction log, since they greatly slow down the rollback mechanism of uncommitted transactions.

Here is a list of things you can do to ensure quick recovery from a full backup:

• Ensure that instant file initialization is enabled for the instance of SQL Server that performs the restore operation to avoid wasting time filling in zero any data files that should be created. This can save hours of downtime for very large data files.
• If possible - restore over existing database - do not delete existing files. This avoids the need to create and potentially fill with zeros the full amount of files, especially transaction log files. Be very careful when using this item, as the existing database will be hopelessly destroyed as soon as the recovery process starts overwriting it.
• Consider using backup compression, it can speed up operations and create and restore backups, and save disk space and storage costs.
• Consider using multiple backup files, each on a separate volume. SQL Server recognizes this situation and will use parallel write (one thread per volume) for file records when creating a backup and reading during the restore process - speeding up all processes. If you have multiple database files, similar I / O parallelization will occur - providing even more acceleration.
• Try to avoid long transactions, as they will take a long time in the rollback process.
• Manage your transaction log so that you do not have too many virtual log files, so that if there are transactions that require a rollback, the rollback is as fast as possible. See this blog entry (English) for a detailed explanation.

Hope this was helpful!