Use a backup job to back up SQL Server environments with snapshots.
Before you begin
During the initial base backup, IBM Spectrum Protect
Plus creates a vSnap LUN volume and creates an NTFS
share on that iSCSI LUN. During incremental backups, the previously created volume is reused. The
IBM Spectrum Protect
Plus agent maps the LUN to the SQL Server
server and mounts the NTFS volume to where the backup is completed. If log backups are enabled,
IBM Spectrum Protect
Plus creates a separate vSnap volume and
creates a CIFS on that volume. Log backup transaction files are copied to this share according to
the schedule created for log backup.
When the backup job is completed, the IBM Spectrum Protect
Plus agent unmounts the share from the SQL Server server and creates a vSnap snapshot of the backup
volume.
Review the following information:
- 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.
- Microsoft iSCSI Initiator must be enabled and running on the Windows server. An iSCSI route must be enabled between the SQL system and vSnap server. For more information, see Microsoft iSCSI Initiator Step-by-Step Guide.
- IBM Spectrum Protect
Plus does not support log backup of
Simple recovery models.
- Failover of an SQL cluster instance during backup is not supported.
- If you plan to back up a large number of databases, you might have to increase the number of
maximum worker threads on each associated SQL Server instance to ensure that backup jobs are
completed successfully. The default value for maximum worker threads is 0. The server automatically
determines the maximum worker threads value based on the number of processors available to the
server. SQL Server uses the threads from this pool for network connections, database checkpoints,
and queries. Additionally, a backup of each database requires one additional thread from this pool.
If you have a large number of databases in a backup job, the default max worker threads might not be
enough to back up all of the databases and the job will fail. For more information about increasing
the maximum worker threads option, see Configure the max worker threads Server Configuration Option.
- SQL databases from test restores are not eligible for backup operations. As a
result, SQL databases that are a product of a test restore cannot be selected for backup nor will
they selectable in an SLA. Finally, if an SLA association is at the instance level, any SQL
databases from test restores will be skipped during backup operations.
- IBM Spectrum Protect
Plus supports database
backups and transaction log backups. The product name is populated in the
msdb.dbo.backupset
for records created by backups initiated from IBM Spectrum Protect
Plus.
- For more information about log backups for SQL, see Log backups.
Take the following actions:
- Register the SQL Servers that you want to back up. For more information, see
Adding an SQL Server application server.
- Configure SLA policies. For more information, see Create backup policies.
- Before you set up and run SQL backup jobs, configure the Shadow Copy storage settings for the
volumes where your SQL databases are located. This setting is configured one time for each volume.
If new databases are added to the job, the setting must be configured for any new volumes that
contain SQL databases. In Windows Explorer, right-click the
source volume and select the Shadow Copies tab. Set the Maximum
size to No limit or a reasonable size based on the source volume
size and I/O activities, and then click OK. The shadow copy storage area must
be on the same volume or another available volume during backup job.
Procedure
To define an SQL backup job, complete the following steps:
-
In the navigation pane, click .
-
Select an SQL Server instance to back up.
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.
-
Click Select an SLA Policy to add one or more SLA policies that meet
your backup data criteria to the job definition.
-
To create the job definition by using default options, click Save.
The job runs as defined by the SLA policies that you selected. To run the job manually, click . Select the job and click .
Tip: When the job for the selected SLA policy runs, all resources that
are associated with that SLA policy are included in the backup operation. To back up only selected
resources, you can run an on-demand job. An on-demand job runs the backup operation immediately.
- To run an on-demand backup job for a single resource, select the resource and click . If the resource is not associated with an SLA policy, the Run
button is not available.
- To run an on-demand backup job for one or more resources, click Create
job, select Ad hoc backup, and follow the instructions in Running an ad hoc backup job.
-
Click Select Options to specify more options before you save the backup
job.
Enable Log Backup
Select this option to enable the backing up of transaction logs. These logs are
used for recovery options such as point-in-time restore operations. If log backups are enabled for
your backup jobs, transactions are continuously logged during the backup time. Notification is sent
if any discontinuity is detected in log file backups.
To enable log backup schedule creation for multiple databases on the same SQL
Server instance, ensure that all databases are added to the same SLA policy. A staging area for the
process of log backing up is not required.
If an on-demand job runs with the Enable Log Backup option enabled, log
backup occurs. However, when the job runs again on a schedule, the option is disabled for that job
run to prevent possible missing segments in the chain of backups.
Select one of the following options:Back up database files
one at a time using parallel streams Select this option to use parallel streams to back
up your databases sequentially.
Back up database files in parallel using parallel
streams Select this option to use parallel streams to backup your databases in
parallel.
Finally, set the Maximum Parallel Streams per Database by selecting the
maximum number of data streams to be used per database during the backing up process. This setting
applies to each database in the job definition. Multiple databases can be backed up in parallel if
the value of the option is set to 1. Specifying Multiple parallel streams can
improve backup speed in some cases.
-
Click Save to save the options for your backup jobs.
The job runs as defined by your SLA policy, or can be run manually from the Job and
Operations window.
-
To configure more options, click the Policy Options field that is
associated with the job in the SLA Policy Status section. Set the additional
policy options:
Pre-scripts and post-scripts
Run a pre-script or a post-script. Pre-scripts and post-scripts are scripts that can be run
before or after a job runs. 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 is due to run. To select an
application server where the script runs, clear the Use Script Server check
box. Scripts and script servers are configured on the 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 finishes 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 with a nonzero return code, the
post-script task status is reported as COMPLETED
.
When this option is not enabled, the backup or restore is not attempted, and the pre-script or
post-script task status is reported as FAILED
.
Exclude ResourcesExclude specific resources from the backup job
through single or multiple exclusion patterns. Resources can be excluded through an exact match or
with wildcard asterisks specified before the pattern (*test) or after the pattern (test*).
Multiple asterisk wildcards are also supported in a single pattern. Patterns support standard
alphanumeric characters in addition to the following special characters: - _ and *.
Separate
multiple filters with a semicolon.
Force Full Backup of
ResourcesForce base backups operations for specific virtual machines or databases in
the backup job definition. Separate multiple resources with a semicolon.
- To save any additional options that you configured, click
Save.
What to do next
After you create the backup job definition, complete the following action: