Restoring SQL Server data

Use a restore job to restore Microsoft SQL Server environment from snapshots. After you run IBM Spectrum® Protect Plus Instant Disk Restore jobs, your SQL Server clones can be used immediately. IBM Spectrum Protect Plus catalogs and tracks all cloned instances.

Before you begin

Complete the following prerequisites:
  • Create and run an SQL backup job. For instructions, see Backing up SQL Server data.
  • Before an IBM Spectrum Protect Plus user can restore data, the appropriate roles and resource groups must be assigned to the user. Grant users access to resources and backup and restore operations by using the Accounts pane. For instructions, see Managing user access.
  • If you are planning to run 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.
Review the following restrictions and considerations:
  • If you are planning to run a production restore operation to an SQL Server failover cluster, the root volume of the alternative 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.
  • You cannot restore data to an NTFS or FAT compressed volume because of SQL Server database restrictions. For more information, see Description of support for SQL Server databases on compressed volumes.
  • If you are planning to restore data to an alternative 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 you are restoring data 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 operation, the secondary database is seeded by the SQL server in environments where automatic seeding is supported (Microsoft SQL Server 2016 and later). The database is then enabled on the destination availability group. The synchronization time depends on the amount of data that is being transferred and the connection between the primary and secondary replicas.

    If automatic seeding is not supported or is not enabled, a secondary restore from the restore point with the shortest Log Sequence Number (LSN) gap of the primary instance must be completed. Log backups with the latest point-in-time restore point that is created by IBM Spectrum Protect Plus must be restored if the log backup was enabled on the primary instance. The secondary database restore operation is completed in the RESTORING state and you must issue the T-SQL command to add the database to the target group. For more information, see https://docs.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-2017.

  • When restoring from a IBM Spectrum Protect archive, files will be migrated to a staging pool from the tape prior to the job beginning. Depending on the size of the restore, this process could take several hours.
  • If you are restoring SQL Server system databases such as the master, MSDB, or model, you must restore using the Instant Access method.
    1. Complete a SQL restore with the Instant Access option set for the system databases.
    2. Next, stop the SQL Server instance. For more information about starting and stopping SQL Server services, see https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/start-stop-pause-resume-restart-sql-server-services?view=sql-server-ver15.
    3. Rename all of the data files for the system databases.
    4. Copy all data files from the Instant Access location to the location of the system databases. This is typically located at the path C:\ProgramData\SPP\mnt\id. Verify that all data files are copied.
    5. Start the SQL Server instance. Verify that the master database is successfully restored.

About this task

Instant Disk Restore uses the iSCSI protocol to immediately mount LUNs without transferring data. Databases for which snapshots were taken 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 by 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 by 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 creates the new database by using the restored files.

