{"id":4722,"date":"2017-05-17T10:41:51","date_gmt":"2017-05-17T17:41:51","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4722"},"modified":"2017-10-12T15:17:56","modified_gmt":"2017-10-12T22:17:56","slug":"sqlskills-sql101-why-is-restore-slower-than-backup","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-is-restore-slower-than-backup\/","title":{"rendered":"SQLskills SQL101: Why is restore slower than backup"},"content":{"rendered":"

As Kimberly blogged about recently<\/a>, SQLskills is embarking on a new initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019ll all be blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101<\/strong> blog posts, check out\u00a0SQLskills.com\/help\/SQL101<\/a>.<\/em><\/p>\n

One question I get asked every so often is why it can sometimes take longer to restore a database from a full backup than it took to perform the backup in the first place. The answer is that in cases like that, there’s more work to do during the restore process.<\/p>\n

A full backup has the following main phases:<\/p>\n

    \n
  1. Perform a checkpoint.<\/li>\n
  2. Read all in-use data from the data files (technically, reading all allocated extents, regardless of whether all 8 pages in the extent are in use).<\/li>\n
  3. Read all transaction log from the start of the oldest uncommitted transaction as of the initial checkpoint up to the time that phase 2 finished. This is necessary so the database can be recovered to a consistent point in time during the restore process (see this post<\/a> for more details).<\/li>\n
  4. (Optionally test all page checksums, optionally perform backup compression, and optionally perform backup encryption).<\/li>\n<\/ol>\n

    A full restore has the following main phases:<\/p>\n

      \n
    1. Create the data files (and zero initialize them if instant file initialization<\/a> is not enabled).<\/li>\n
    2. Copy data from the backup to the data files.<\/li>\n
    3. Create the log file and zero initialize it.\u00a0The log file must always be zero initialized when created (see this post<\/a> for more details).<\/li>\n
    4. Copy transaction log from the backup to the log file.<\/li>\n
    5. Run crash recovery on the database.<\/li>\n
    6. (Optionally test all page checksums during phase 2, perform decompression if the backup is compressed, and perform decryption if the backup is encrypted.)<\/li>\n<\/ol>\n

      Phase 3 above can often be the longest phase in the restore process, and is proportional to the size of the transaction log. This is done as a separate phase rather than being done in parallel with phases 1 and 2, and for a deep investigation of this, see Bob Ward’s recent blog post<\/a>.<\/p>\n

      Phase 5 above might be the longest phase in the restore process if there were any long-running, uncommitted transactions when the backup was performed. This will be even more so if there are a very large number of virtual log files (thousands) in the transaction log, as that hugely slows down the mechanism that rolls back uncommitted transactions.<\/p>\n

      Here’s a list of things you can do to make restoring a full backup go faster:<\/p>\n