SQL SERVER – Finding Last Backup Time for All Database – Last Full, Differential and Log Backup

April 4, 2014

About four years ago, I wrote a blog post where I posted a script about finding backup time for all the databases. You can see the blog post over here SQL SERVER – Finding Last Backup Time for All Database. It has been a very popular script. However, this script was just giving details about last full backup time. SQL Expert Sravani has posted a fantastic script which also displays last full, differential and log backup of the database.

SQL SERVER - Finding Last Backup Time for All Database - Last Full, Differential and Log Backup backuptime-800x353

Here is the script.

SET NOCOUNT ON
GO
SET quoted_identifier OFF
DECLARE @dbname AS VARCHAR(80)
DECLARE @msgdb AS VARCHAR(100)
DECLARE @dbbkpname AS VARCHAR(80)
DECLARE @dypart1 AS VARCHAR(2)
DECLARE @dypart2 AS VARCHAR(3)
DECLARE @dypart3 AS VARCHAR(4)
DECLARE @currentdate AS VARCHAR(10)
DECLARE @server_name AS VARCHAR(30)
SELECT @server_name = @@servername
SELECT @dypart1 = DATEPART(dd,GETDATE())
SELECT @dypart2 = DATENAME(mm,GETDATE())
SELECT @dypart3 = DATEPART(yy,GETDATE())
SELECT @currentdate= @dypart1 + @dypart2 + @dypart3
PRINT "#################################################################"
PRINT "# SERVERNAME : "+ @server_name + " DATE : "+ @currentdate +"#"
PRINT "#################################################################"
PRINT "DatabaseName Full Diff TranLog"
PRINT "#################################################################"
SELECT SUBSTRING(s.name,1,50) AS 'DATABASE Name',
b.backup_start_date AS 'Full DB Backup Status',
c.backup_start_date AS 'Differential DB Backup Status',
d.backup_start_date AS 'Transaction Log Backup Status'
FROM MASTER..sysdatabases s
LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name
AND b.backup_start_date =
(SELECT MAX(backup_start_date)AS 'Full DB Backup Status'
FROM msdb..backupset
WHERE database_name = b.database_name
AND TYPE = 'D') -- full database backups only, not log backups
LEFT OUTER JOIN msdb..backupset c
ON s.name = c.database_name
AND c.backup_start_date =
(SELECT MAX(backup_start_date)'Differential DB Backup Status'
FROM msdb..backupset
WHERE database_name = c.database_name
AND TYPE = 'I')
LEFT OUTER JOIN msdb..backupset d
ON s.name = d.database_name
AND d.backup_start_date =
(SELECT MAX(backup_start_date)'Transaction Log Backup Status'
FROM msdb..backupset
WHERE database_name = d.database_name
AND TYPE = 'L')
WHERE s.name <>'tempdb'
ORDER BY s.name

Sravani, please send me email at my mail id and I will send USD 20 worth Amazon Gift Card or INR 1000 Flipkart Gift Voucher for your wonderful contribution. If you use any such script in your daily routine. Please send me email and I will be glad to post the same on blog with due credit.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Backup, SQL Scripts, SQL Server

Developer – How To Market Yourself as a Software Developer

SQL SERVER – Unable to ALTER Computed Column in SQL Server – How to ALTER Computed Column

18 Comments. Leave new

