|
|||||||||||||||||
|
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.
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.
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.
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:
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.
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:
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
|
||||||||||||||||||||||||||||