SQL Backup Master Help

SQL Backup Master offers the ability to execute a custom SQL script (e.g. a set of custom SQL statements) before and after the associated backup job is executed.

 

Error and informational messages that occur during custom SQL script execution are written to the SQL Backup Master log. Such errors will not halt the progress of the backup job.

 

Script Variables

 

SQL Backup Master declares an XML variable prior to executing your SQL script, thereby making backup job state information available to your script at run time.

 

Here's an example variable declaration:

 

--- Types: "Full", "Diff" or "Log"

--- Stages: "BeforeBackup" or "AfterBackup"

--- Status: "Finished", "FinishedWithErrors" or "Failed"

DECLARE @BACKUP_CONTEXT XML = '<Backup Name="Company" Type="Full" Stage="BeforeBackup" Status="Failed">

 <Databases>

   <Database Name="Customers" />

   <Database Name="Invoices" />

   <Database Name="Orders" />

 </Databases>

</Backup>;'

 

Custom Script Example

 

Your custom script can  extract information from the above declaration by using SQL Server's built-in XML querying capabilities.

 

Using the example variable declaration above, let's look at an example of how you might extract and use this information (see inline comments for details).

 

--- extract backup type, stage, and status into variables

DECLARE @BackupType nvarchar(10)

DECLARE @BackupStage nvarchar(20)

DECLARE @BackupStatus nvarchar(20)

SET @BackupType = @BACKUP_CONTEXT.value('(/Backup/@Type)[1]', 'nvarchar(10)')

SET @BackupStage = @BACKUP_CONTEXT.value('(/Backup/@Stage)[1]', 'nvarchar(20)')

SET @BackupStatus = @BACKUP_CONTEXT.value('(/Backup/@Status)[1]', 'nvarchar(20)')

 

--- example of conditional test using backup type and stage; note that the backup

--- status will be populated only after the backup completes (otherwise null)

IF @BackupType = 'Full'

BEGIN

 IF @BackupStage = 'BeforeBackup'

         PRINT 'Full backup is starting'

 ELSE

         PRINT 'Full backup is finished, status = ' + @BackupStatus

END

 

--- use a cursor to loop over the backup database names

DECLARE @DatabaseName nvarchar(255)

 

DECLARE crs CURSOR static forward_only read_only FOR

SELECT tab.col.value('@Name[1]', 'nvarchar(255)')

FROM @BACKUP_CONTEXT.nodes('//Database') tab(col)

 

OPEN crs;

FETCH next FROM crs INTO @DatabaseName;

WHILE 0 = @@fetch_status

BEGIN          

    PRINT @DatabaseName; --- do what you want with @DatabaseName

    FETCH next FROM crs INTO @DatabaseName;

END

CLOSE crs;

DEALLOCATE crs;

 

Error Handling

 

If your custom SQL script produces an error, an error will be logged by SQL Backup Master and will be reflected in the final backup job state. Such errors will not halt the overall backup job.

 

You can use output specific error messages in your SQL script, if desired, by using the RAISEERROR statement.

 

If your SQL script outputs messages via the PRINT statement, they will be logged as information messages in the SQL Backup Master log.

 

Tips

 

·Use the "GO" statement to separate logical blocks of T-SQL code.

·The "USE" T-SQL command should be used to switch between databases during script execution, followed by a "GO" statement.

·Use  SQL Server Management Studio to write and debug your scripts prior to using them within SQL Backup Master.