Recovering from a Failure of SQL Server 2005

Recovering from a failure of SQL Server 2005 can be a complex and time-consuming task. Each SQL Server 2005 solution potentially has its own customizations that need to be taken into account. Therefore, it is important to devise a document of disaster recovery procedures in case of a SQL Server 2005 failure.

We cannot stress enough the importance of documenting the series of steps required. The more detailed, the better. Inevitably, whenever there is a SQL Server 2005 failure, there will be a lot of pressure on the DBA team to recover the SQL Server 2005 solution as quickly as possible. In this high-pressure situation, it is easy to overlook or forget certain critical steps or to take longer to perform these steps in the correct sequence. Documentation helps a DBA quickly and easily recover from a SQL Server 2005 failure because it should contain instructions regarding what to do and where to go to for certain information.

The following represents an example of a disaster recovery plan, at a high level:

1. Install and configure the replacement hardware.

2. Install the Windows operating system.

3. Upgrade the Windows operating system:

   1. Install the appropriate service packs.

   2. Install the appropriate fixes.

4. Install SQL Server 2005.

5. Upgrade SQL Server 2005:

   1. Install the appropriate SQL Server 2005 service packs.

   2. Install the appropriate SQL Server 2005 hotfixes.

6. Restore the system databases:

   1. master

   2. msdb

   3. model (optional)

7. Restore the user databases:

   1. Full database backups

   2. Differential backups

   3. Transaction logs

8. Implement outstanding changes from the change management process of the database objects not restored in steps 6 and 7:

   1. Create database objects.

   2. Create security objects.

      1. Logins

      2. Certificates

      3. Linked servers

      4. Database users

      5. Database roles

      6. Create SQL Server Agent jobs.

9. Synchronize data from paper trails.

Note 

A disaster recovery plan (DRP) should typically contain the contact details of all the stakeholders of the SQL Server 2005 solution in case there is a need to contact them if the SQL Server 2005 solution fails.

Restoring the databases depends on the backup strategy that you have implemented, so you should examine that in more detail.

Tip 

If your SQL Server 2005 installation has a corrupted master database, you need to run the setup.exe program. You can use the setup program to rebuild the system databases, as well as rebuild, verify, and repair a SQL Server 2005 instance.