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

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
Previous Post
Developer – How To Market Yourself as a Software Developer
Next Post
SQL SERVER – Unable to ALTER Computed Column in SQL Server – How to ALTER Computed Column

Related Posts

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

Leave a Reply