Procedure

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

  1. In the navigation pane, click Manage Protection > Databases > SQL. Click on Create job, and then select Restore to open the Restore wizard.
    Tips:
    • You can also open the wizard by clicking Jobs and Operations > Create job > Restore > SQL.
    • For a running summary of your selections in the wizard, click Preview Restore in the navigation pane in the wizard.
    • The wizard is opened in the default setup mode. To run the wizard in advanced setup mode, select Advanced Setup. With advanced setup mode, you can set more options for your restore job.
  2. On the Select source page, take the following actions:
    1. Click a source in the list to show the databases that are available for restore operations. You can toggle the displayed sources to show either SQL Server instances in a stand-alone or cluster environment or Always On availability groups by using the View filter.

      You can also use the search function to search for databases in the instances or availability groups.

    2. Click the plus icon Plus icon next to the database that you want to use as the source of the restore operation. You can select more than one database from the list.

      The selected sources are added to the restore list next to the database list. To remove an item from the list source, click the minus icon Minus icon next to the item.

    3. Click Next to continue.
  3. On the Source snapshot page, select the type of restore job that you want to create:
    On-demand: Snapshot
    Runs a one-time restore operation. The restore job starts immediately upon the completion of the wizard.
    On-demand: Point in Time
    Runs a one-time restore job from a point-in-time backup of a database. The restore job starts immediately upon the completion of the wizard.
    Recurring
    Creates a repeating point-in-time restore job that runs on a schedule.
  4. Complete the fields on the Source snapshot page and click Next to continue.
    The fields that are shown depend on the number of items that were selected on the Select source page and on the restore type. Some fields are also not shown until you select a related field.

    Fields that are shown for an on-demand snapshot, single resource restore

    Option Description
    Date range Specify a range of dates to show the available snapshots within that range.
    Backup storage type All backups in the selected date range are listed in rows that show the time that the backup operation occurred and the service level agreement (SLA) policy for the backup. Select the row that contains the backup time and SLA policy that you want, and then take one of the following actions:
    • Click the backup storage type that you want to restore from. The storage types that are shown depend on the types that are available in your environment and are shown in the following order:
      Backup
      Restores data that is backed up to a vSnap server.
      Replication
      Restores data that is replicated to a vSnap server.
      Object Storage
      Restores data that is copied to a cloud service or to a repository server.
      Archive
      Restores data that is copied to a cloud service archive or to a repository server archive (tape).
    • Click anywhere on the row. The first backup type that is shown sequentially from the left of the row is selected by default. For example, if the storage types Backup, Replication, and Archive are shown, Backup is selected by default.
    Use alternate vSnap server for the restore job If you are restoring data from a cloud service or a repository server, select this box to specify an alternative vSnap server, and then select a server from the Select alternate vSnap menu.

    When you restore data from a restore point that was copied to a cloud resource or repository server, a vSnap server is used as a gateway to complete the operation. By default, the vSnap server that is used to complete the restore operation is the same vSnap server that is used to complete the backup and copy operations. To reduce the load on the vSnap server, you can select an alternative vSnap server to serve as the gateway.

    Fields that are shown for an on-demand snapshot, multiple resources restore; point-in-time restore; or recurring restore

    Option Description
    Restore Location Type Select a type of location from which to restore data:
    Site
    The site to which snapshots were backed up. The site is defined in the System Configuration > Site pane.
    Cloud service
    The cloud service to which snapshots were copied. The cloud service is defined in the System Configuration > Backup Storage > Object Storage pane.
    Repository server
    The repository server to which snapshots were copied. The repository server is defined in the System Configuration > Backup Storage > Repository Server pane.
    Cloud service archive
    The cloud archive service to which snapshots were copied. The cloud service is defined in the System Configuration > Backup Storage > Object Storage pane.
    Repository server archive
    The repository server to which snapshots were copied to tape. The repository server is defined in the System Configuration > Backup Storage > Repository Server pane.
    Select a location If you are restoring data from a site, select one of the following restore locations:
    Primary
    The primary site from which to restore snapshots.
    Secondary
    The secondary site from which to restore snapshots.

    If you are restoring data from a cloud or repository server, select a server from the Select a location menu.

    Date selector For on-demand restore operations, specify a range of dates to show the available snapshots within that range.
    Restore Point For on-demand restore operations, select a snapshot from the list of available snapshots in the selected date range.
    Use alternate vSnap server for the restore job If you are restoring data from a cloud service or a repository server, select this box to specify an alternative vSnap server, and then select a server from the Select alternate vSnap menu.

    When you restore data from a restore point that was copied to a cloud service or repository server, a vSnap server is used as a gateway to complete the operation. By default, the vSnap server that is used to complete the restore operation is the same vSnap server that is used to complete the backup and copy operations. To reduce the load on the vSnap server, you can select an alternative vSnap server to serve as the gateway.

  5. On the Restore method page, set the restore job to run in test, production, or instant access mode by default.

    For test or production mode, you can optionally enter a new name for the restored database.

    For production mode, you can also specify a new folder for the restored database by expanding the database and entering a new folder name.

    Optionally, for Production and Test restores, in the New Database Name field, enter the new name for the restored database. The New Database Name field is also displayed when you choose Production restore, but this is for restoring to a new database location on the original instance. When renaming a SQL database, the naming rules for identifiers apply. For more information, see https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers. When restoring with a new name, the Global Preferences option Rename SQL data and log files when database is restore in production mode with new name must be enabled. For more information, see Configuring global preferences.

    Click Next to continue.

    After the job is created, you can run it in test, production, or instant access mode in the Job Sessions pane.

  6. On the Set destination page, specify where you want to restore the database and click Next.
    Restore to original instance
    Select this option to restore the database to the original instance.
    Restore to primary instance
    For restore operations in an SQL Always On environment, select this option to restore the database to the primary instance of the Always On Availability Group. The database is added back to the group.
    Restore to alternate instance
    Select this option to restore the database to a local destination that is different from the original instance, and then select the alternative location from the list of available servers.

    For restore operations in an SQL Always On environment in test mode, the source availability database is restored to the selected target instance.

    For restore operations in an SQL Always On environment in 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 automatic seeding option is enabled for the destination availability group, the secondary database file paths are synchronized with the primary database. If the primary database log is not truncated, the secondary database can be added to the availability group by SQL.

  7. On the Job options page, configure additional options for the restore job and click Next to continue.
    Recovery Options
    Set the following point-in-time recovery options:
    No Recovery
    Set 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.
    Restriction: The No Recovery option does not support production mode restore operations to SQL Always On groups.
    Recover until end of backup
    Restore the selected database to the state at the time that the backup was created.
    Recover until specific point in time
    When log backup is enabled by using an SQL backup job definition, point-in-time restore options will be available when you create an SQL restore job definition. Select one of the following options:
    • By Time. Select this option to configure a point-in-time recovery from a specific date and time.
    • By Transaction ID. Select this option to configure a point-in-time recovery by transaction ID.
    Standby mode
    When the Standby mode option is selected, this leaves the SQL database in a read-only state. Uncommitted transactions are undone and saved into an undo file which may subsequently be used for bringing the database online. Transactions stored in the standby file can be applied when the database is ready to be recovered.
    Note: The location of a database restored using Standby mode may be reported to be in the original database location when viewing the database in SQL Management Studio. The location will actually be the directory that is specified by the user for a Production mode restore and the C:\ProgramData\mnt\uuid_subdirectory for a Test mode restore.

    In a stand-alone 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. If the point in time is after the last backup operation, a temporary restore point is created.

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

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

    Application Options
    Set the application options:
    Overwrite existing database
    Enable the restore job to overwrite the selected database. By default, this option is not enabled.
    Tip: Before you run restore operations in an SQL Always On environment by using the 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 number of parallel data streams from the backup storage per database. This setting applies to each database in the job definition. If the value of the option is set to 1, multiple databases can still be restored in parallel. Multiple parallel streams might improve restore speed, but high bandwidth consumption might affect overall system performance.

    This option is applicable only when you restore 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
    This option enables the automatic cleanup of backup data as part of a restore operation if recovery fails. This option is selected by default. Do not clear this option unless instructed by IBM® Software Support for troubleshooting purposes.
    Allow session overwrite
    Select this option to replace an existing database with a database of 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 or 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 detects a 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 this option is not enabled, the restore job stops if the recovery of a resource fails.
    Protocol Priority (Instant Access only)
    If more than one storage protocol is available, select the protocol to take priority in the job. The available protocols are iSCSI and Fibre Channel.
    Mount Point Prefix
    For instant access restore operations, specify the prefix for the path where the mount point is to be directed.
  8. Optional: On the Apply scripts page, specify scripts that can be run before or after an operation runs at the job level. Batch and PowerShell scripts are supported.
    Pre-Script
    Select this check box to choose an uploaded script and an application or script server where the pre-script will run. To select an application server where the pre-script will run, clear the Use Script Server check box. Scripts and script servers are configured on the System Configuration > Script page.
    Post-Script
    Select this option to choose an uploaded script and an application or script server where the post-script will run. To select an application server where the post-script will run, clear the Use Script Server check box. Scripts and script servers are configured on the System Configuration > Script page.
    Continue job/task on script error
    Select this check box to continue running the job if the script that is associated with the job fails.

    When you select this check box, if a pre-script or post-script completes processing with a nonzero return code, the backup or restore operation is attempted and the pre-script task status is reported as COMPLETED. If a post-script completes processing with a nonzero return code, the post-script task status is reported as COMPLETED.

    If you clear this check box, the backup or restore operation is not attempted, and the pre-script or post-script task status is reported as FAILED.

  9. Take one of the following actions on the Schedule page:
    • If you are running an on-demand job, click Next.
    • If you are setting up a recurring job, enter a name for the job schedule, and specify how often and when to start the restore job. Click Next.
  10. On the Review page, review your restore job settings and click Submit to create the job.

Results

An on-demand job begins after you click Submit, and the onDemandRestore record is added to the Job Sessions pane shortly. To view progress of the restore operation, expand the job. You can also download the log file by clicking the download icon Download icon.

A recurring job will begin at the scheduled start time when you start the schedule in the Jobs and Operations > Schedule page.

All running jobs are viewable in the Jobs and Operations > Running Jobs page.