Restoring SQL Server databases from virtual machine snapshots

You can restore SQL Server databases from virtual machine snapshots when IBM Spectrum Protect™ for Virtual Environments, Version 7.1 and later is used to back up the data. Before you restore the SQL Server database, you must verify that the data is backed up correctly.

Before you begin

  • Before you restore SQL Server databases form virtual machine snapshots, verify that the data is backed up according to the following procedure.
  • You can restore the SQL Server data to basic disks with MBR-style partitions. Because of an SQL Server limitation, however, you cannot restore a VSS backup to an alternative SQL Server instance. VSS backups must be restored to the same SQL Server instance where the snapshot is created.

    Also, when you restore an SQL Server database from a VM backup, the restore operation is not possible if the VM name is changed after the VM backup.

Verifying the SQL Server backup

Procedure

  1. Install the IBM Spectrum Protect for Virtual Environments Recovery Agent 7.1 package and the IBM Spectrum Protect Backup-Archive Client 7.1 from the Data Protection for VMware 7.1 package. These software packages are available for download from Passport Advantage®.

    Install these packages on the guest virtual machine with Data Protection for SQL Server. Ensure that the guest virtual machine meets the operating system and application prerequisites. For more information, see Application protection for Microsoft Exchange Server and SQL Server.

  2. Specify the following IBM Spectrum Protect Backup-Archive Client 7.1 option in the dsm.opt file: 
    INCLUDE.VMTSMVSS vmname

    When you set this option, virtual machine applications receive a notification when a backup is scheduled to occur. This notification allows the application to truncate transaction logs and commit transactions so that the application can resume from a consistent state when the backup completes. By default, this option is not enabled. You must set this option to enable application protection for a virtual machine.

    The vmname specifies the name of the virtual machine that contains the applications to quiesce. Specify one virtual machine per INCLUDE.VMTSMVSS statement. To protect all virtual machines with this option, use an asterisk as a wildcard. For example: 
    INCLUDE.VMTSMVSS *
    You can also use question marks to match any single character. For example:
    INCLUDE.VMTSMVSS vm??
    This type of option setting protects all virtual machines that have names that begin with vm and are followed by any two characters. For example, vm10 and vm15.
    If the OPTions KEEPSqllog parameter is specified in an INCLUDE.VMTSMVSS statement, this parameter prevents SQL Server logs from being truncated when a data mover node backs up a virtual machine that runs an SQL Server. Specifying this parameter allows the SQL Server administrator to manually manage the SQL Server logs. The logs can be preserved as required and be used to restore SQL Server transactions to a specific checkpoint, after the virtual machine is restored. When this option is specified, the SQL Server log is not truncated and following message is displayed and logged on the server: 
    ANS4179I IBM Tivoli Storage Manager application protection did not truncate 
Microsoft SQL Server logs on virtual machine vmname
    IBM Spectrum Protect does not back up the SQL Server log files. The SQL Server administrator must back up the log files so that those logs can be applied after the database is restored.
  3. Verify that the VSS service and SQL Server instance are online and active. SQL Server databases that do not have an active instance are backed up. However, information about these databases is not saved to IBM Spectrum Protect. Therefore, these databases are not available for a database-level restore operations. You can restore these databases with a full VM restore operation.
  4. Use the IBM Spectrum Protect for Virtual Environments software to back up the data.
  5. After you back up data, verify that the virtual machine backup contains the necessary database metadata.
    1. Enter the following IBM Spectrum Protect Backup-Archive Client command on the data mover: 
      dsmc query vm <vmname> -detail
    2. In the command output, ensure that the Application(s) protected: value specifies (database-level recovery) For example:
      # Backup Date Mgmt Class Size Type A/I Virtual Machine
--- ------------------- ---------- ----------- ------ --- ---------------
1 06/07/2012 19:25:58 STANDARD 29.29 GB FULL A wombat
The size of this incremental backup: n/a
The number of incremental backups since last full: n/a
The amount of extra data: n/a
The TSM objects fragmentation: n/a
Backup is represented by: n/a
Application protection type: TSM VSS
Application(s) protected: MS SQL 2008 (database-level recovery)
VMDK[1]Label: Hard disk 1
VMDK[1]Name: [ess800_dev1] wombat/wombat-000002.vmdk
VMDK[1]Status: Protected

Restoring the SQL Server database

Procedure

  1. Log on to the system where you want to restore the SQL Server database. The Data Protection for VMware Recovery Agent license and Data Protection for SQL Server must be installed on the system where you restore the data.
  2. When Data Protection for SQL Server is configured, for the Configuring Recovery Agent rule, verify that the status is Passed. If the status is not Passed, re-run the configuration wizard. On the IBM Spectrum Protect Node Names wizard page, enter the data center node name. The data center node is the virtual node that maps to a data center.
  3. Set access to the virtual machine that is backed up in a data center node as shown in the following table:
    Table 1. Node names used to set access
    Node name Location Description Proxy type
    DC_NODE Data mover Node for the virtual machine backup Agent (data owner)
    SQL_NODE In guest virtual machine running Microsoft SQL Server Node for Data Protection for SQL Server Agent (data owner)
    VSS_NODE In guest virtual machine running Microsoft SQL Server Node for Data Protection for SQL Server DSMAGENT Agent (data worker)
  4. Ensure that the IBM Spectrum Protect administrator provides access to the virtual machine that is backed up to the VSS_NODE. The IBM Spectrum Protect administrator can use the IBM Spectrum Protect command-line interface to enter the set access command while connected to the DC_NODE. Enabling access is required for Data Protection for SQL Server to restore the data that is owned by the DC_NODE.

    Running the commands from the DC_NODE is best practice because the options file has the necessary settings to communicate with the IBM Spectrum Protect server. The IBM Spectrum Protect administrator credentials can be used if the DC_NODE administrator password is not available.

  5. The set access command cannot be run if the ASNODE option is used. To issue the set access command, use an option file that does not contain ASNODE.
    1. Copy dsm.opt and dsm.setaccess.opt files.
    2. If you run the set access command from a node with ASNODE in the options file, edit the dsm.setaccess.opt file. For any line that contains ASNODE, remove the line.
    3. Edit the dsm.setaccess.opt file to set the NODENAME option to the following entry: 
      DC_NODE NODENAME DC_NODE
    4. Enter the following command: 
      dsmc set access backup -type=VM traveler VSS_NODE -optfile=dsm.setaccess.opt
      You might be prompted to enter the password for the DC_NODE.
    For any subsequent set access, query access, and delete access commands, repeat these steps.
  6. From the Protect and Recover Data section in MMC, select an SQL Server.
  7. On the Recover tab for that SQL Server, select View: Databases to see a list of SQL Server database backups that are available to restore. SQL Server databases that are backed up with IBM Spectrum Protect for Virtual Environments software are listed with the VMVSS backup method.
  8. Verify the restore options. If the restore options are not displayed, click Show Restore Options.
  9. Optional: Choose a mode for the current task:
    • Run Interactively: Click this item to run the current task interactively. This selection is the default.
    • Run Scheduled: Click this item to convert the current action into a scheduled task. When you select this item, the schedule wizard begins, complete with the command that is required to complete the task.
  10. On the Actions pane, click Restore to begin the restore operation.
  11. To view the status of the restore operation, click Task List in the lower half of the results pane. Click Task Details to view detailed status information.