PowerProtect Data Manager 19.9 Microsoft Application Agent SQL Server User Guide

PDF

Performing federated backups of SQL Always On availability group databases by using T-SQL scripts

To perform federated backups, use the same backup command and options that Perform backups with T-SQL scripts describes, but with the following modifications:

Specify the Windows cluster name for <client_name> in -c <client_name>.
Specify the backup object name for <save_set_name> in -N <save_set_name>.
Specify #<AlwaysOn_Availability_Group_name> in the backup object name.

The options in the following sample scripts indicate these modifications.

NOTE The Microsoft application agent does not support backing up multiple Always On availability groups in the same operation.

Sample T-SQL script to back up an entire Always On availability group

USE [master]
GO
DECLARE @returnCode int
EXEC @returnCode = dbo.emc_run_backup '-c SQL2012clus3.brsvlab.local
-S 4 -l full -y +30d -a "NSR_DFA_SI_DD_HOST=ddve-01"
-a "NSR_DFA_SI_DD_USER=sqlboost" -a "NSR_DFA_SI_PATH=/sqlboost"
-a "NSR_DFA_SI_DD_LOCKBOX_PATH=C:\ProgramFiles\DPSAPPS\common\lockbox"
-a "NSR_SKIP_NON_BACKUPABLE_STATE_DB=TRUE"
-N "MSSQL$SQ12INST4#sql2012-aag3:" "MSSQL$SQ12INST4#sql2012-aag3:"'
IF @returnCode <> 0
BEGIN
RAISERROR ('Fail!', 16, 1)
END

Sample T-SQL script to back up multiple databases (a subset of databases) of an Always On availability group

USE [master]
GO
DECLARE @returnCode int
EXEC @returnCode = dbo.emc_run_backup '-c SQL2012clus3.brsvlab.local -
S 4 -l full -y +30d -a "NSR_DFA_SI_DD_HOST=ddve-01"
-a "NSR_DFA_SI_DD_USER=sqlboost" -a "NSR_DFA_SI_PATH=/sqlboost"
-a "NSR_DFA_SI_DD_LOCKBOX_PATH=C:\ProgramFiles\DPSAPPS\common\lockbox"
-a "NSR_SKIP_NON_BACKUPABLE_STATE_DB=TRUE"
-N "MSSQL$SQ12INST4#sql2012-aag3:DB" "MSSQL$SQ12INST4#sql2012-aag3:database1"
"MSSQL$SQ12INST4#sql2012-aag3:database3" "MSSQL$SQ12INST4#sql2012-aag3:database8"'
IF @returnCode <> 0
BEGIN
RAISERROR ('Fail!', 16, 1)
END

Sample T-SQL script to back up a specific database of an Always On availability group

USE [master]
GO
DECLARE @returnCode int
EXEC @returnCode = dbo.emc_run_backup '-c SQL2012clus3.brsvlab.local -
S 4 -l full -y +30d -a "NSR_DFA_SI_DD_HOST=ddve-01"
-a "NSR_DFA_SI_DD_USER=sqlboost" -a "NSR_DFA_SI_PATH=/sqlboost"
-a "NSR_DFA_SI_DD_LOCKBOX_PATH=C:\ProgramFiles\DPSAPPS\common\lockbox"
-a "NSR_SKIP_NON_BACKUPABLE_STATE_DB=TRUE"
-N "MSSQL$SQ12INST4#sql2012-aag3:database1" "MSSQL$SQ12INST4#sql2012-aag3:database1"'
IF @returnCode <> 0
BEGIN
RAISERROR ('Fail!', 16, 1)
END