Creating VSS backups of SQL Server databases

Edit online

You can back up standard SQL Server databases or availability databases by using Microsoft™ Volume Shadow Copy Service (VSS).

Before you begin

To manage local VSS backups or to run offloaded backups to IBM Storage® Protect server storage, ensure that IBM Storage Protect Snapshot is configured in your environment.

If you use VSS to back up data to an IBM Storage Protect server, IBM Storage Protect Snapshot is not required.

About this task

On SQL Server 2012 and later versions, you can back up availability databases in an AlwaysOn Availability Group (AAG) regardless of which availability replica is used for the backup operation.
Restriction: When you complete a full backup of a secondary replica in an AAG, only a copyfull backup of that database is created.

To back up availability databases, ensure that IBM Storage Protect Snapshot is configured to use an AlwaysOn node. Additionally, specify the AlwaysOn node in the AlwaysOn Node field in the TSM Node Names page of the IBM Storage Protect Configuration Wizard. If you change the AlwaysOn node name field in the AlwaysOn Node properties page for your SQL Server workload, you must run the IBM Storage Protect Configuration Wizard to complete the reconfiguration of the name.

If you do not want to use the IBM Storage Protect Configuration Wizard to register the node on the IBM Storage Protect server, you can use the IBM Storage Protect register node command.

Procedure

  1. Start Microsoft Management Console (MMC).
  2. If you plan to use offloaded backups, and your environment is configured for use with an IBM Storage Protect server, specify a value in the Remote DSMAGENT Node name field.
    1. Select the SQL Server instance in the navigation tree, and click Properties in the Actions pane.
    2. Select the VSS Backup property page. If the Remote DSMAGENT Node name is blank, enter a node name.
    An offloaded backup uses another system (specified with the Remote DSMAGENT Node name parameter) to move SQL Server data to IBM Storage Protect server storage. Offloaded backups can reduce the load on network, I/O, and processor resources during backup processing.
  3. On the Protect tab of an SQL Server instance, select an option for viewing databases.
    Table 1. Database backup views
    Task Action
    View a list of SQL Server databases that are available for a backup operation Click View: Databases.
    View a list of SQL Server 2012 and later version availability databases that are available for a backup operation Click Standard Databases. Information about the availability databases in an availability group is displayed, including the replica role, synchronization state, and space and log usage.

    Toggle the Standard Databases / Availability Databases button for the respective database views.

    Refine the list of available databases in the results pane by entering a keyword in the Search field. Then, select the databases to back up.

  4. Verify the backup options. If the backup options are not displayed, click Show Backup Options.
    If you want to use offloaded backups, select Yes in the Offload field.
  5. In the Actions pane, click Backup Method and select VSS.
  6. On the Actions pane, select TSM for the Backup Destination.
    The only option that is available to you is TSM because the database backups are stored on IBM Storage Protect server storage.
  7. In the Actions pane, click Backup Destination and select a location to store the backup:
    Local
    Click this item to store the database backups to local shadow volumes only.
    TSM
    Click this item to store the database backups on IBM Storage Protect server storage only. Do not select this option if you are using IBM Storage Protect Snapshot in a stand-alone configuration.
    Both
    Click this item to store the database backups to IBM Storage Protect server storage and local shadow volumes. Do not select this option if you do not have an IBM Storage Protect Snapshot license or if you are using IBM Storage Protect Snapshot in a stand-alone configuration.
  8. Optional: Choose a mode for the current task:
    • Run Interactively: Click this item to run the current task interactively. This selection is the default.
    • Run Scheduled: Click this item to convert the current action into a scheduled task. When you select this item, the schedule wizard runs the command that is required to complete the task.
  9. To start the backup operation, in the Actions pane, take one of the following actions:
    1. Click Full Backup.
      Alternatively, right-click a database and select the backup action that you require from the menu.
    2. Click Copy-Only Full Backup.
      A copy-only full backup is independent of the sequence of SQL Server backups, and is not used as a base for a differential backup. A differential backup is not associated with the copy-full backup, but is associated with the previous full backup that completed. You might use a copy-only full backup as a special purpose backup that does not affect backup and restore operations, and retain such a backup for longer than conventional backups.
  10. Review the status of the backup operation by clicking Task List in the results pane. Click Task Details to view detailed status information.

Results

During backup processing, Data Protection for SQL Server bypasses database snapshots and databases that are in offline, mirroring, and restoring states.

What to do next

To determine which databases backups are bypassed during backup processing, review the tdpsql.log in the directory where Data Protection for SQL Server is installed.