Fail Back Always on Databases From Dr Site to Prod Site

Script to retrieve SQL Server database backup history and no backups

By:   |   Updated: 2021-10-04   |   Comments (37)   |   Related: 1 | 2 | 3 | More > Backup

Problem

There is a multitude of data to be mined from within the Microsoft SQL Server system views. This data is used to present information back to the end user of the SQL Server Management Studio (SSMS) and all third party management tools that are available for SQL Server Professionals. Be it database backup information, file statistics, indexing information, or one of the thousands of other metrics that the instance maintains, this data is readily available for direct querying and assimilation into your "home-grown" monitoring solutions as well.

This tip focuses on that first metric: database backup information. Where it resides, how it is structured, and what data is available to be mined.

Solution

The msdb system database is the primary repository for storage of SQL Agent, backup, Service Broker, Database Mail, Log Shipping, restore, and maintenance plan metadata. We will be focusing on the handful of system views associated with database backups for this tip:

• dbo.backupset: provides information concerning the most-granular details of the backup process
• dbo.backupmediafamily: provides metadata for the physical backup files as they relate to backup sets
• dbo.backupfile: this system view provides the most-granular information for the physical backup files

Based upon these tables, we can create a variety of queries to collect a detailed insight into the status of backups for the databases in any given SQL Server instance.

Database Backups for all databases For Previous Week

---------------------------------------------------------------------------------  --Database Backups for all databases For Previous Week  ---------------------------------------------------------------------------------  SELECT     CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,     msdb.dbo.backupset.database_name,     msdb.dbo.backupset.backup_start_date,     msdb.dbo.backupset.backup_finish_date,     msdb.dbo.backupset.expiration_date,     CASE msdb..backupset.type        WHEN 'D' THEN 'Database'        WHEN 'L' THEN 'Log'        END AS backup_type,     msdb.dbo.backupset.backup_size,     msdb.dbo.backupmediafamily.logical_device_name,     msdb.dbo.backupmediafamily.physical_device_name,     msdb.dbo.backupset.name AS backupset_name,     msdb.dbo.backupset.description  FROM     msdb.dbo.backupmediafamily     INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  WHERE     (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)  ORDER BY     msdb.dbo.backupset.database_name,     msdb.dbo.backupset.backup_finish_date            

Note: for readability the output was split into two screenshots.

Most Recent Database Backup for Each Database

-------------------------------------------------------------------------------------------  --Most Recent Database Backup for Each Database  -------------------------------------------------------------------------------------------  SELECT      CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,     msdb.dbo.backupset.database_name,      MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date  FROM     msdb.dbo.backupmediafamily      INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id   WHERE msdb..backupset.type = 'D'  GROUP BY     msdb.dbo.backupset.database_name   ORDER BY      msdb.dbo.backupset.database_name            

Most Recent Database Backup for Each Database - Detailed

You can join the two result sets together by using the following query in order to return more detailed information about the last database backup for each database. The LEFT JOIN allows you to match up grouped data with the detailed data from the previous query without having to include the fields you do not wish to group on in the query itself.

-------------------------------------------------------------------------------------------  --Most Recent Database Backup for Each Database - Detailed  -------------------------------------------------------------------------------------------  SELECT      A.[Server],      A.last_db_backup_date,      B.backup_start_date,      B.expiration_date,     B.backup_size,      B.logical_device_name,      B.physical_device_name,       B.backupset_name,     B.description  FROM     (     SELECT          CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,        msdb.dbo.backupset.database_name,         MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date     FROM        msdb.dbo.backupmediafamily         INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id      WHERE        msdb..backupset.type = 'D'     GROUP BY        msdb.dbo.backupset.database_name      ) AS A     LEFT JOIN      (     SELECT          CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,        msdb.dbo.backupset.database_name,         msdb.dbo.backupset.backup_start_date,         msdb.dbo.backupset.backup_finish_date,        msdb.dbo.backupset.expiration_date,        msdb.dbo.backupset.backup_size,         msdb.dbo.backupmediafamily.logical_device_name,         msdb.dbo.backupmediafamily.physical_device_name,          msdb.dbo.backupset.name AS backupset_name,        msdb.dbo.backupset.description     FROM        msdb.dbo.backupmediafamily         INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id      WHERE        msdb..backupset.type = 'D'     ) AS B     ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]  ORDER BY      A.database_name            

Note: for readability the output was split into two screenshots.

Databases Missing a Data (aka Full) Back-Up Within Past 24 Hours

At this point we've seen how to look at the history for databases that have been backed up. While this information is important, there is an aspect to backup metadata that is slightly more important - which of the databases you administer have not been getting backed up. The following query provides you with that information (with some caveats.)

-------------------------------------------------------------------------------------------  --Databases Missing a Data (aka Full) Back-Up Within Past 24 Hours  -------------------------------------------------------------------------------------------  --Databases with data backup over 24 hours old  SELECT     CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,     msdb.dbo.backupset.database_name,     MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,     DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]  FROM     msdb.dbo.backupset  WHERE     msdb.dbo.backupset.type = 'D'   GROUP BY     msdb.dbo.backupset.database_name  HAVING     (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))    UNION    --Databases without any backup history  SELECT          CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,      master.sys.sysdatabases.NAME AS database_name,      NULL AS [Last Data Backup Date],      9999 AS [Backup Age (Hours)]   FROM     master.sys.sysdatabases     LEFT JOIN msdb.dbo.backupset ON master.sys.sysdatabases.name = msdb.dbo.backupset.database_name  WHERE     msdb.dbo.backupset.database_name IS NULL     AND master.sys.sysdatabases.name <> 'tempdb'  ORDER BY      msdb.dbo.backupset.database_name            

Now let me explain those caveats, and this query.

• Caveat #1 - the first part of the query returns all records where the last database (full) backup is older than 24 hours from the current system date. This data is then combined via the UNION statement to the second portion of the query. That second statement returns information on all databases that have no backup history. I've taken the liberty of singling tempdb out from the result set since you do not back up that system database. It is recreated each time the SQL Server services are restarted.
• Caveat #2 - is the arbitrary value I've assigned to the aging value for databases without any backup history. I've set that value at 9999 hours because in my environment I want to place a higher emphasis on those databases that have never been backed up.

Using this final query I produce a report that is distributed to the DBA Team on a daily basis that highlights any missed backups.

Next Steps

• This tip has been tested with SQL Server 2019 and should work from SQL 2005 and later.
• Review this tip, Do you know if your SQL Server database backups are successful.
• Managing SQL Server Database and Application Metadata provides further information on creating repositories for database metadata.

Related Articles

Popular Articles

About the author

Article Last Updated: 2021-10-04

Fail Back Always on Databases From Dr Site to Prod Site

Source: https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/

Share this post