Monitor Your Database Servers with DTS, Part 1: Backup SLA Report

How Do the Script and Parts of the Reporting Process Work?

1.  The first part is to run the SQL file, Before_bkp_sla.sql. The SQL deletes any data from a previous run of the backup exception report in the same day to avoid duplicate data. (View the text of the scripts.)

isql -E -S SQLSRVCNTREP -d dbamaint -n -t 60
-i%DBASQL%\Before_bkp_sla.sql > %DBAOUT%\before_bkp_exception.out

2.  The next step is to run the DTS package, "Bkp_exception_report," to fetch the data from all the servers to the tb_bkp_history table in SQLSRVCNTREP.

dtsrun /S SQLSRVCNTREP /N Bkp_exception_report /E >>%DBAOUT%\Bkp_exception_report.log

3.  The next step is to run bkp_exception.sql. The SQL selects the records from the tb_bkp_history table and checks for conditions to see if there are any backups that have not happened as per their predefined schedule (for example, if there is a daily backup that has not happened in the last 24 hours or a weekly backup has not happened in last 7 days). You can also define if there are any backups that happen only on business days. In case of such backups the query will not check for them on Monday. The detail of the backup schedule is kept in tb_bkp_schedule. (View the text of the scripts.)

isql -E -S SQLSRVCNTREP -d dbamaint -n -t 60
-i%DBASQL%\bkp_exception.sql > %DBAOUT%\bkp_exception_report.out

4.  The last step is to send an e-mail to the team with the exception report. You can use any SMTP client to send the e-mail. "Sendmail" and "blat" are among a few. Blat is freeware. If you want to use blat you can download it from http://www.blat.net/.

blat %DBAOUT%\bkp_exception_report.out -t to_email@domain.com -s "SQL Server: Backup Exception Report" -server timeline -f from_email@domain.com