Lakshmi Narayana
April 4, 2014 5:23 pm
we are using below script
USE [msdb]
GO
;WITH [MostRecentBackupStatus_CTE]
AS
(
SELECT bsfull.[server_name] ,
bsfull.[database_name] ,
bsfull.[backup_finish_date] AS [last_full_backup] ,
bsdiff.[backup_finish_date] AS [last_diff_backup] ,
bstlog.[backup_finish_date] AS [last_tran_backup] ,
DATEDIFF(dd, bsfull.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_full_backup] ,
DATEDIFF(dd, bsdiff.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_diff_backup] ,
DATEDIFF(hh, bstlog.[backup_finish_date], CURRENT_TIMESTAMP) AS [hours_since_tranlog_backup] ,
( SELECT [physical_device_name]
FROM [msdb]..[backupmediafamily] bmf
WHERE bmf.[media_set_id] = bsfull.[media_set_id]
) AS [full_backup_location] ,
( SELECT [physical_device_name]
FROM [msdb]..[backupmediafamily] bmf
WHERE bmf.[media_set_id] = bsdiff.[media_set_id]
) AS [diff_backup_location] ,
( SELECT [physical_device_name]
FROM [msdb]..[backupmediafamily] bmf
WHERE bmf.[media_set_id] = bstlog.[media_set_id]
) AS [tlog_backup_location]
FROM [msdb]..[backupset] AS bsfull
LEFT JOIN [msdb]..[backupset] AS bstlog ON bstlog.[database_name] = bsfull.[database_name]
AND bstlog.[server_name] = bsfull.[server_name]
AND bstlog.[type] = ‘L’
AND bstlog.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
FROM [msdb]..[backupset] b2
WHERE b2.[database_name] = bsfull.[database_name]
AND b2.[server_name] = bsfull.[server_name]
AND b2.[type] = ‘L’) )
LEFT JOIN [msdb]..[backupset] AS bsdiff ON bsdiff.[database_name] = bsfull.[database_name]
AND bsdiff.[server_name] = bsfull.[server_name]
AND bsdiff.[type] = ‘I’
AND bsdiff.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
FROM [msdb]..[backupset] b2
WHERE b2.[database_name] = bsfull.[database_name]
AND b2.[server_name] = bsfull.[server_name]
AND b2.[type] = N’I’) )
WHERE bsfull.[type] = N’D’
AND bsfull.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
FROM [msdb]..[backupset] b2
WHERE b2.[database_name] = bsfull.[database_name]
AND b2.[server_name] = bsfull.[server_name]
AND b2.[type] = N’D’) )
AND EXISTS ( SELECT [name]
FROM [master].[sys].[databases]
WHERE [name] = bsfull.[database_name] )
AND bsfull.[database_name] N’tempdb’
)
SELECT c.[server_name] ,
c.[database_name] ,
d.[recovery_model_desc] ,
c.[last_full_backup] ,
c.[last_diff_backup] ,
c.[last_tran_backup] ,
c.[days_since_full_backup] ,
c.[days_since_diff_backup] ,
c.[hours_since_tranlog_backup] ,
c.[full_backup_location] ,
c.[diff_backup_location] ,
c.[tlog_backup_location]
FROM [MostRecentBackupStatus_CTE] c
INNER JOIN [master].[sys].[databases] d ON c.[database_name] = d.[name];
GO
Reply
- KiranAcchitapuram
  July 27, 2015 9:11 am
  The script is not working, PFB, error
  Msg 4104, Level 16, State 1, Line 1
  The multi-part identifier “bsfull.database_name” could not be bound.
  AND EXISTS (SELECT [name]FROM [master].[sys].[databases]WHERE [name] = bsfull.[database_name]) AND bsfull.[database_name]N’tempdb’
  Reply
