Script to retrieve SQL Server database backup history and no backups

By:   |   Updated: 2021-10-04   |   Comments (40)   |   Related: 1 | 2 | 3 | > 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.

database name
backup

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 
server

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.

server
physical device

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 
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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2021-10-04

Comments For This Article




Sunday, February 25, 2024 - 8:34:35 PM - teja Back To Top (92010)
this very nice article very good i got good knowledge for this article

Friday, August 5, 2022 - 4:47:01 PM - yogi Back To Top (90355)
when using availability groups these scripts fail after failover. how to pull the backup history from other nodes as well?

Tuesday, March 15, 2022 - 9:01:54 PM - Shohel Ahmed Back To Top (89889)
Great article and thank you so much.

Friday, September 3, 2021 - 9:43:16 AM - Karen Payne Back To Top (89203)
Great article, thanks for sharing

Thursday, August 12, 2021 - 11:10:41 AM - Carmelo Cardona Back To Top (89119)
Good scripts!!! I need to include the database owner in the report.

Wednesday, September 25, 2019 - 9:21:29 AM - sankar prasad sahu Back To Top (82566)
----Databases Missing a Data (aka Full) Back-Up Within Past 24 Hours(skips offline DBs)---
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.databases.NAME AS database_name,  
   NULL AS [Last Data Backup Date],  
   9999 AS [Backup Age (Hours)]  
FROM 
   master.sys.databases LEFT JOIN msdb.dbo.backupset 
       ON master.sys.databases.name  = msdb.dbo.backupset.database_name 
WHERE msdb.dbo.backupset.database_name IS NULL AND master.sys.databases.name <> 'tempdb' and state = 0
ORDER BY  
   msdb.dbo.backupset.database_name 

Friday, October 19, 2018 - 3:16:22 PM - BillRob Back To Top (77986)

Great article, you should include the Differential backup type too.

CASE msdb..backupset.type 

WHEN 'D' THEN 'Database' 

WHEN 'L' THEN 'Log' 

WHEN 'I' THEN 'Diff'

END AS backup_type,


Thursday, June 7, 2018 - 1:09:50 PM - jatin Back To Top (76151)

 Now a days the Disk backups tools also ark and entry in the backup history tables as full backup . So event when the SQL backup jobs failes the query will not retreive the results as disk level bakups makes an entry in the tables. by adding "and msdb.dbo.backupset.name is NOT NULL" . we can avoid the entries from disk levle backups:-

------------------------------------------------------------------------------

 

------------- 

--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'   and msdb.dbo.backupset.name is NOT NULL 

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 

 

 


Thursday, June 7, 2018 - 12:21:08 PM - jatin Back To Top (76150)

 

 The query for missing backups is picking up the databases from the backup history that no more exisits . This is giving a less accurate output

 


Friday, March 16, 2018 - 10:38:23 AM - VC Back To Top (75444)

 This post helped me a lot. Thank you!

 


Thursday, March 15, 2018 - 8:00:51 AM - Suman Roy Back To Top (75424)

 Your post helped me a lot

 

Thanks

 


Wednesday, August 2, 2017 - 9:23:10 AM - Suman Back To Top (63769)

 Hi Greg,

Thanks for the reply appreciate it...

 


Tuesday, August 1, 2017 - 8:59:06 AM - Greg Robidoux Back To Top (63671)

Hi Suman, you can run RESTORE commands to get different information about what is in the backup file.

Take a look at this tutorial: https://www.mssqltips.com/sqlservertutorial/85/sql-server-restore-commands/

-Greg


Tuesday, August 1, 2017 - 8:48:09 AM - Suman Back To Top (63667)

My client gave me an old backup file and asked me to which database it belongs and it’s complete details.

How can we get that info?

 

Thanks & Regards,

Suman

 


Monday, July 31, 2017 - 4:57:01 PM - Mike Andrews Back To Top (63650)

Thank you!  To a nube, this is magical.  One further step would be very helpul - we have 16 servers with a combined 20 instances.  How might one run this query for all instances, but run it from one spot (and maybe have the results emailed out?)  Thanks, again!


Tuesday, April 11, 2017 - 11:42:09 PM - Alejandro Hernández Silva Back To Top (54702)

Hi Tim,

Great tips for the DBAs comunity is wonderfull to have so many expert that help us, thanks for that, I need some help with my query, I have the query to get the numbers of the days without recent backups according with my retention policy, but I need to create a SQL agent job that fail when any database has more days without a backup, I know that I can use a raiserror but I don't know how to get the proper instruction to force the job fail and force a checking process.

Thanks in Advance. 

 


Monday, September 19, 2016 - 3:37:55 AM - KISHORE Back To Top (43351)

 

Hi,

 

