Restoring SQL Server data

Use a restore job to restore SQL Server environments from snapshots. Your SQL Server clones can be utilized and consumed instantly through IBM Spectrum Protect Plus Instant Disk Restore jobs. IBM Spectrum Protect Plus catalogs and tracks all cloned instances.

Before you begin

Note the following procedures and considerations before creating a restore job definition:
  • Create and run an SQL backup job. For more information, see Backing up SQL Server data.
  • Review the SQL Server system requirements in Microsoft SQL Server requirements.
  • Before an IBM Spectrum Protect Plus user can implement backup and restore operations, roles and resource groups must be assigned to the user. Grant users access to resources and backup and restore operations through the Accounts pane. For more information, see Managing user access.
  • When completing a production restore to an SQL Server failover cluster, the root volume of the alternate file path must be eligible to host database and log files. The volume should belong to the destination SQL Server cluster server resource group, and be a dependency of the SQL Server cluster server.
  • A restore to an NTFS or FAT compressed volume is not supported because of SQL Server database restrictions. For more information see Description of support for SQL Server databases on compressed volumes.
  • When completing a point-in-time recovery, ensure that both the restore target SQL instance service and the IBM Spectrum Protect Plus SQL Server service use the same user account.
  • When restoring to an alternate location, the SQL Server destination must be running the same version of SQL Server or a later version. For more information, see Compatibility Support.
  • When restoring to a primary instance in an SQL Always On Availability Group environment, the database is added to the target Always On database group. After the primary restore, the secondary database is seeded by the SQL server in environments where automatic seeding is supported (SQL 2016 and later). The database is then enabled on the destination availability group. The synchronization time depends on the amount of data being transferred and the connection between the primary and secondary replicas.

    If automatic seeding is not supported or is disabled, a secondary restore from the restore point with the shortest LSN gap of the primary instance must be completed. Log backups with the latest point-in-time restore point created by IBM Spectrum Protect Plus must be restored if the log backup was enabled on the primary instance. The secondary database restore is completed in a restoring state and you must issue the T-SQL command to add the database to the target group. For more information, see Transact-SQL Reference (Database Engine).

About this task

Instant Disk Restore leverages the iSCSI protocol to provide immediate mount of LUNs without transferring data. Snapshotted databases are cataloged and instantly recoverable with no physical transfer of data.

The following restore modes are supported:
Instant Access mode
In Instant Access mode, no further action is taken after mounting the share. Users can complete any custom recovery using the files in the vSnap volume. An Instant Access restore of an Always On database is restored to the local destination instance.
Test mode
In test mode, the agent creates a new database using the data files directly from the vSnap volume.
Production mode
In production mode, the agent first restores the files from the vSnap volume back to primary storage and then spins up the new database using the restored files.

Procedure

