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 for the database.

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 (http://blog.SQLAuthority.com)

About these ads

10 thoughts on “SQL SERVER – Finding Last Backup Time for All Database – Last Full, Differential and Log Backup

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

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

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

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

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

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

    • Here is my suggestion based on your suggestion.
      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’,
      CASE d.recovery_model WHEN 1 THEN ‘Full’ WHEN 2 THEN ‘Bulk Logged’ WHEN 3 THEN ‘Simple’ END RecoveryModel
      FROM MASTER.sys.databases d
      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

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

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

  9. Pingback: SQL SERVER – Finding Last Backup Time for All Database – Last Full, Differential and Log Backup – Optimized | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s