Disaster Recovery of Microsoft SQL Server

So your Microsoft SQL Server has gone up in smoke, and you need to restore it from backup. How do you go about it?

For this article, we will assume that your SQL server is not your Backup Exec media server, and that you’ve been backing up SQL using the SQL agent, with a combination of Full and Log backups.

Before you can restore SQL Server, you need to recover the server it runs on, so perform a disaster recovery using Backup Exec in the normal manner.

Once you have recovered the server, SQL server will not start. This is because it is missing some of the system databases it needs to start. These files are missing because the .mdf and .ldf files are automatically skipped by Backup Exec during a backup (they are locked in use, and you’re using the SQL agent anyway). To get around this catch-22 situation, when Backup Exec backs up an SQL database, it creates some copies of the system database files. These copies do not have the .mdf and .ldf extensions, and so will be included in the normal filesystem backup.

On your newly recovered SQL server, locate the folder containing the system databases. With SQL 2005, this is C:Program FilesMicrosoft SQL ServerMSSQLData. Within this folder you should find the following files:

  • master$4idr
  • mastlog$4idr
  • model$4idr
  • modellog$4idr

These are the copies of the system databases made by Backup Exec. If you do not have these files, then something has gone wrong! It’s not time to panic yet, as a default master and model database can be used, but that’s not covered by this article.

If you have the following files, delete them (or rename them, just in case). These files will probably not be present, however.

  • master.mdf
  • mastlog.ldf
  • model.ldf
  • modellog.ldf

Now we need to rename our backup copies to their correct names.

  • Rename master$4idr to master.mdf
  • Rename mastlog$4idr to mastlog.ldf
  • Rename model$4idr to model.mdf
  • Rename modellog$4idr to modellog.ldf

The above four files will be set to read-only, which will prevent SQL Server from starting, so change their attribues to clear the read-only flag.

Start the SQL Server service. If it doesn’t start, then something has gone wrong, so use Event Viewer to check for any relevent error messages. SQL Server should now be running, though it doesn’t know about any user databases, and the master database may not be up-to-date.

From Backup Exec, create a restore job, and select only the Master database on your SQL server. Under Settings, Microsoft SQL, select Automate Master database restore. Run the job.

When the master database restore has finished, create another job, and select all the databases other than the master database, selecting both the full backup and any subsequent transaction log backups. Under Settings, Microsoft SQL, select Leave database ready to use. Additional  transaction logs or differential backups cannot be restored. Select the option Overwrite the existing database. In the Consistency check after restore field, select Full check, including indexes.

When the restore completes, your SQL Server should be running with all user databases intact.