Recovering SQL Server databases using SnapMirror

After the failure of a storage system or a volume on a storage system, you can recover SQL Server databases that are mirrored using SnapMirror, as described in the following topics:

Preparing for disaster recovery using mirrored volumes

To be able to recover SQL Server databases using SnapMirror, you must complete the following disaster recovery preparation tasks:

Configure SnapMirror to replicate SQL Server database backups to mirrored volumes

You can configure SnapMirror to use a destination volume on the same storage system or a different, remote storage system. Whether the destination volume is on the same or a different storage system as the failed volume, the disaster recovery procedure is largely the same. For more information about configuring the Data ONTAP SnapMirror feature, see Data ONTAP Data Protection Guide for your particular version of Data ONTAP.

Note the drive letter mappings

For each LUN on the SnapMirror source volume, note the relevant drive letter mapping on the SQL Server computer. You need to use the same mappings during the disaster recovery procedure when you use SnapDrive to connect to the corresponding LUNs on the SnapMirror destination volume.

Procedure overview

The procedure entails recovering the relevant LUNs--the LUNs containing Snapshot copies of the databases to be restored--from the SnapMirror destination volume.

Basic procedure

First use SnapDrive to connect to the corresponding LUNs in the SnapMirror destination volume. Then use the SQL Server management tool (Enterprise Manager or SQL Server Management Studio) to attach the database located on the SnapMirror destination volume.

If you cannot attach the database on the SnapMirror destination volume and the transaction log files are intact, follow the steps that describe how to minimize data loss by ensuring that SnapManager restore automatically back up the last active transaction log of the database.

If you succeed in recovering the necessary LUNs, you can use SnapManager to restore from the most recent backup set. Some of the details of this phase of the recovery procedure depend on the nature of the storage system or volume failure:

Only SQL Server database data files were lost
Only SQL Server transaction log files were lost
Both SQL Server database files and transaction log files were lost

If the transaction log files were lost

The recovery procedure includes special steps you must take if the transaction files were lost:

If the transaction log files were lost, the active transactions were lost and are unrecoverable. Because the active transactions are unavailable, you must use SnapManager to perform a point-in-time restore and not an up-to-the minute restore.
For descriptions of the two types of restore operations, see Types of SnapManager restore operations.
In addition, the SnapManager Restore from the SnapMirror destination volume must be performed with the transaction log backup option disabled if the transaction log files were lost.
If you fail to disable this transaction log backup, subsequent SnapManager backup sets reside on a recovery path that is inconsistent with that of the database. Such backup sets cannot be applied to the database; attempts to restore the database from such backup sets results in failure, with the following error message in the restore log:

Failed with error code 0x800410df

If this occurs, perform the restore again, but do not apply transaction logs.

Performing disaster recovery using mirrored volumes

To recover SQL Server databases whose backup sets have been mirrored using SnapMirror, complete the following steps.

Step	Action
1	If...	Then...
	You are recovering SQL Server 2000 databases	Do the following: Stop the SQL Server. Continue with Step 2.
	You are recovering SQL Server 2005 databases	Go to Step 2.
2	If any LUNs from the failed source volume still appear to be connected, disconnect them.
3	Use SnapDrive to connect to the corresponding LUN or LUNs in the SnapMirror destination volume. Note Use the same drive letters for connecting to the mirrored LUNs that were used on the source volume. Result For each mirrored volume, SnapDrive breaks the mirror and restores the LUN using the most recent Snapshot copy generated by SnapDrive or SnapManager.
4	Restart SQL Server if it has been stopped.
5	Use SQL Server Enterprise Manager or SQL Server Management Studio to attach the database located on the associated LUNs in the SnapMirror destination volume, as follows:
	If...	Then...
	You succeeded in attaching the database	Complete this procedure as described in Step 6.
	The database could not be accessed using SQL Server Enterprise Manager or SQL Server Management Studio	Complete this procedure as described beginning with Step 10.
	You were unable to attach the database	Complete this procedure as described beginning with Step 10.