Is there any method available to recover  old date .bak  file from MSSQL 2008 R2 by using

dbo.backupset,dbo.backupmediafamily,dbo.backupfile or else any other recovery strategy  availble

 

issue:we have deleted the .bak file without taking backup,generated in the physical path 

 

 

Thanks

Kishore

 

 


Monday, January 11, 2016 - 7:59:14 AM - Dmitry Back To Top (40390)

It`s very hehelpfull scripts. Thanks!!!

But can anybody help me?

I need to join info about BACKUP SETS and JOBS in Sql Agent 

Is there some intersection points?

 

 


Thursday, September 17, 2015 - 10:44:11 AM - Vikana Back To Top (38698)

Very Handy, great scripts, Thank you very much.


Thursday, June 11, 2015 - 5:34:37 PM - Greg Robidoux Back To Top (37903)

You can try to add this to the code to get the user that ran the backup.

msdb.dbo.backupset.user_name


Thursday, June 11, 2015 - 2:03:14 PM - Sylvester Back To Top (37902)

Hi Tim,

 

Could you pls. tell whether is it possible to know the user credentials who took the last database backup ?

 

Many thanks in advance.

 


Wednesday, June 10, 2015 - 4:47:47 PM - NaveenNaidu Back To Top (37889)

Amazing scripts. Being not a DBA, this helped me (dev) a lot


Tuesday, May 19, 2015 - 5:11:55 AM - Tirupathirao Back To Top (37218)

 

The script are very nice and useful

But I want last one year data in single backup file

 

Thanks for advance

 


Tuesday, April 14, 2015 - 1:28:33 PM - Hank Freeman Back To Top (36931)

 

Thanks for the Code works great;

I use it in a dev environment where we want to make sure the PROD back we are restoring is the correct one, post the restore

Two changes I made

1. changed your CHAR(100) on CONVERT(varCHAR(100), SERVERPROPERTY('Servername')) AS 'Server' to get rid of the the spaces on the right... Could have used rtrim(CONVERT(CHAR(100), SERVERPROPERTY('Servername'))) AS 'Server' as well..

2. Used this function to convert your backup size to varchar number with commas, from this site: http://stackoverflow.com/questions/13207075/format-a-number-with-commas-but-without-decimals-in-sql-server-2008-r2

Example changing the code ever so slightly on the select gives the better result for reading.

B.backup_size,  dbo.Utility_fn_FormatNumberAndTruncateDecimals(B.backup_size) as 'Backup_size-C' , 

backup_sizeBackup_size-C
53243789312 53,243,789,312
164830818304 164,830,818,304

 end

Hank Freeman


Friday, April 3, 2015 - 4:09:50 PM - Anjum Sumaiya Back To Top (36822)

Thanks a lot. This makes me to complete my work very easier..


Monday, December 8, 2014 - 4:19:02 PM - rosemberg ennes Back To Top (35547)
hello Tim Ford.
Congratulations on the post.

Thursday, September 18, 2014 - 5:47:14 PM - Jamie Back To Top (34614)

Missing 'databse_name' from "Most Recent Database Backup for Each Database - Detailed":

--------------

SELECT  

   A.[Server],  

   A.database_name,

   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 

...

-----------

Thanks a lot for these scripts!


Friday, January 24, 2014 - 1:36:11 PM - James Chan Back To Top (28217)

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? 


Tuesday, September 24, 2013 - 2:02:15 PM - KUSQLDBA Back To Top (26917)

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


Friday, July 5, 2013 - 6:55:28 AM - Panimu Back To Top (25721)

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.


Monday, June 24, 2013 - 9:40:49 PM - vinni Back To Top (25548)

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.


Monday, April 22, 2013 - 1:04:01 PM - Rama Back To Top (23488)

 

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


Saturday, March 16, 2013 - 2:48:53 AM - Shreenidhi R Back To Top (22822)

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!


Thursday, March 7, 2013 - 10:26:42 PM - unruledboy Back To Top (22643)

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


Tuesday, February 12, 2013 - 4:59:25 AM - Diego Back To Top (22062)

GREAT SCRIPTS everybody!

Many thanks!!!!


Wednesday, November 21, 2012 - 3:04:31 AM - Filip Back To Top (20447)

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, September 18, 2012 - 9:33:00 PM - Grazil Back To Top (19557)

 

KUDOS! This really helped me! Thanks!


Monday, May 21, 2012 - 9:17:02 AM - Dinesh Vishe Back To Top (17570)

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


Friday, October 24, 2008 - 6:58:31 AM - crcobb Back To Top (2076)

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


Friday, October 10, 2008 - 1:29:26 AM - tosc Back To Top (1951)

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














get free sql tips
agree to terms