To define an SQL restore job, complete the following steps:

  1. In the navigation pane, click Manage Protection > Applications > SQL > Restore.
  2. In the Restore pane, review the available restore points of your SQL Servers servers.
  3. Use the search function to search for available instances and toggle the displayed instances through the View filter. The available options are Standalone/Failover Cluster and Always On.
  4. To select the latest restore point, click the add to restore list icon the add to restore list icon at the resource level. Select Restore by site or Restore by cloud/repository server. If restoring from a Site, click the Select a site drop-down menu to choose a site associated with the backup storage server you want to restore from. If restoring from a cloud or repository server, the restore source will be automatically selected.

    To select a restore point that is not the latest, expand a resource in the Restore pane, and then click the add to restore list icon the add to restore list icon that is associated with the restore point. Adding a combination of latest restore points and non-latest restore points to the Restore List is not supported. Click the delete icon the remove from restore list icon to remove restore points from the Restore List.

    Additional filtering options are available when viewing non-latest restore points. To view available restore points from sites, cloud resources, or repository servers, expand a resource in the Restore pane, then select the source type through the Filters menu. Once a source type is selected, all available restore points associated with the source type display. To view restore points on a specific resource, select it from the drop-down menu adjacent to the source type drop-down menu. For example, if the filter is set to Sites, click Show restore points in all sites to select a specific site.

  5. To run the job now using default options, click Restore. To schedule the job to run using default options, click Manage Jobs and define a trigger for the job definition.
  6. To edit options before you create the job definition, click Options. Set the job definition options.

    Destination

    Set the restore destination.

    Restore to original instance

    Select to restore to the original instance.

    Restore to primary instance

    When performing restore operations in an SQL Always On environment, select to restore the database to the primary instance of the Always On Availability Group and add the database back to the group

    Restore to alternate instance

    Select to restore to a local destination different from the instance, then select the alternate location from available servers. When performing restore operations in an SQL Always On environment using test mode, the source availability database is restored to the selected target instance.

    When performing restore operations in an SQL Always On environment using production mode, the restored database is added to the target availability group if the destination instance is a primary replica. If the destination instance is a secondary replica of the target availability group, the database is restored to the secondary replica and left in restoring state.

    If the destination availability group has the automatic seeding option enabled, the secondary database file paths are synchronized with the primary database. If the primary database log is not truncated, the secondary database may be added to the availability group by SQL.

    New Database Name

    Click the New Database Name field to enter an optional alternate name for the database.

    Alternate vSnap

    When restoring from a restore point that was offloaded to a cloud resource or repository server, a vSnap server is used as a gateway to complete the operation. By default, the vSnap server used to complete the restore is the same vSnap server used to complete the backup and offload operations. To reduce load, an alternate vSnap server can be selected to serve as the gateway to complete the restore. To select an alternate vSnap server when restoring a specific, non-latest restore point from a cloud resource or repository server, select Use alternate vSnap server for the restore job, then select a server from the Select alternate vSnap menu.

    Restore Type

    Set the SQL Restore job to run in test, production, or Instant Access mode by default. Once the job is created, it can be run in test, production, or Instant Access mode through the Job Sessions pane.

    Recovery Options

    Set the recovery options:

    No Recovery
    Sets the selected database to a RESTORING state. If you are managing transaction log backups without using IBM Spectrum Protect Plus, you can manually restore log files, and add the database to an availability group, assuming that the lsn of the secondary and primary database copies meets the criteria. The No Recovery option does not support production mode restores to SQL Always On groups.
    Recover until end of backup
    Restore the selected database to the state at the time the backup was created.
    Recover until specific point in time
    When log backup is enabled through an SQL backup job definition, point-in-time restore options will be available when creating an SQL restore job definition. Select one of the following options, and then click Save:
    • By Time. Select this option to configure a point-in-time recovery by a specific date and time.
    • By Transaction ID. Select this option to configure a point-in-time recovery by transaction ID.

    In a standalone restore operation, IBM Spectrum Protect Plus finds the restore points that directly proceed and follow the selected point-in-time. During the recovery, the older data backup volume and the newer log backup volume are mounted. A temporary restore point is created if the point-in-time is after the last backup operation.

    When performing restore operations in an SQL Always On environment using test mode, the restored database will join the instance where the availability group resides.

    When performing restore operations in an SQL Always On environment using production mode, the restored Primary database will be joined to the availability group. If the destination availability group has the automatic seeding option enabled, the secondary database file paths are synchronized with the primary database. If the primary database log is not truncated, the secondary database may be added to the availability group by SQL.

    Application Options

    Set the application options:

    Overwrite existing database
    Enable to allow the restore job to overwrite the selected database. By default, this option is disabled.

    Note: Before performing restore operations in a SQL Always On environment using production mode with the Overwrite existing database option, ensure that the database is not present on the replicas of the target availability group. To do so, you must manually clean up the original databases (to be overwritten) from all replicas of the target availability group.

    Maximum Parallel Streams per Database
    Set the maximum data stream from the backup storage per database. This setting applies to each database in the job definition. Note that multiple databases can still be restored in parallel if the value of the option is set to 1. Multiple parallel streams might improve restore speed, but high bandwidth consumption might affect overall system performance.

    This option is applicable only when restoring an SQL Server database to its original location using its original database name.

    Advanced Options

    Set the advanced job definition options:

    Run cleanup immediately on job failure
    Enable to automatically clean up allocated resources as part of a restore if the recovery fails.
    Allow session overwrite
    Select this option to replace an existing database with the same name during recovery. When an Instant Disk Restore is performed for a database and another database with the same name is already running on the destination host/cluster, IBM Spectrum Protect Plus shuts down the existing database before starting up the recovered database. If this option is not selected, the restore job fails when IBM Spectrum Protect Plus encounters an existing running database with the same name.
    Continue with restores of other databases even if one fails
    Toggle the recovery of a resource in a series if the previous resource recovery fails. If disabled, the Restore job stops if the recovery of a resource fails.
    Mount Point Prefix
    For instant access restore operations, specify the prefix for the path where the mount point is to be directed.
    Script Settings

    Pre-scripts and post-scripts are scripts that can be run before or after a job runs at the job level. Batch and PowerShell scripts are supported.

    In the Pre-script or Post-script section, select an uploaded script and an application or script server where the script will run. To select an application server where the script will run, clear the Use Script Server check box. Scripts and script servers are configured through the System Configuration > Script page.

    To continue running the job if the script associated with the job fails, select Continue job/task on script error.

    When this option is enabled, if a pre-script or post-script completes processing with a non-zero return code, the backup or restore operation is attempted and the pre-script task status is reported as COMPLETED. If a post-script completes with a non-zero return code, the post-script task status is reported as COMPLETED.

    When this option is disabled, the backup or restore is not attempted, and the pre-script or post-script task status is reported as FAILED.

  7. Click Save.
  8. To run the job now, click Restore. To schedule the job click Manage Jobs and define a trigger for the job definition.