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
Get Professional Microsoft® SQL Server® 2008 Administration now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
