Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SSRS report for SQL Server Database Backup Status


By:   |   Read Comments (4)   |   Related Tips: More > Backup

Attend these FREE MSSQLTips webcasts >> click to register


Problem

I have a lot of databases to backup and I need a SQL Server Reporting Services (SSRS) report to show the backup status.  What options are available?  What is the most important data?

Solution

SQL Server Reporting Services (SSRS) is a great tool that keeps getting better and better. One way we can use SSRS is to generate a dynamic backup status report that provides us with details on items such as: last backup date and time, backup method, backup location, last log backup, and log backup location. This tip is in a similar theme as Koen Verbeeck's tip on Administrative Intelligence, Custom SSMS report to show SQL Server tables per filegroup, which describes generating a report which list out all the tables in various file groups.

First we need to start with setting up the dataset to get the backup data. Fortunately, several tables exist in the msdb database that give us just about all of the data we need. These tables include:

  • dbo.backupfile
  • dbo.backupfilegroup
  • dbo.backupmediafamily
  • dbo.backup mediaset
  • and dbo.backupset.

We will use the 2014 versions of the AdventureWorks regular and data warehouse databases which are available on Codeplex: https://www.microsoft.com/en-us/download/details.aspx?id=49502. Furthermore, we will use the Contoso sample database which is available at: https://www.microsoft.com/en-us/download/details.aspx?id=18279, in our backup list. Once these sample databases are downloaded and installed, we subsequently initiate several backups to simulate a real backup situation. Finally, to generate our report, we will use the SQL Server Data Tools for Business Intelligence (SSDT-BI) for Visual Studio 2013 to develop our report. You can download SSDT-BI from: http://www.microsoft.com/en-us/download/details.aspx?id=42313.

SSRS Backup Report Setup

In order to create the backup status report, we first must create a query set from some of the above mentioned tables. Some of the caveats and assumptions for the report are as follows:

  • Only the latest full and transaction log back are reviewed even though transaction log backups could span multiple days and / or periods and full database backups.
  • For the report, differential backups are not considered, but I did include them for future reference if needed.
  • All databases except TempDB are evaluated.
  • Different conditions will be cause a red or yellow highlight:
    • Red - no backup in the history for the database
    • Yellow - transaction log backup missing or backup older than x number of days

Of course this report is just one version of many that could be created. Feel free to use it as a basis for additions and modifications which suit your particular organization's needs.

So let us get to the below query. We make use of several Common Table Expressions (CTE) to help with readability and flow of the SQL. The first CTE gets a list of all the databases backups that have been executed, along with some related details. The next CTE returns information about differential backups (note, I did not use differential database information in the report to save space, but you could easily add it). Finally, the last two CTEs in the below code retrieve information about the latest transaction log backup that has been run.

---Get List of Last Full Backup or No Backup
;WITH LAST_FULL_BACKUP_LIST
AS
(
SELECT
SYSDBLIST.name AS Name,
MAX(BUSETS.backup_finish_date) AS Last_Backup_Finish_DateTime
FROM
MASTER.sys.databases AS SYSDBLIST
LEFT OUTER JOIN
msdb.dbo.backupset AS BUSETS
ON
SYSDBLIST.name = BUSETS.database_name
WHERE
SYSDBLIST.name<>'TempDB'
AND
BUSETS.[type] ='D' OR BUSETS.[type] IS NULL
GROUP BY
SYSDBLIST.name
)

,
---Get List of Last Differential Backup
LAST_DIFFERENTIAL_BACKUP_LIST
AS
(
SELECT
SYSDBLIST.name AS Name,
MAX(BUSETS.backup_finish_date) AS Last_Backup_Finish_DateTime
FROM
MASTER.sys.databases AS SYSDBLIST
LEFT OUTER JOIN
msdb.dbo.backupset AS BUSETS
ON
SYSDBLIST.name = BUSETS.database_name
WHERE
SYSDBLIST.name<>'TempDB'
AND
BUSETS.[type] ='I'
GROUP BY
SYSDBLIST.name
)

,
---Get List of Last Log Backup

