Monitor Backups

The procedure Monitor backups creates an overview of all backups that are found on the Linked Servers. This list is compared to the list that was retrieved the last time this procedure ran. Any changes found are reported. in addition the report shows whether backups are missing or older than 1 day.

Reported fields are:

  • ServerName: The local servername for the Linked Server. The Linked Server name is not logged.
  • DatabaseName: The name of the database on the server crawled.
  • RecoveryModel: The Recovery Model for the database since this dictates the kind of backups that can be made.
  • BackupType: The type of backup found (Full, Tran , Diff, File, Unknown).
  • DateStart: The first time this backup was found.
  • DateStop: The first time this backup was not found anymore.
  • LastBackup: The last time this backup was performed.
  • Location: The name of the backup if the destination is tape or the filepath if the destination is a disk.
  • Comment: The reason this backup is included in the report
    – Newly found backup
    – Backup old
    – No Backup

How to use it

The procedure usp_Report_backups will crawl all Linked Servers and retrieve information on all backups for all databases it finds. Information for these backups is stored in the table tbl_Backups.

Create a scheduled job using the Settings window. This will create a job with all necessary information. If you prefer to do this manually, execute the stored procedure with the following parameters:

DECLARE @RC int
DECLARE @Recipient nvarchar(250)
DECLARE @ExceptionList varchar(250)
DECLARE @MailStats bit
DECLARE @SqlVersion int
DECLARE @Separator nchar(1)

SET @Recipient = ‘yourname@yourdomain.com’
SET @ExceptionList = ‘ServerNotToProcess’
SET @MailStats = 1
SET @SqlVersion = 0
SET @Separator = ‘,’

EXECUTE @RC = [dbo].[usp_Report_Backups] 
   @Recipient
  ,@ExceptionList
  ,@MailStats
  ,@SqlVersion
  ,@Separator
;

  • The recipient is the emailaddress where to send the results to.
  • The ExceptionList is a comma separated list of servers that should not be monitored. 
  • Mailstats = 1 means that you will recieve an email if there is no useful information te report, telling you exactly this. If you set this to 0 then no email is sent unless there is something to report.
  • SqlVersion = 0 means report all (possible) Sql Server Verions. If you set this to 11, then only Sql Server 2012 will be monitored and reported.
  • Seperator is the separator character for the comma separated list. You can choose any single character for this.

We recommend that you schedule this procedure on a daily basis after the Monitor Servers procedure has run. Any servers not found by the Monitor Servers will be skipped, saving time.

Collected Information

The report contains all collected information.

  • ServerName: The local servername for the Linked Server. The Linked Server name is not logged.
  • DatabaseName: The name of the database on the server crawled.
  • RecoveryModel: The Recovery Model for the database since this dictates the kind of backups that can be made.
  • BackupType: The type of backup found (Full, Tran , Diff, File, Unknown).
  • DateStart: The first time this backup was found.
  • DateStop: The first time this backup was not found anymore.
  • LastBackup: The last time this backup was performed.
  • Location: The name of the backup if the destination is tape or the filepath if the destination is a disk.
  • Comment: The reason this backup is included in the report