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.
- Complete a SQL restore with the Instant Access option set for the system
databases.
- 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.
- Rename all of the data files for the system databases.
- 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.
- 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:
-
In the navigation panel, click . Click on
Create job, and then select Restore to open the
Restore wizard.
Tips:
- You can also open the wizard by clicking .
- For a running summary of your selections in the wizard, click Preview
Restore in the navigation panel 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.
-
On the Select source page, take the following actions:
-
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.
-
Click the 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 next to the item.
-
Click Next to continue.
-
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.
-
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 pane.
- Cloud service
- The cloud service to which snapshots were copied. The cloud service is defined in the pane.
- Repository server
- The repository server to which snapshots were copied. The repository server is defined in the pane.
- Cloud service archive
- The cloud archive service to which snapshots were copied. The cloud service is defined in the pane.
- Repository server archive
- The repository server to which snapshots were copied to tape. The repository server is defined
in the 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.
|
- 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.
-
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.
- 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 job if the job
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.
- 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
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
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.
- 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.
- 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 .
A recurring job will begin at the scheduled start time when you start the schedule in the
page.
All running jobs are viewable in the page.