O'Reilly logo

Professional Microsoft® SQL Server® 2008 Administration by Steven Wort, Ross LoForte, Wayne Snyder, Ketan Patel, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

18.8. Performing Recovery

This section explains the various methods of recovery, through both Management Studio and T-SQL. You will also learn how to restore the system databases.

18.8.1. Restore Process

It is a DBA's task to ensure that backups are consistently taken and validated in order to restore. Each backup sequence is labeled and stored to enable quick identification to restore a database.

18.8.1.1. Instant File Initialization

Versions prior to SQL Server 2005 required file initialization by filling the files with zeros to overwrite any existing data inside the file for the following SQL Server operations: creating a database; adding files, logs, or data to an existing database; increasing the size of an existing file; and restoring a database or filegroup. As a result, for a large database, file initialization would take significant time. Beginning with SQL Server 2005, however, data files can use instant file initialization, provided that the SQL Server service account is assigned to the Windows SE_MANAGE_VOLUME_NAME permission, which can be done by assigning the account to the Perform Volume Maintenance Tasks security policy. Instant file initialization reduces the time required to create a database or perform other tasks by initializing the new file areas with zeros.

Instant file initialization works only on Windows XP and Windows 2003 and later versions. In addition, the transaction-log file cannot be instant-file initialized.

18.8.1.2. Full Database Restore

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required