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

Original author: Paul S. Randal
  • Transfer
  • Tutorial
SQLskills launches a new initiative for posting records 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 entries in this series, check out the link SQLskills.com/help/SQL101 (English).

One of the questions that I am constantly asked is why restoring a database from a full backup takes longer than creating a full backup. The answer is that almost always the recovery process requires more work.

Creating a full backup includes the following main stages:

  1. Create a breakpoint.
  2. Reading all used data from data files (technically, reading all posted extents, regardless of how many of the 8 pages in the extent are actually used).
  3. Read the transaction log from the beginning of the oldest uncommitted transaction from the initial checkpoint until the second stage has been completed. This is necessary so that the database can be restored to a consistent state at a point in time that belongs to the backup period (see this article (English) for a detailed explanation).
  4. (Optional testing of checksums of all pages, optionally performing backup compression and optionally encrypting backups).

Restoring from a full backup includes the following main steps:

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

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

Stage 5 may be the longest stage in the recovery process if long uncommitted transactions existed during the backup process. And it can be even longer if there are a large number of virtual log files (thousands) in the transaction log, since they greatly slow down the mechanism of rollback 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 is performing the restore operation to avoid wasting time filling out any data files that must be created with zeros. This can save hours of downtime for very large data files.
  • If possible - restore over an existing database - do not delete existing files. This avoids the need to create and potentially zeros fill the full volume 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 from 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 writing (one thread per volume) for writing files when creating a backup and while reading in the recovery process - speeding up all processes. If you have multiple database files, similar parallelization of I / O processes will occur - providing even greater speedup.
  • 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 an excessive number of virtual log files so that when there are transactions that require rollback, the rollback is as fast as possible. See this blog post (English) for a detailed explanation.

Hope this was helpful!

Also popular now: