PowerProtect Data Manager 19.15 Microsoft SQL Server User Guide

Scheduling a T-SQL job

1. Open the SSMS, and then select View > Object Explorer.
2. In the Object Explorer, expand SQL Server Agent, right-click Jobs, and then select New job.
3. In the Job Properties window:
   1. On the General page, specify the following fields:
      • Name: Type a name for the job.
      • Owner: Click the button beside the text box, and then complete the following steps:
        1. In the Select Login dialog box, click Browse.
        2. In the Browse for Objects dialog box, under Matching objects, select NT SERVICE\SQLSERVERAGENT, and then click OK.
        3. In the Select Login dialog box, click OK.
      • Description: Type a description for the job.
   2. On the Steps page, click New.
   3. In the Job Step Properties window, on the General page, specify the following fields:
      • Step name: Type a name for the job step.
      • Type: Select Transact-SQL script (T-SQL).
      • Run as: Select SQL Server Agent Service Account.
      • Process execute exit code of a successful command: Type the process success exit code.
      • Command: Specify the required T-SQL command.

        Perform backups with the Microsoft app agent for Application Direct SSMS plug-in provides information about how to generate the T-SQL command.

        You can run the generated T-SQL command by using the New Query menu option to check whether the operation succeeds. If the command runs successfully, the scheduled backups will be successful.

        If you want to use return codes in the generated T-SQL command, you must modify the command.

        Consider the following example raw T-SQL command:

        USE [master]
        GO
        DECLARE @returnCode int
        EXEC   @returnCode = dbo.emc_run_backup ' -c NMMDB154.nmmdev.com -l full -a "NSR_DFA_SI_DD_HOST=10.31.192.14" -a "NSR_DFA_SI_DD_USER=ddbma" -a "NSR_DFA_SI_DEVICE_PATH=/ddbma-sqlagent2" "MSSQL:Fabrics"'
        PRINT @returnCode
        GO

        Consider the following example T-SQL command with return codes:

        DECLARE @returnCode int
        EXEC   @returnCode = dbo.emc_run_backup ' -c NMMDA224.heroines.local -l full -a "NSR_DFA_SI_DD_HOST=10.31.77.27" -a "NSR_DFA_SI_DD_USER=ost112" -a "NSR_DFA_SI_DEVICE_PATH=/heroines" "MSSQL:CopyOftest_db_1" "MSSQL:db1"'
        IF @returnCode <>0
        BEGIN
        RAISERROR ('Fail!', 16, 1)
        END

        If the return code is error, the job step fails.

        NOTE:The last two parameters, 16 and 1 are necessary to raise an error if a job step fails.
   4. On the Advanced page, specify the following fields:
      • On success action: Select whether to proceed to the next job step or quit the current job step and report success, after the current job step succeeds.
      • Specify the other fields according to your requirements.
      • Click OK.
      • On the Schedules page, click New to schedule a job.
      • In the Job Schedule Properties window, specify appropriate information in the corresponding fields, and then click OK.
      • On the Alerts page, click Add to create an alert that will perform a job when a certain event occurs.
      • In the New Alert window:

        On the General page, specify the following fields.

        • Name: Type a name for the alert.
        • Type: Select the type of the event.
        • Specify the appropriate information in the other fields.
   5. On the Response page, specify the following fields:
      • Notify operators: Select this option to send a message to the operators about the job step status.
      • New Operator: Click this button to add an operator to the Operator list.
   6. On the Options page, configure a method, such as E-mail, Pager, or Net Send, to notify operators about the status of the job step.
   7. On the Notifications page, under Actions to perform when the job completes, select the appropriate notification methods to notify operators about the status of the job step.
   8. On the Targets page, select Target local server.
   9. Click OK.