Aaron
April 5, 2014 7:04 pm
Really, query above can be significantly optimized:
SELECT
s.name
, [Full DB Backup Status] = D
, [Differential DB Backup Status] = I
, [Transaction Log Backup Status] = L
FROM sys.sysdatabases s
LEFT JOIN (
SELECT p.*
FROM (
SELECT [type], database_name, backup_start_date
FROM msdb.dbo.backupset
WHERE type IN (‘L’, ‘I’, ‘D’)
) t
PIVOT (
MAX(backup_start_date)
FOR [type] IN ([L], [I], [D])
) p
) t ON s.name = t.database_name
ORDER BY s.name
I took this query from dbForge Studio for SQL Server.
Reply
Robert
April 6, 2014 3:25 am
The query can be modified to use sys.databases instead of sys.sysdatabases to list the recovery model of the databases. This is very helpful when you have a server with many databases.
SELECT
s.name
, s.recovery_model_desc
, [Full DB Backup Status] = D
, [Differential DB Backup Status] = I
, [Transaction Log Backup Status] = L
FROM sys.databases s
LEFT JOIN (
SELECT p.*
FROM (
SELECT [type], database_name, backup_start_date
FROM msdb.dbo.backupset
WHERE type IN (‘L’, ‘I’, ‘D’)
) t
PIVOT (
MAX(backup_start_date)
FOR [type] IN ([L], [I], [D])
) p
) t ON s.name = t.database_name
ORDER BY s.name
Reply
TheSQLGuru
April 7, 2014 1:54 am
SET NOCOUNT ON
GO
–unnecessary
–SET quoted_identifier OFF
–NONE of these variables are necessary, but if you DO create many variables, do them as a SINGLE operation
–Also varchar(2 and 3) are inefficient as storages constructs, especially if they are on an actual table
–DECLARE @dbname AS VARCHAR(80), @msgdb AS VARCHAR(100), @dbbkpname AS VARCHAR(80), @dypart1 AS VARCHAR(2),
— @dypart2 AS VARCHAR(3), @dypart3 AS VARCHAR(4), @currentdate AS VARCHAR(10), @server_name AS VARCHAR(30)
–again, these aren’t necessary, but combine them into a SINGLE SELECT to be most efficient
–also, why use 3 variables and 3 functions simply to get the date?!?
–SELECT @server_name = @@servername, @dypart1 = DATEPART(dd,GETDATE()), @dypart2 = DATENAME(mm,GETDATE()),
— @dypart3 = DATEPART(yy,GETDATE()), @currentdate= @dypart1 + @dypart2 + @dypart3
PRINT “#####################################################################”
PRINT “# SERVERNAME : “+ @@servername + ” DATE : “+ CONVERT(char(10), getdate(), 112) +”#”
PRINT “#####################################################################”
–this part isn’t needed because the output has verbose column headings already
–PRINT “DatabaseName Full Diff TranLog”
–PRINT “##########################################################################################################################################”
–the query has all kinds of issues, but the MAIN one is hitting the same table THREE times when ONCE will suffice
–we run the risk of duplicate database names here with the 50 length limitation. remove it. also a teeny bit less work
–better formatting (for me)
–ALWAYS reference objects explicitly by their schema!!
–and I HATE having spaces in the name and dealing with the consequences of that!! :)
SELECT d.name AS ‘DATABASE_Name’,
MAX(CASE WHEN bu.Type = ‘D’ THEN bu.LastBackupDate END) AS ‘Full DB Backup Status’,
MAX(CASE WHEN bu.Type = ‘I’ THEN bu.LastBackupDate END) AS ‘Differential DB Backup Status’,
MAX(CASE WHEN bu.Type = ‘L’ THEN bu.LastBackupDate END) AS ‘Transaction DB Backup Status’
FROM MASTER.sys.databases d –better alias?
LEFT OUTER JOIN (SELECT database_name, type, MAX(backup_start_date) as LastBackupDate
FROM msdb.dbo.backupset
GROUP BY database_name, type) as bu ON d.name = bu.database_name
GROUP BY d.Name
original query:
1.478 query cost
Table ‘backupset’. Scan count 37, logical reads 12540
Table ‘Worktable’. Scan count 1, logical reads 57
Table ‘Worktable’. Scan count 0, logical reads 0
Table ‘sysdbreg’. Scan count 2, logical reads 4
refactored query:
0.2867 query cost
Table ‘Worktable’. Scan count 1, logical reads 75
Table ‘backupset’. Scan count 1, logical reads 285
Table ‘sysdbreg’. Scan count 1, logical reads 2
query cost: 5.2X improvement
read cost: 34.8X better
KEY TAKE-AWAYS:
1) Group DECLARES and SETS/SELECTs of variables
2) Don’t use variables when inline constructs or direct access will suffice
3) Use schemas on EVERY object you can
4) NEVER hit a table more than once if you don’t have to
5) Learn to use the CASE statement – it is my favorite 4-letter TSQL word! :-D The CASE query is 0.02 more efficient than the PIVOT statement above.
Reply
TheSQLGuru
April 7, 2014 1:55 am
There were so many things your readers could benefit from Pinal I just had to comment on this one.
SET NOCOUNT ON
GO
–unnecessary
–SET quoted_identifier OFF
–NONE of these variables are necessary, but if you DO create many variables, do them as a SINGLE operation
–Also varchar(2 and 3) are inefficient as storages constructs, especially if they are on an actual table
–DECLARE @dbname AS VARCHAR(80), @msgdb AS VARCHAR(100), @dbbkpname AS VARCHAR(80), @dypart1 AS VARCHAR(2),
— @dypart2 AS VARCHAR(3), @dypart3 AS VARCHAR(4), @currentdate AS VARCHAR(10), @server_name AS VARCHAR(30)
–again, these aren’t necessary, but combine them into a SINGLE SELECT to be most efficient
–also, why use 3 variables and 3 functions simply to get the date?!?
–SELECT @server_name = @@servername, @dypart1 = DATEPART(dd,GETDATE()), @dypart2 = DATENAME(mm,GETDATE()),
— @dypart3 = DATEPART(yy,GETDATE()), @currentdate= @dypart1 + @dypart2 + @dypart3
PRINT “#####################################################################”
PRINT “# SERVERNAME : “+ @@servername + ” DATE : “+ CONVERT(char(10), getdate(), 112) +”#”
PRINT “#####################################################################”
–this part isn’t needed because the output has verbose column headings already
–PRINT “DatabaseName Full Diff TranLog”
–PRINT “##########################################################################################################################################”
–the query has all kinds of issues, but the MAIN one is hitting the same table THREE times when ONCE will suffice
–we run the risk of duplicate database names here with the 50 length limitation. remove it. also a teeny bit less work
–better formatting (for me)
–ALWAYS reference objects explicitly by their schema!!
–and I HATE having spaces in the name and dealing with the consequences of that!! :)
SELECT d.name AS ‘DATABASE_Name’,
MAX(CASE WHEN bu.Type = ‘D’ THEN bu.LastBackupDate END) AS ‘Full DB Backup Status’,
MAX(CASE WHEN bu.Type = ‘I’ THEN bu.LastBackupDate END) AS ‘Differential DB Backup Status’,
MAX(CASE WHEN bu.Type = ‘L’ THEN bu.LastBackupDate END) AS ‘Transaction DB Backup Status’
FROM MASTER.sys.databases d –better alias?
LEFT OUTER JOIN (SELECT database_name, type, MAX(backup_start_date) as LastBackupDate
FROM msdb.dbo.backupset
GROUP BY database_name, type) as bu ON d.name = bu.database_name
GROUP BY d.Name
original query:
1.478 query cost
Table ‘backupset’. Scan count 37, logical reads 12540
Table ‘Worktable’. Scan count 1, logical reads 57
Table ‘Worktable’. Scan count 0, logical reads 0
Table ‘sysdbreg’. Scan count 2, logical reads 4
refactored query:
0.2867 query cost
Table ‘Worktable’. Scan count 1, logical reads 75
Table ‘backupset’. Scan count 1, logical reads 285
Table ‘sysdbreg’. Scan count 1, logical reads 2
query cost: 5.2X improvement
read cost: 34.8X better
KEY TAKE-AWAYS:
1) Group DECLARES and SETS/SELECTs of variables
2) Don’t use variables when inline constructs or direct access will suffice
3) Use schemas on EVERY object you can
4) NEVER hit a table more than once if you don’t have to
5) Learn to use the CASE statement – it is my favorite 4-letter TSQL word! :-D This CASE query is 0.02 more efficient than the PIVOT one above.
Reply
TheSQLGuru
April 7, 2014 2:01 am
Actually here is another enhancement. I put in the recovery_model of the database so you don’t flip out about databases that don’t have tlog backups! :)
SELECT d.name AS ‘DATABASE_Name’,
MAX(CASE WHEN bu.Type = ‘D’ THEN bu.LastBackupDate END) AS ‘Full DB Backup Status’,
MAX(CASE WHEN bu.Type = ‘I’ THEN bu.LastBackupDate END) AS ‘Differential DB Backup Status’,
MAX(CASE WHEN bu.Type = ‘L’ THEN bu.LastBackupDate END) AS ‘Transaction DB Backup Status’,
d.recovery_model
FROM MASTER.sys.databases d –better alias?
LEFT OUTER JOIN (SELECT database_name, type, MAX(backup_start_date) as LastBackupDate
FROM msdb.dbo.backupset
GROUP BY database_name, type) as bu ON d.name = bu.database_name
GROUP BY d.Name, d.recovery_model
Reply
Graham Tuxford
April 7, 2014 3:23 pm
Brilliant scripts but I would have used sys.databases instead of sys.sysdatabases and also selected recovery_model_desc to ensure that you know if the database is in simple mode and hence doesn’t need to have log backups
other than that keep up the good work
Reply
TheSQLGuru
April 10, 2014 10:36 pm
Good on you Pinal for de-coding the recovery_model number into a meaningful string. I sometimes forget that not everyone is a consultant with 45000 man hours in SQL Server under their belt. :-D
Reply
Karcsi
November 20, 2014 8:16 pm
Thanks for the script!
I am referring below to the script as shown in the article, and not to those in the comments.
For me it was important to find out the time of the last full backup of a certain database, ignoring the “copy-only” full backups. For this I had to modify the line
AND TYPE = ‘D’) — full database backups only, not log backups
this way:
AND TYPE = ‘D’ AND flags & 1024 = 0) — (not copy-only) full database backups only, not log backups
I hope this will be useful for some readers.
Karcsi
Reply
- William Hug
  March 24, 2015 10:39 pm
  I see a gotcha…The database backups must be after the create date of the database.
  Reply
  - Pinal Dave
    March 26, 2015 7:52 pm
    William – I didn’t understand.
KiranAcchitapuram
July 27, 2015 9:18 am
Hi All,
Can anyone help to with the T-sql script to get the below mentioned columns to select….
server_name,
database_name,
Creation_date,
status_desc,
recovery_model_desc,
last_full_backup,
full_backup_location,
last_diff_backup,
diff_backup_location,
last_tran_backup,
tlog_backup_location
days_since_full_backup,
days_since_diff_backup,
hours_since_tranlog_backup
Immense Thanks in Advance…!!!
Reply
Derek
March 17, 2016 1:04 am
Does anyone know if you can put an AFTER INSERT trigger on msdb.dbo.backupset? I’m leaning towards no based on my lack of success in getting a trigger to fire, but was hoping for final confirmation. Thanks!
Reply
- Prashant
  April 25, 2017 1:20 pm
  Hi Derek,
  Any joy with AFTER INSERT trigger on msdb.dbo.backupset, I’m facing the same issue. The trigger just won’t trigger. Thanks.
  Reply
murai
April 26, 2019 4:26 pm
Hi I have one question can you please give answer .
If the database full backup is not taken more than 8 days on the instance, query should show those databases.
Reply