Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Script to retrieve SQL Server database backup history and no backups

MSSQLTips author Tim Ford By:   |   Read Comments (13)   |   Related Tips: 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(datetimemsdb.dbo.backupset.backup_start_date102) >= 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_dateAS 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_dateAS 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_dateAS last_db_backup_date,
   
DATEDIFF(hhMAX(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, - 24GETDATE())) 

UNION 

--Databases without any backup history
SELECT     
   
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server
   
master.dbo.sysdatabases.NAME AS database_name
   NULL 
AS [Last Data Backup Date]
   
9999 AS [Backup Age (Hours)] 
FROM
   
master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
       
ON master.dbo.sysdatabases.name  msdb.dbo.backupset.database_name
WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb'
ORDER BY 
   
msdb.dbo.backupset.database_name

Now let me explain those caveats, and this query.  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.  That is caveat #1.  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 via SQL Server Reporting Services that is distributed to the DBA Team on a daily basis that highlights any missed backups.  That, however, is for another tip.

Next Steps

  • Review previous tips from MSSQLtips.com on backup history.
  • This tip provides further information on creating repositories for database metadata.
  • Stay tuned to MSSQLTips.com for future tips on reporting backup exception metadata via SQL Server Reporting Services. 


Last Update: 10/9/2008


About the author
MSSQLTips author Tim Ford
Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Friday, October 10, 2008 - 1:29:26 AM - tosc Read The Tip

Hi,

or another way:

CREATE VIEW ViewLastBackup
AS
SELECT a.name AS 'DataBase', Backup_Date, Days_since_last_Backup
FROM master.dbo.sysdatabases AS a LEFT JOIN
(SELECT database_name,MAX(backup_finish_date) AS Backup_Date,
ABS(DATEDIFF(day,GETDATE(),MAX(backup_finish_date))) AS Days_since_last_Backup
FROM msdb.dbo.backupset WHERE backup_finish_date <= GETDATE()
GROUP BY database_name) AS b
ON a.name = b.database_name

SELECT * FROM ViewLastBackup WHERE Backup_Date IS NOT NULL
ORDER BY 2 DESC,1

SELECT * FROM ViewLastBackup WHERE Backup_Date IS NULL
ORDER BY 2 DESC,1

Friday, October 24, 2008 - 6:58:31 AM - crcobb Read The Tip

Great scripts they have saved me a bunch of time getting a handle on the 25 SQL servers that I have inherited.  I have been using your Back-up in the Past 24 hours and I found it was displaying Databases that have been deleted but there were still history enteries.  I added an inner join so I only get results on existing databases.

Chris

 -------------------------------------------------------------------------------------------
--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 INNER JOIN master.dbo.sysdatabases ON master.dbo.sysdatabases.NAME = msdb.dbo.backupset.database_name
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.dbo.sysdatabases.NAME AS database_name, 
   NULL AS [Last Data Backup Date], 
   9999 AS [Backup Age (Hours)] 
FROM
   master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
       ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name
WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb'
ORDER BY 
   msdb.dbo.backupset.database_name


Monday, May 21, 2012 - 9:17:02 AM - Dinesh Vishe Read The Tip

great .........I want to check using differnce backup type ????


Tuesday, September 18, 2012 - 9:33:00 PM - Grazil Read The Tip

 

KUDOS! This really helped me! Thanks!


Wednesday, November 21, 2012 - 3:04:31 AM - Filip Read The Tip

This article is an example how it should be: simple, clear, to the point and very nicely presented ! This is thé added value of the www !

 

;-

 

Filip


Tuesday, February 12, 2013 - 4:59:25 AM - Diego Read The Tip

GREAT SCRIPTS everybody!

Many thanks!!!!


Thursday, March 07, 2013 - 10:26:42 PM - unruledboy Read The Tip

Is the last db backup date same as "dbi_dbccLastKnownGood" from "DBCC DBINFO WITH TABLERESULTS"?


Saturday, March 16, 2013 - 2:48:53 AM - Shreenidhi R Read The Tip

Hi

I have a question.

I have done a restore on a pirticular database. The restore copy (.BAK) file was in two locations. But now I have deleted that two locations.

All I want to know is that; Can I know the attributes of that .bak file which was restored on 17th feb which is in the DB. Because, all I know is that the .bak   properties had "Modified date" which is the 17th of feb. So I m confused if I backed the wrong one or right one as I have deleted the backup location.

Thanks!


Monday, April 22, 2013 - 1:04:01 PM - Rama Read The Tip

 

HI Tim

Wonderful scripts. I have one question as Im a newbee in Sql Server.

I have multiple instances across multiple database servers. i.e. N:N databases and instances

For Ex: I have 30 different instances on 30 different servers as they all are different clients. I currently get 30 backup reports from all these servers by using your above queies on each server.

My Question is --> Do you have any query which can get a consolidated report from all these 30 servers. i.e. instead of me getting 30 different emails I just want 1 email with all instance backup reports in that.

Im trying to run this query from our Idear monitroing box - since this is the only box which can communicate to all the 30 diff servers so I thought this is the right place to run the big query.

 

Thx!

Rama


Monday, June 24, 2013 - 9:40:49 PM - vinni Read The Tip

Hi,

I have to create a report which shows Failed backup jobs on all multiple servers( we have more than 100 servers) to identify the most recently failed backup jobs( full, diff,log,monthly backups) so that we can fix immediately.I have created linked server as suggested above for all the servers in single SQL server system. Now I need to have a script which gives me server name, database name, back up job that failed, date of last run backup job on all these servers . Can you please provide me with a script that helps me create a report like this.

Can you please give me a script which can generate failed backup's for all servers,all databases.


Friday, July 05, 2013 - 6:55:28 AM - Panimu Read The Tip

The scripts do not work. Or at least "Database Backups for all databases For Previous Week" does not. It actually shows any use of a backupset, including restores.


Tuesday, September 24, 2013 - 2:02:15 PM - KUSQLDBA Read The Tip

Excellent scripts! Clean...to the point! Thanks Mate!


Friday, January 24, 2014 - 1:36:11 PM - James Chan Read The Tip

I have a database that is backed up via maintenance plan. The database is scheduled to be backed up at 8:00PM. It usually takes 6 hours to complete. According to this query, the database doesn't start at 8:00PM. It starts the next day at 12:03AM. If the backup completes before 12:00AM, the query shows that the backup was started at 8:00PM.

Scenario 1: Backup begins at 1/5/14 at 8pm. Backup completes at 1/6/14 at 2AM. This query displays that the backup began at 1/16/14 at 12:05AM.

Scenario 2: Backup begins at 1/6/14 at 8pm. Backup completes at 1/6/14 at 11pm. THe query displays that the began at 1/6/14 at 8pm.

If I look at the maintenance plan history logs, it shows that the database is always backed up at 8:00PM.

Any ideas to why I get these results? 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.