Disaster Recovery, Backup of "System Volumes" Available

The entire server is being recovered after a catastrophic failure. An image snapshot of the system volumes (those volumes containing Windows and SQL Server installations) is available. The system volume snapshot includes current contents of master, model, and MSDB. Conventional log or differential backups are available for some user databases.

This scenario assumes that the snapshot backups are mounted (or copied into place) at the beginning of the sequence as part of the system volume(s). It is also possible to mount or copy the files into place during the execution of the individual restore commands.

Recovery of user databases needing roll-forward is prevented by bringing up the server in minimal mode (-f) to prevent recovery of the databases, then taking these databases offline. The server must not be allowed to come up without this flag, or the capability to roll-forward will be lost. It is usually desirable for SQL Server to start automatically without the –f option, so the snapshot image will not have this option set. Therefore, Microsoft recommends starting Windows in safe mode and adding the –f option to the startup parameters of the SQL Server service. If this is not possible, recovery of a database may also be prevented by temporarily renaming one of the database files.

If the model, msdb and tempdb databases are being relocated, use of the –T3608 flag prevents startup of these databases. Then sp_detach_db and sp_attach_db can be used to point at the new locations. In SQL Server 2005, a better method is available: use ALTER DATABASE MODIFY FILE to tell SQL Server when the files will be located on the next restart.

Steps

• Mount all volumes containing the system image snapshot
  
  
• Start SQL Server in minimal configuration (-f startup parameter)
  
  
• Detach databases for which log or differential backups are available using sp_detach_db.
  
  
• Start SQL Server normally (without –f)
  
  
• For each user database detached {

  Restore database snapshot with NORECOVERY, REPLACE.

  Apply conventional log and/or differential backups and recover.

  }