Example backup scenarios

Edit online

The following examples describe some typical backup scenarios in a cluster environment.

Scenario: You have a cluster configuration consisting of two SQL Server nodes. The cluster is configured in an AlwaysOn Availability group, where one node is the primary replica and the other, the secondary replica. You want to do a full backup of the secondary replica and also want to back up the transaction log using MMC.

To do a full backup with transaction log, you can run a legacy backup.

  1. To run a legacy backup, ensure that the Data Protection for SQL Server license file is installed.
  2. Start MMC.
  3. In the tree view, open the Protect and Recover view and select the SQL Server replica which you want to back up.
    Note: IBM Storage® Protect Snapshot for SQL Server must be installed on all replicas that are eligible to run a backup.
  4. On the Protect tab of the selected SQL Server node instance, select an option for viewing databases and information about the included databases.
  5. Toggle the Standard Databases / Availability Databases button for the respective database views. Verify the replica role, synchronization state, and space and log usage of your selected SQL Server node.
  6. In the Actions pane, click Backup Method and select Legacy.
  7. Click Full Backup to perform a full database backup on the secondary node and wait for it to finish. Review the status of the backup operation by clicking Task List in the results pane.

Since running your full backup, an update has been made to your primary replica. This change is synchronized to the secondary replica using log shipping. To also back up the change, you can run a log backup. In MMC, select your SQL Server secondary replica instance. Click Backup Method and select Legacy. Select Log Backup to IBM Storage Protect

You have now completed a full and a log backup on a secondary node in an AAG cluster.
Scenario: Your AAG environment is configured to run VSS backups. You want to run a scheduled VSS snapshot backup of your secondary replica.
When you configure your environment to distribute a VSS backup across AAG replicas, you can run only full VSS backups on the primary availability replica. To back up other replicas using VSS, you must choose copy-only full backups.
  1. In the tree, open the Protect and Recover view and select the SQL Server instance which you want to back up.
  2. Verify that your preferred replica is your secondary replica. Right-click selected SQL Server instance. Select Properties and choose AlwaysOn Preferences. Select Prefer secondary replica
  3. Install IBM Storage Protect Snapshot for SQL Server on all replicas that are eligible to run a backup.
  4. Create a command script to run a .CMD file with a separate backup command per database similar to the following sample
    tdpsqlc backup db1 full /alwaysonpriority /backupmethod=VSS 
	backupdest=TSM
tdpsqlc backup db2 full /alwaysonpriority /backupmethod=VSS 
	backupdest=TSM
tdpsqlc backup db3 full /alwaysonpriority /backupmethod=VSS 
	backupdest=TSM
  5. Associate each IBM Storage Protect Snapshot for SQL Server node with the defined schedule.
  6. Run backups on the SQL node according to defined priorities for each database.