LOG_BACKUP_LIST
AS
(
SELECT
SYSDBLIST.name AS Name,
BUSETS.backup_finish_date AS Backup_Finish_DateTime,
ROUND(((BUSETS.backup_size/1024)/1024),2) AS Backup_Size_MB,
ROW_NUMBER() OVER(Partition by SYSDBLIST.name ORDER BY BUSETS.backup_finish_date DESC) AS RevOrderBuDate
FROM
MASTER.sys.databases AS SYSDBLIST
LEFT OUTER JOIN
msdb.dbo.backupset AS BUSETS
ON
SYSDBLIST.name = BUSETS.database_name
WHERE
SYSDBLIST.name<>'TempDB'
AND
BUSETS.[type] ='L'
)
,
LAST_LOG_BACKUP_LIST
AS
(SELECT
Name,
Backup_Finish_DateTime AS Last_Backup_Finish_DateTime,
Backup_Size_MB AS Log_Backup_Size_MB
FROM
LOG_BACKUP_LIST
WHERE
RevOrderBuDate=1
)



SELECT SERVERPROPERTY('Servername') AS ServerName,
SYSDBLIST.name AS Database_Name,
SYSDBLIST.Compatibility_level,
SYSDBLIST.Recovery_model,
SYSDBLIST.Recovery_model_desc,
BUSETS.database_creation_date AS Database_Create_Date,
CASE WHEN BUSETS.backup_set_id IS NULL THEN 'NO Backup' ELSE 'Backup Complete' END AS Backup_Completed,
BUSETS.backup_start_date AS Backup_Start_DateTime,
BUSETS.backup_finish_date AS Backup_Finish_DateTime,
DATEDIFF(MINUTE, BUSETS.backup_start_date, BUSETS.backup_finish_date) AS Duration_Min,
(DATEDIFF(DAY,BUSETS.backup_finish_date,GETDATE())) AS Days_Since_Last_Backup,

LASTDIFFBACKUP.Last_Backup_Finish_DateTime AS Last_Differential_Finish_DateTime,
(DATEDIFF(DAY,LASTDIFFBACKUP.Last_Backup_Finish_DateTime ,GETDATE())) AS Days_Since_Last_Differential_Backup,
LASTLOGBACKUP.Last_Backup_Finish_DateTime AS Last_Log_Finish_DateTime,
(DATEDIFF(DAY,LASTLOGBACKUP.Last_Backup_Finish_DateTime,GETDATE()))AS Days_Since_Last_Log_Backup,
LASTLOGBACKUP.Log_Backup_Size_MB AS LOG_Backup_Size_MB,
CASE
WHEN BUSETS.[type] = 'D' THEN 'Full Backup'
WHEN BUSETS.[type] = 'I' THEN 'Differential Database'
WHEN BUSETS.[type] = 'L' THEN 'Log'
WHEN BUSETS.[type] = 'F' THEN 'File/Filegroup'
WHEN BUSETS.[type] = 'G' THEN 'Differential File'
WHEN BUSETS.[type] = 'P' THEN 'Partial'
WHEN BUSETS.[type] = 'Q'THEN 'Differential partial'
END AS Backup_Type,
ROUND(((BUSETS.backup_size/1024)/1024),2) AS Backup_Size_MB,
ROUND(((BUSETS.compressed_backup_size/1024)/1024),2) AS Backup_Size_Compressed_MB,
BUMEDFAM.Device_type,
BUMEDFAM.Physical_device_name,
BUMEDFAM.Logical_device_name
FROM
MASTER.sys.databases AS SYSDBLIST
LEFT OUTER JOIN
msdb.dbo.backupset AS BUSETS
ON
SYSDBLIST.name = BUSETS.database_name
LEFT OUTER JOIN
msdb.dbo.backupmediafamily AS BUMEDFAM
ON
BUSETS.media_set_id = BUMEDFAM.media_set_id

INNER JOIN
LAST_FULL_BACKUP_LIST AS LASTBACKUP
ON
SYSDBLIST.Name=LASTBACKUP.Name
AND
ISNULL(BUSETS.backup_finish_date,'01/01/1900') = ISNULL(LASTBACKUP. Last_Backup_Finish_DateTime,'01/01/1900')

LEFT OUTER JOIN
LAST_DIFFERENTIAL_BACKUP_LIST AS LASTDIFFBACKUP
ON
SYSDBLIST.Name=LASTDIFFBACKUP.Name

LEFT OUTER JOIN
LAST_LOG_BACKUP_LIST AS LASTLOGBACKUP
ON
SYSDBLIST.Name=LASTLOGBACKUP.Name
WHERE
SYSDBLIST.name<>'TempDB'

The query returns one line per database and includes even those databases where no backup has been taken.

backup query

Next we open SSDT-BI to begin creating our Backup Status Report. If you need help creating a SSRS report, I would suggest starting at this tutorial: SQL Server Reporting Services (SSRS) Tutorial.

Backup Report

Let me go over what is present on the above report file. The report header on the left side lists the server name and date while the right side shows the report name. The report footer lists the page numbers and run time. Inside the main part of the report, we list the server name, database name, recovery model, type of backup (full or transaction log), the last back date, the days since the last backup (from the run time), the backup size, the compressed backup size, the compression rate, and finally the time it took, in minutes, for the backup to complete.

We then add conditional formatting to the Last Backup Date Field and Days Since Last Backup. These highlights are noted in the Column headers for easy notification. Of course you can adjust accordingly.

Additionally, we define two parameters. The first parameter is a free form field where you will enter the server name for which you would like the backup status report. This parameter could easily be modified, for instance, to retrieve a list of server names from a table that contains a list of all your servers. For this tip, I was trying to make the report somewhat flexible and easy to run.

ServerName

You may be wondering how we make the Data Source dynamically adjust to the server to connect to. As shown below, we create an embedded data source with a type of Microsoft SQL Server, and then use ="data source=" & Parameters!pServerName.Value in the connection string. Now the server is dynamically set based on what we input in the servername parameter.

Server Data Source

The next step is to add a Days Threshold parameter which controls whether the number of days since the last back textbox is highlighted in yellow or not. I set the default value to 3, but that can easily be changed to an alternate number.

Days Parameter

Days Parameter Default

Finally we are ready to run the report. As shown below, we now have a quick backup status report that can keep us up to date on the status of our backups. Also notice we added a quick tool tip in the date field which displays the physical location of the backup.

status report 1

A copy of the SSRS RDL file that was used in this tip is available to download at the end of this tip.

Conclusion

In this tip we designed a backup status report that queries the msdb database to generate various important and timely information about our database backups. The query primarily uses the msdb.dbo.backupset table to retrieve items, such as backup finish date, backup size, backup compression size, and backup duration. The report itself provides a full and transaction log row for each database and use various conditional formatting to notate specific areas that may need attention. I am hoping this tip will be the first in a series of sample SSRS reports that can assist with your Administrative Intelligence functions.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, September 08, 2017 - 6:19:19 PM - Scott Murray Back To Top

maybe a double hop issue?  Or try the ip address?


Friday, September 08, 2017 - 4:10:08 PM - Marcos Back To Top

 Scott,

Thanks for the quick reply. I have the report deployed in a SQL 2014 SSRS server, when I ran it in SSRS it opens the report and ask for teh parameters, but when hit on run, it fails. Strange thing, when I ran in the Development Studio, it works fine for all the instances.  The deployed report only work for local instances, not for external.

 

Thanks

 


Friday, September 08, 2017 - 1:20:24 PM - Scott Back To Top

If it runs locally then you would need to check the data source on the ssrs box.  


Friday, September 08, 2017 - 12:28:25 PM - Marcos Back To Top

When I deployed the report it raise an error on execution time:

  • An error has occurred during report processing. (rsProcessingAborted)
    • Cannot create a connection to data source 'ServerName'. (rsErrorOpeningConnection)
      • For more information about this error navigate to the report server on the local server machine, or enable remote errors

Any clue?


Learn more about SQL Server tools