How does SQL Server speed up database restoration?

The factors that affect the database restoration speed are the same as those that affect the database backup speed. In addition, if you use SQL Server 2005, you can start another optimization task to restore a database that does not exist currently. The running environment is Windows XP, Windows 2003 Server, or an updated version.

Perform Volume Maintenance Tasks

When you restore a new complete database, SQL Server reads the backup file header and creates the disk space required for data and log files in the original database. If the SQL Server service startup account does not have the "Perform Volume Maintenance Tasks" permission, the data and log files must be initialized to 0, that is, SQL Server first creates these files, then fill them with 0. For a large database, this will take a lot of time. I remember that when I used SQL Server 2000 to restore a GB database from a tape, I always wondered why there was always 30 minutes. At all, the restoration process said there was no progress.

Then, if the SQL Server service startup account has the "Perform Volume Maintenance Tasks" permission, it will create data files based on the size and skip the "fill 0" phase.

Use secpol. MSC to display Permissions

You can imagine how much time it will save you to restore large databases. Note that the transaction log file still needs to be "filled with 0". This step can only be skipped for the data file.

Note: when using the new permission, you must start the SQL Server service to make it take effect.

The following table compares the time used to restore 20 GB of data and 5 GB of transaction logs.

	Restoration duration
"Perform Volume Maintenance Tasks" is not used"	5: 05
Use "Perform Volume Maintenance Tasks"	1: 01

The time consumed is because SQL Server still needs to fill the transaction log file with "0" and does not use "Perform volume maintenance tasks, SQL Server needs to fill the data files and transaction logs with "0", so the restoration time is longer.

You can use the following script to quickly determine whether pvmt (Perform Volume Maintenance Tasks) is currently used ).

Create Database test_instantinit on
Primary (name = 'test _ instantinit ', filename = 'K:/temp/test_instantinit.mdf', size = 1 GB)
Log On (name = 'test _ instantinit_log ', filename = 'K:/temp/test_instantinit.ldf', size = 1 MB)
Drop database test_instantinit

If the entire script is completed in a few seconds, pvmt is used.

Here is a little more to note. When SQL Server skips the "fill 0" phase space, if the space occupied by the data file contains the previous data, you can use the DBCC page command or other hexadecimal editor to view the space that is not occupied by the data page. That is to say, if a data that includes sensitive important content has been deleted, but the new database occupies this space, sensitive data may be partially leaked.

Note: When pvmt is active, you can use it to create a database, create a data file, and increase data files. For more information, seeDatabase File Initialization[Sql2005]

In summary, do I need to delete a database before I restore it from a backup file?

The following table shows the effects of different operations on the same data:

	Restore time
Restore A 1 GB Database	0: 40
2 GB database Restoration	1: 08
Restore A 1 GB database. A 2 GB database with the same name exists.	0: 29
Restore A 2 GB database. A 1 GB database with the same name exists.	0: 56

The results show that if you perform a full database recovery and overwrite an existing database with the same name, the recovery speed will be faster than direct recovery (table row 1 and row 3, or line 2 and Line 4 ). This seems to be because the "fill 0" operation has not been performed on existing data files, saving time. However, this is only limited to the fact that the recovered database has files of the same name. If you use the move option to relocate database files, no matter whether you have deleted the database or not, there is no difference.

The restoration status also affects the restoration speed.

Another factor affecting restoration speed is the status of the restored database you selected, provided that the recovery is not selected. Generally, to prepare for future upgrades, you can use norecovery or standby if you choose not to completely recover the database. Norecovery puts the database in the "restoring" mode, allowing you to perform subsequent upgrades, and the database is not readable at this time. Standby also puts the database in the "restoring" status, allowing you to perform subsequent upgrades, but the database is readable at this time.

When you use the standby option, you must provide a name for the rollback file. This file includes the rollback operation results in transactions that have never been prompted. The more uncommitted transactions you have, the larger the file, the longer the restoration time.

The following example contains four transaction logs, each of which is about MB. Except for the third transaction log, all backups only include committed transactions. The third transaction log contains 32 MB uncommitted transactions. The result is as follows:

Use the norecovery option to restore transaction logs:

Use the standby option to restore transaction logs:

In general, using standby to restore transaction logs is slower than norecovery. This is because when there are uncommitted transactions, SQL Server will spend additional time creating a rollback file (UNDO file ).

It is also worth noting that if you want to restore multiple transaction logs and make the database in read-only mode, you should first use the norecovery option to restore the transaction log, after all the logs are restored, you can switch the database to the read-only mode of standby, as shown below:

Restore database mydb with standby = 'G:/data/mydb/mydb_und.dat'

By using this method, you only create a rollback file once, avoiding the process of creating multiple rollback files when restoring multiple transaction logs, and accelerating the recovery process.

 

This article translated from sqlbackuprestore, more exciting content please visit http://www.sqlbackuprestore.com