InfoScale™ 9.0 Storage Foundation Quick Recovery Solutions Guide for Microsoft SQL Server - Windows

Last Published:
Product(s): InfoScale & Storage Foundation (9.0)
Platform: Windows
  1. Introducing Quick Recovery for SQL Server
    1.  
      About Quick Recovery snapshot solutions
    2.  
      About snapshot-assisted backups
    3.  
      Advantages of Quick Recovery snapshots
    4.  
      Quick Recovery process
    5. Methods of implementing Quick Recovery snapshots for SQL Server
      1.  
        About the Quick Recovery Configuration Wizard
      2.  
        About the VSS Snapshot Scheduler Wizard
      3.  
        About the VSS Snapshot and Snapback wizards and the vxsnap utility
    6. About the components used in Quick Recovery
      1.  
        FlashSnap and FastResync
      2. Integration with Microsoft Volume Shadow Copy Service
        1.  
          VSS framework
        2.  
          VSS process
    7.  
      VCS, Microsoft clustering, and Volume Replicator considerations
    8.  
      About the Solutions Configuration Center
    9.  
      Starting the Configuration Center
    10.  
      Solutions wizard logs
  2. Preparing to implement Quick Recovery for SQL Server
    1.  
      Tasks for preparing to implement Quick Recovery for SQL Server
    2. Reviewing the prerequisites
      1.  
        Storage requirements and best practices
      2.  
        Configuration requirements and best practices
    3.  
      Reviewing the configuration
    4. Configuring SQL Server storage with Storage Foundation for Windows
      1.  
        Creating dynamic disk groups
      2.  
        Creating dynamic volumes
      3.  
        Pointing the databases and log paths to the SFW volumes
  3. Implementing Quick Recovery for SQL Server with the configuration wizard
    1. About the Quick Recovery Configuration Wizard
      1.  
        Backup types for snapshot sets
      2. About snapshot templates
        1.  
          Templates and multiple components
        2.  
          Templates and schedule start dates
    2.  
      Tasks for implementing snapshot sets with the configuration wizard
    3.  
      Reviewing the prerequisites
    4. Scheduling SQL Server snapshot sets
      1.  
        System Selection panel details
      2.  
        Instance Selection panel details
      3.  
        Mount Details panel details
      4.  
        Synchronizing Schedules panel details
      5.  
        Template Selection panel details
      6.  
        Number of Snapshot Sets panel details
      7.  
        Snapshot Volume Assignment panel details
      8.  
        Snapshot Schedule panel details
      9.  
        Specifying snapshot schedule details
      10.  
        Summary panel details
      11.  
        Template Implementation panel
  4. Scheduling or creating an individual snapshot set for SQL Server
    1.  
      About scheduling or creating an individual snapshot set
    2.  
      Tasks to schedule a new snapshot set
    3.  
      Tasks to create a one-time snapshot set
    4.  
      Reviewing the prerequisites
    5.  
      Preparing the snapshot mirrors
    6.  
      Scheduling a new snapshot set
    7.  
      Creating a one-time snapshot set
    8. Refreshing a snapshot set
      1.  
        Reattaching the split-mirror snapshots
  5. Maintaining or troubleshooting snapshots
    1.  
      Viewing the status of scheduled snapshots
    2.  
      Troubleshooting scheduled snapshots
    3.  
      Deleting or modifying schedules
    4.  
      Synchronizing schedules after adding a cluster node
  6. Recovering a SQL Server database
    1.  
      About recovering a SQL Server database
    2.  
      Tasks for recovering a SQL Server database
    3.  
      Prerequisites for recovering a SQL Server database
    4.  
      Types of recovery
    5.  
      Recovering using snapshots without log replay
    6.  
      Recovering using snapshots and log replay
    7.  
      Restoring snapshots and manually applying logs
    8. Recovering missing volumes
      1. Preparing for the recovery
        1.  
          Reassigning the drive letter or mount points of the missing volumes to the snapshot volumes
        2.  
          Replacing hardware and adding disks to the dynamic disk group
      2.  
        Performing the recovery
    9.  
      Post-recovery steps
    10.  
      Vxsnap restore command reference
  7. Vxsnap utility command line reference for SQL Server
    1.  
      About the vxsnap utility
    2. Vxsnap keywords
      1.  
        vxsnap prepare
      2.  
        vxsnap create
      3.  
        vxsnap reattach
      4.  
        vxsnap restore

Recovering using snapshots and log replay

The following procedure restores the database from the snapshot set volumes and applies the backed up transaction log files that you specify to automatically roll forward the recovery to the point of failure.

You can choose the procedure for the VSS SQL Restore Wizard or for the vxsnap restore command.

Warning:

Before you begin, use your preferred method to backup the transaction logs within SQL Server. You must use the "overwrite existing media" option to create uniquely-named backup files.

To use log replay for an automatic roll-forward recovery to a point of failure using the VEA

  1. Ensure that you have backed up the transaction logs within SQL Server using the "overwrite existing media" option to create uniquely-named backup files.
  2. Close the SQL GUI and all Explorer windows, applications, consoles (except the VEA), or third-party system management tools that may be accessing the volumes. It is also recommended to bring the database offline.
  3. From the VEA console, navigate to the system where the database volumes are located.

  4. Expand the system node, the Storage Agent node, and the Applications node
  5. Right-click SQL and click VSS Restore.
  6. Review the Welcome page and click Next.
  7. Select the snapshot set XML metadata file to be used for this operation and click Next.

    The XML metadata file contains all required information needed to restore the snapshot set, including the names of the database and transaction logs volumes. Click the appropriate header to sort the list of available files by File Name or Creation Time.

  8. On the Select Restore Type panel, do the following and click Next:

    • Click Recovery + Log replay.

    • Enter the full path of each log file, in the order they are to be applied.

  9. You may receive a message "Some volumes in this component have open handles. Do you want to override these handles and do this restore? Click Yes to proceed." Click No, close any open handles and retry the command.
  10. Verify the restore specifications and click Finish.

    After the most recent backup log is replayed, the SQL Server database is closed and left in an operational state. If you took it offline earlier, bring it back online.

  11. The restore operation leaves the snapshot volumes snapped back to the production volumes. To ensure that another split-mirror snapshot set is immediately available, use the VSS SQL Snapshot Wizard to create a new snapshot of all the volumes in the database.

To use log replay for an automatic roll-forward recovery to a point of failure using the vxsnap restore command

  1. Ensure that you have backed up the transaction logs within SQL Server using the "overwrite existing media" option to create uniquely-named backup files.
  2. Close the SQL GUI and all Explorer windows, applications, consoles, or third-party system management tools that may be accessing the volumes. It is also recommended to bring the database offline.
  3. Type the command as in the following example:
    vxsnap -x billing_DB.xml restore RestoreType=RECOVERY
   logFiles=c:\backup\tLog1.bak, c:\tLog2.bak

    where billing_DB.xml is the name of the metadata file generated by the vxsnap create command and c:\backup\tLog1.bak, c:\tLog2.baklog1,log2 are the paths to the transaction log backup files. After the most recent backup log is replayed, the SQL Server database is closed and left in an operational state. If you took it offline earlier, bring it back online.

  4. The restore operation leaves the snapshot volumes snapped back to the production volumes. To ensure that another split-mirror snapshot set is immediately available, use the vxsnap create command to create a new snapshot of all the volumes in the database.