An Exceptions-Based Report for SQL Backups – Part 2   Leave a comment

As promised, here is part 2 of of the blog “An Exceptions-Based Report for SQL Backups” (click HERE for the first blog).

Using the attached “Backups Exception Report.rdl” file as an example (click HERE to download), you can create a nifty report based on the stored procedure in the first post. Click HERE to see an example in .pdf format of what the report looks like when rendered. The report .RDL file is in SSRS 2005 format, so you can easily use it with SSRS 2005 and above.

One of the most useful – well fantastic, really! – features of this report and the accompanying stored procedure is that records are shown only for SQL backups that are missing. Potential exceptions are denoted in red and bolded. If there are no missing backups for the SQL instance, a message is displayed stating, “No exceptions were noted for this server.” In this way you have a quick and reliable way to identify when your backups are being missed, without having to wade through the vast majority of backups that are not missing.

You will also notice that the report displays records for databases that are in a status other than “ONLINE” (e.g. “OFFLINE” status), with the last backups of each type displayed (assuming one of the last backups ages over the number of prescribed days). This will be a handy visual reminder of any databases in an unusual status.

To use the example .RDL file, load it into an SSRS project in BIDS and open it. When it first opens, there may be an error displayed. Click to ignore it as it is just trying to connect the data source to a dummy SQL server name which I have embedded in the report. Also make sure you deploy the stored procedure from the first post to the master database of the instance you will be running this against.

Once the report is open in BIDS, go to the data sources tab and click to edit the data set. Within that dialog box, click to edit the data source and change the name of it, and the connection properties, to reflect the name of your SQL instance, then click OK. Then make sure the query type is set to Stored Procedure and in the query text field, paste the name of the procedure, rpt_SQL_Backups_Report. Click on the parameters tab and make sure there is a parameter named “@type” (don’t include the double quotes), and in the value column for that, make sure it says:


…(include the double quotes)

When you click OK, another error may be generated stating it cannot connect to a #TEMP table. This is expected. Click OK, then click the “Refresh Fields” button on the toolbar, and when it prompts you to enter a value for “@type,” enter the string “all_exceptions” (DON’T include the double quotes).

If you made a change to the name of the data set, you will need to point the table on the layout tab to use the new data set name. That is all! You are ready to start using this report.

Another enhancement, which I have not demonstrated here, is to include data sets for multiple SQL instances on the same report, and wire them up to separate tables on the layout tab (copying and pasting from the first table to easily create clones).

Happy reporting!

Posted March 25, 2011 by Norm Enger in Microsoft SQL Server

Posted March 25, 2011 by Norm Enger in Microsoft SQL Server

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: