By: Scott Murray | Updated: 2016-02-10 | Comments (6) | Related: > Backup
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.
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.
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.
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.
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.
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.
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
- Check out these resources:
- Download the Backup Status Report RDL file
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2016-02-10