If you attached the database on the SnapMirror destination volume...
6	The steps for restoring the database depend on whether the transaction log volume was lost:
	If...	Then...
	You lost only the data files of the database	Complete this procedure as described in Step 7.
	If you lost only the transaction log files of the database	Complete this procedure as described in Step 8.
	If you lost both the data files and the transaction log files of the database	Complete this procedure as described in Step 8.
Performing the restore if only the data volume was lost
7	Run SnapManager and use the newest full database backup to perform either an up-to-the-minute restore or a point-in-time restore: For an up-to-the-minute restore, SnapManager automatically backs up the last active transaction log before performing the restore. For a point-in-time restore, select the backup set, a combination of transaction log backups to be restored, or both. For more information, see the following topics: For information about identifying the most recent full database backup, see SnapManager backup set names. For information about identifying the transaction logs, see SnapInfo subdirectory names and Transaction log backup. For information about performing a restore operation, see Performing a restore operation. The procedure is now finished.
Performing the restore if the transaction log volume was lost
8	Disable the option to back up the transaction log prior to performing the restore: From the SnapManager menu bar, select Options > Restore Settings. In the Restore Settings dialog box, deselect the "Create transaction log backup before restore" option. Click OK. For more information, see SnapManager restore options. The reason you must disable this restore option is that the active transactions were lost due to the failure of the volume containing the transaction log.
9	Run SnapManager and use the newest full database backup to perform a point-in-time restore. Note Because the transaction log volume was lost, an up-to-the minute restore is not possible. For a point-in-time restore, select the backup set, a combination of transaction log backups to be restored, or both. For more information, see the following topics: For information about identifying the most recent full database backup, see SnapManager backup set names. For information about identifying the transaction logs, see SnapInfo subdirectory names and Transaction log backup. For information about performing a restore operation, see Performing a restore operation. The procedure is now finished.
If you did not attach the database on the SnapMirror destination volume...
10	If you cannot attach the database on the SnapMirror destination volume and none of the transaction log files were lost, then--to reduce the loss of data--ensure that the last active transaction log of the database is automatically backed up by SnapManager Restore: See Microsoft KB article 253817, "HOW TO: Backup the Last Transaction Log When the Master and the Database Files Are Damaged." This article describes how you can back up the currently active transaction log even if the SQL Server 2000 database file is damaged, provided that the transaction log file is still accessible. Use this same Microsoft KB article as a general guide for gaining access to the last active transaction log of the database on the SnapMirror destination volume. While referring to the steps in that article, observe the following key points: When you create a similar database that contains the same number of data and transaction log files as the original database on the SnapMirror destination volume, you are creating the database you will be restoring using SnapManager. Instead of using the SQL Server `Backup Log` command to back up the transaction log (as described in the Microsoft article), proceed to the next step in this procedure. For information about identifying the transaction logs, see SnapInfo subdirectory names and Transaction log backup. Attention Do not use SQL Server Enterprise Manager or SQL Server Management Studio to back up the last active transaction log. Due to file formatting differences between SnapManager backups and Enterprise Manager or Management Studio backups, SnapManager cannot perform a restore operation using transaction logs that were backed up using Enterprise Manager or Management Studio. If any of the transaction log files were lost, no workaround is possible and you cannot minimize data loss.
11	Use SnapManager Restore to automatically back up the last active transaction log of the database. Start the SnapManager application. Select Options > Restore Settings, and ensure that the "Create transaction log backup before restore" option is enabled. This causes SnapManager Restore to automatically back up the last active transaction log before actually performing the restore portion of the operation. Use the newest full database backup to perform either an up-to-the-minute restore or a point-in-time restore to the new database you created in the previous step. For information about identifying the most recent full database backup, see SnapManager backup set names. For general information about performing a restore operation, see Performing a restore operation.