Speeding up restores

Prev Page Next Page
Recovery models
Main backup types
Backing up the database files by copying
The transaction log
Transaction log restore sequence
Log sequence numbers
Truncating and shrinking the transaction log
Backing up the tail
Inside the transaction log
So, what's in a backup file?
Test: A full backup does not contain deleted data
Verifying backup files
Verifying backup files on a budget
Cumulative backups
Recovering individual tables
Backup and restore history details
Backup reads and writes
Speeding up backups
Backup speed details
Speeding up restores
Restore state affects speed too
Backup and restore rights
Log shipping
Log shipping in SQL Server 2000
Setting up log shipping using Enterprise Manager
Checking the set up
Log shipping in SQL Server 2005
Setting up log shipping using Management Studio
Checking the set up
Log shipping status report
Log shipping in SQL Backup
Using the CopyTool utility
3rd party backup applications
VDI versions
VDI errors
SQL Backup - beyond compression
Restoring a chain of transaction log backups
Restoring to the latest possible state
Backing up multiple databases
Backup retention
Making a copy of the backup file
Backup file naming conventions
Restoring the latest backup set
Network resilience
Integrated database verification
Database file relocation
Improved backup retention
High-availability group support
Common SQL Backup issues
Installation checklist
Setting up rights
Configuring service rights
Backup data
Hanging issues
Common backup and restore errors
Error 3201 - when performing a backup to a network share
Full database backup file is larger than database size
Error 3205 - Too many backup devices specified for backup or restore
Error 4305 - an earlier transaction log backup is required
Bringing a database that is in recovery or read-only mode online
Using bulk-logged recovery model but transaction log backup is still large
Error 14274 - unable to delete SQL Server Agent job
Error messages when restoring from different versions of SQL Server.
vdi error codes
Restore speed details
Help, my transaction log file is huge!
Mirror or log ship

The factors affecting restore speed is identical to that affecting backup speed, as discussed in this article.  However, there is one additional optimization task you can do, if you are running SQL Server 2005, restoring a database that currently does not exist, and are running on Windows XP (this isn't a production system, right?), Windows 2003 Server or later.

When you restore a full database backup as a new database, SQL Server will read the backup file(s) header and create the necessary data and log files sized identically to the original database.  If the SQL Server service startup user does not have the 'Perform Volume Maintenance Tasks' right, the data and log files will need to be initialized with zeros i.e. SQL Server will create the files and fill them with zeros.  For a large database, this will take a while.  I remember restoring a 320 GB database from tape using SQL Server 2000, and was always wondering why for the first 30 minutes, there was no progress in the restore.

However, if the SQL Server service startup user has the  'Perform Volume Maintenance Tasks' right, it will just create the data files, size them accordingly but skip the 'zero-ing out' stage.

   Assigning rights using secpol.msc   


You can imagine how much time this will save when restoring a large database.  Note that the transaction log files will still need to be 'zero-ed' out.  Only the data files can skip this stage.

When you have newly assigned the right, you need to restart the SQL Server service for it to take effect.

Here are some timings on the duration taken to restore a that has a 20 GB data file and a 5 GB transaction log file.

Time taken to restore

Without instant file initialization


With instant file initialization


The 1:01 duration was because SQL Server still had to zero-out the transaction log file.  Without instant file initialization, SQL Server had to zero-out both the data file and transaction log file, and the duration was proportionately longer.

Here is a script you can use to quickly determine if instant file initialization is currently used:

PRIMARY (name = 'test_InstantInit', filename = 'k:\temp\test_InstantInit.mdf', size = 1GB)
LOG ON (name = 'test_InstantInit_log', filename = 'k:\temp\test_InstantInit.ldf', size = 1MB)
DROP DATABASE test_InstantInit

The entire script should only take a couple of seconds if instant initialization is used.

There is one caveat to doing this.  When SQL Server skips the 'zero-ing' out phase, the data previously contained in the space now occupied by the data files can be viewed using the DBCC PAGE command, or a hex editor, if the space is not occupied by database pages.  This means that if you had a file containing sensitive data, which has now been erased, and the space is now used by the database, users can potentially view fragments of that file.

Instant file initialization, when active, will also be used when creating new databases, adding new data files, and when the data files has to grow. See Database File Initialization [SQL2005].

Should I delete a database before restoring it from a backup?

Strangely enough, this has an effect on restore times too, if instant file initialization is not in use e.g. you're using SQL Server 2000, or the 'Perform Volume Maintenance Tasks' right is not granted to the SQL Server service startup account.

The following table shows the effects of different actions during a restore of the same database:

Time taken to restore

Restore a 1 GB database from scratch


Restore a 2 GB database from scratch


Restore the 1 GB database without deleting the same existing database currently 2 GB in size


Restore the 2 GB database without deleting the same existing database currently 1 GB in size


It would appear that if you are going to perform a full database restore over an existing database, the restore would complete faster if the existing database was not dropped first.  It looks like the time savings comes from not having to zero-out the existing data files. However, this would only work if you are restoring the database to the same files.  If you use the MOVE option to relocate the database files, it would make no difference whether you dropped the database first or not.

Document history
8/1/2008    Initial release.    
Copyright 2008 - 2021 Yohz Ventures Sdn Bhd. All rights reserved.
All product and company names are trademarks or registered trademarks of their respective owners.