Creating VSS backups of SQL Server databases

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

• Before you back up a database, run dbcc checkdb and dbcc checkcatalog to verify the logical and physical consistency of the database.
• 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.

• Do not mix persistent and nonpersistent VSS snapshots.

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.

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 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.

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 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 instance, select an option for viewing databases.

   Table 1. Table 1. Database backup views

   Task	Action
   View a list of SQL 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. 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 only local shadow volumes.

   TSM

   Click this item to store the database backups only on IBM Storage Protect server storage. 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.

   If you set the Backup Destination to TSM, a nonpersistent VSS snapshot is created. To avoid mixing persistent and nonpersistent VSS backups, do not follow a series of backups to a local server with a backup to IBM Storage Protect server. As a best practice, set the Backup Destination to BOTH to send data to IBM Storage Protect server and preserve the local snapshot backup versions.
7. 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.
8. 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.
9. Review the status of the backup operation by clicking Task List in the results pane. Click Task Details to view detailed status information.

During backup processing, IBM Storage Protect Snapshot for Microsoft SQL Server bypasses database snapshots and databases that are in offline, mirroring, and restoring states. To determine which databases backups are bypassed during backup processing, review the tdpsql.log in the directory where IBM Storage Protect Snapshot is installed.