Many administrators believe that if they take a backup of their user databases, they are on the safe side.

Indeed, on a disaster recovery scenario they can rebuild Windows, they can then rebuild the Windows Cluster and they can reinstall the SQL Server failover instance. Then they can restore the database data and manually recreate the logins, the DTS/SSIS packages, the linked servers, e.t.c.

But how much time will these operations take? And will you remember exactly how the SQL server was configured?

This guide will help you to have your SQL Server cluster up and running ASAP by having all the needed data and configuration exported and ready to be used to restore any part of your SQL Server installation.

Consider this typical SQL Server scenario: A Windows 2003 Cluster with a SQL Server 2005 Cluster.

1.     Operating system is on C: (local disk)

2.     SQL Server Program Files are on C: (local disk)

3.     Cluster Quorum is on drive Q: (storage disk)

4.     SQL Data files are on drive M: (storage disk)

5.     SQL Log files are on drive L: (storage disk)

6.     DTS/SSIS packages

7.     Linked servers

8.     Backup Devices

9.     SQL Server logins

10.  Databases that are Replicated

11.  SQL Server Agent Jobs

12.  Maintenance Plans

13.  Operators

14.  Analysis Services

15.  Reporting Services

Let’s see how we will backup all these components:

For components 1, 2, 3 and maybe 6:

In my personal experience, the most reliable method of backing up the OS, is via the Windows builtin tool called ntbackup. To set it up you may follow these steps for both nodes (shared disks will be accessible from only one of the nodes):

·          Click on Start | Run and type ntbackup to start this tool.

·          Leave it on Wizard mode and click on next.

·          Choose to backup “File and Settings” and choose next.

·          Check “Let me choose what to back up” and click Next. Choose:

o    Local disk where your OS is located (drive C: in this example). You can exclude any folders that are irrelevant to the OS.

o    The system state (which includes vital data like the registry, boot files, e.t.c.).

o    The Quorum disk (Q: in our example).

o    The folder where you keep your DTS/SSIS packages (if you don’t keep them inside the MSDB database).

·          Create a schedule for this backup, so as to take place without requiring any manual intervention.

For components 4 and 5 it is preferable to back them up from SQL Server, via the SQL Server Management Studio (SSMS):

·          Login into SQL Server using Management Studio.

·          Create a new Maintenance Plan (using the wizard) that will take a FULL backup of the user databases e.g. on a daily basis. You can then schedule other Differential backups throughout the week to save backup space and time.

·          You will also need to have a backup of the system databases (master, model, msdb). You will need to back them up only if you make any changes (e.g. if you install a hotfix, add an SSIS package, change SQL Server configuration, e.t.c.).

For component 6, as already mentioned you can:

·          Take a backup of the package files if you have them stored in the filesystem.

·          Otherwise take a backup of the MSDB database.

For component 7 you can directly export a linked server’s configuration from inside SQL Server Management Studio:

·          Right click on the linked server.

·          Choose “Script linked server as” | “Create to” | “File”.

·          This operation will create a T-SQL script that you can execute to recreate the linked server.

For component 8, you can do the same as with 7 (export the backup device configuration into a .sql file.

For component 9, you can use this KB article to export the SQL Server logins into a script: 

How to transfer the logins and the passwords between instances of SQL Server 2005 (http://support.microsoft.com/kb/918992)

For component 10, under Replication in SSMS, you can right click on the publication and choose to Generate scripts. This will save the replication configuration into a .sql file.

For component 11, you can again right click on a job and export it as a script in a .sql file (see component 7 for more details).

For component 12, you can export the maintenance plans:

·          Connect to Integration Services on the server where the maintenance plans are located.

·          Go to Stored Packages | MSDB | Maintenance Plans.

·          Right click on Maintenance Plans and select export.

For component 13, again you can right click on an operator and export his configuration as a CREATE TO script.

For component 14, you can use this technet article:

Backing Up and Restoring an Analysis Services Database (http://technet.microsoft.com/en-us/library/ms174874(SQL.90).aspx)

For component 15, you can use this MSDN article:

Backup and Restore Operations for a Reporting Services Installation (http://msdn.microsoft.com/en-us/library/ms155814(SQL.90).aspx)

If you can spot a component I have missed, please send me a comment and I will add it to this list.