Backing up SQL Server data

Edit online

Use a backup job to back up SQL Server environments with snapshots.

Before you begin

During the initial base backup, IBM Storage® 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 Storage 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 Storage 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 Storage 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 Storage 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 Storage 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.
  • IBM Storage 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 Storage Protect Plus.
  • SQL databases protected by transparent data encryption (TDE) require that a master key and certificate to be created prior to encryption. The master key and certificate are managed by the user outside of IBM Storage Protect Plus and are not protected as part of a backup job. Prior to restoring a TDE protected database, the master key and certificate must be restored to the restore destination so that the data protected by IBM Storage Protect Plus can be unencrypted. The password used during the initial encryption process must be used to unencrypt the data. For more information about moving SQL databases protected with TDE, see Move a TDE Protected Database to Another SQL Server.
  • For more information about log backups for SQL, see Log backups.
  • When the SQL Server backup job fails and the server reports an error message, you can resolve the issue. For instructions, see Resolving the failed SQL Server database backup.
Note: Due to limitations with the Volume Shadow Copy Services (VSS) framework, leading spaces, trailing spaces, and unprintable characters should not be used in database names. For more information, see Backing up a SQL Server database using a VSS backup application may fail for some databases

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:
  1. In the navigation panel, click Manage Protection > Databases > SQL.
  2. 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.
    Tip:
    • When a resource is added to an SQL backup instance, the backup job details (name, version, SLA policy, and log backup policy) are displayed on the inventory screen.
    • The Log Backup Policy column in the inventory screen indicates which SLA policy has log backup enabled.
  3. Click Select an SLA Policy to add one or more SLA policies that meet your backup data criteria to the job definition.
  4. 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 Jobs and Operations > Schedule. Select the job and click Actions > Start.
    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 Run. If the resource is not associated with an SLA policy, the Run button is not available.
    • To run an Ad hoc backup job for single resource with log backup sub policy enabled, on the Run wizard screen, choose the backup type (Backup or Log Backup) to start the backup job.
    • To run an Ad hoc backup job for all resources that are associated with that SLA policy has log backup sub policy enabled, on the Run wizard screen, select SLA policy from the drop-down list and choose the backup type (Backup or Log Backup) to start the backup job.
    • 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.
  5. Click Select Options to specify more options before you save the backup job.
    Tips for configuring options:
    Review the following tips to help you configure options for the backup job:
    • To set the options for child resources to the same values as the parent, click Set all options to inherit.
    • If multiple resources were selected for the backup job, the options are indeterminate. If you change the value for an option, that value is used for all selected resources after you click Save.
    • Options that are shown in yellow indicate that the option value has changed from the previously saved value.
    • To close the Options pane without saving changes, click Select Options.

    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.

    Restriction: It is possible that the same databases that are on a VM might be backed up as part of a VM backup job and a SQL Server backup job. If you want to enable log backup for a SQL Server backup job, ensure that Truncate SQL logs option is not selected for a VM backup job that backs up the same databases. The log truncation deletes all inactive logs from the log file. The deleted log sequence causes discontinuity in the log backup.

    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.

    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.

  6. 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.
  7. To configure additional options, click the Policy Options clipboard icon clipboard icon that is associated with the job in the SLA Policy Status section. Set the following 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 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 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 Resources

    Exclude 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 Resources

    Force base backups operations for specific virtual machines or databases in the backup job definition. Separate multiple resources with a semicolon.

  8. To save any additional options that you configured, click Save.
  9. After the backup job is completed, the IBM Storage Protect Plus agent records the database backup status in Windows Event log . To navigate to the backup logs, click Windows Event log > Applications and Services Logs > IBM Event log.

What to do next

After you create the backup job definition, complete the following action:
Action How to
Create an SQL Restore job definition. See Restoring SQL Server data.