SQL SERVER – Finding Last Backup Time for All Database

Here is the quick script I use find last backup time for all the databases in my server instance.

SQL SERVER - Finding Last Backup Time for All Database backupsqlserver

SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name

The query above will return following result where we can see the database last database backup date and time.

SQL SERVER - Finding Last Backup Time for All Database backupsqlserver1

Do you use any other script for the same purpose, please share here, it will be interesting for all of us know.

Here are few other blog posts which are related to this subject.

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

SQL Backup, SQL DMV, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Fix: Error: MS Jet OLEDB 4.0 cannot be used for distributed queries because the provider is used to run in apartment mode.
Next Post
SQLAuthority News – Happy Deepavali and Happy News Year

Related Posts

49 Comments. Leave new

  • Looking for a way to identify databases that are using differential backups – any way to identify them and not full or log backups?

    Reply
  • Hi Pinal,
    In my application the database is created by the User, so I am executing the backup query (listing all the databses from sys.databases) using T-SQL Statement Task. Currently i have 392 databases in our azure virtual machine and I have scheduled a backup to azure blob storage. I will explain my problem now. Backup schedule is not running for all the databases. I have checked the history. But it is showing the schedule is executed successfully. Is there is any way to solve or track this. Expecting your reply

    Thanks and Regards
    Sreejith

    Reply
  • Hi,

    I am using the below script for checking the status of latest full, diff and tlog backups for all the databases.

    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

    Reply
  • Hi all…
    when I execute (A) on the server, I got more detailed result of back-up for field “Last_BKP”.
    If I execute from a different server using a “Linked Server” (B) a got less row…field “Last_BKP
    ” is NULL for 4 rows.

    I did not understand why…
    ALEN

    ———————————– (A) ———————————-
    SELECT
    @@SERVERNAME [Istanza],
    sdb.Name AS DatabaseName,
    CONVERT(VARCHAR(13), MAX(bus.backup_finish_date), 103) AS [Last_BKP],
    DATEDIFF(day, MAX(bus.backup_finish_date), getdate()) DD_Late,
    MAX(bus.backup_finish_date) backup_finish_date
    FROM master.dbo.sysdatabases sdb
    LEFT OUTER JOIN msdb.dbo.backupset bus
    ON bus.database_name = sdb.name
    WHERE sdb.Name ‘tempdb’
    GROUP BY sdb.Name

    ———————————– (B) ———————————-
    SELECT
    ‘MyDB server name’ [Istanza],
    sdb.Name AS DatabaseName,
    CONVERT(VARCHAR(13), MAX(bus.backup_finish_date), 103) AS [Last_BKP],
    DATEDIFF(day, MAX(bus.backup_finish_date), getdate()) DD_Late,
    MAX(bus.backup_finish_date) backup_finish_date
    FROM [].master.dbo.sysdatabases sdb
    LEFT OUTER JOIN msdb.dbo.backupset bus
    ON bus.database_name = sdb.name
    WHERE sdb.Name ‘tempdb’
    GROUP BY sdb.Name

    Reply
  • give query very full to every one…

    WITH sqltmp (DatabaseName, LastBackupId) AS
    (
    SELECT sdb.Name ,MAX(bus.backup_set_id)
    FROM sys.sysdatabases sdb
    LEFT OUTER JOIN msdb.dbo.backupset bus ON (bus.database_name = sdb.name AND bus.type = ‘D’ )
    GROUP BY sdb.name
    )
    SELECT @@SERVERNAME ‘Server’, a.DatabaseName, b.backup_start_date , b.backup_finish_date,m.physical_device_name,
    Convert( numeric (8,2),Round(b.backup_size/ (1024*1024),2) ) [Size_MB]
    FROM sqltmp a INNER JOIN msdb.dbo.backupset b ON
    ( a.LastBackupId = b.backup_set_id)
    LEFT OUTER JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
    WHERE a.DatabaseName NOT IN (‘tempdb’,’model’,’msdb’,’master’)
    ORDER BY DatabaseName

    Reply
  • can any one please tell me the script for ” how to find yesterday’s backups only “

    Reply
    • select sdb.name as DateBaseName ,convert(varchar(10), bus.backup_finish_date,101) as LastBacupdate,
      bus.machine_name as MeachineName,bus.server_name as ServerName,bus.user_name from sys.sysdatabases sdb
      join msdb.dbo.backupset bus
      on sdb.name=bus.database_name
      where DATEDIFF(day,bus.backup_finish_date,getdate())=1

      Reply
  • Hi, In your scripts you need to define explcitly which for the full,diff or log, because in your scripts it takes the logbackup date/time stamp, so that confuses for other guys.

    Reply
  • I have make changes in the query and get the required results

    DECLARE @db_name VARCHAR(100)
    SELECT @db_name = DB_NAME()

    SELECT TOP ( 30 )
    s.server_name,
    s.database_name,
    CASE s.[type]
    WHEN ‘D’ THEN ‘Full’
    WHEN ‘I’ THEN ‘Differential’
    WHEN ‘L’ THEN ‘Transaction Log’
    END as BackupType,
    s.recovery_model,
    s.backup_finish_date,
    s.backup_start_date,
    DATEDIFF(mi,s.backup_start_date , s.backup_finish_date)/60 as Total_Time_in_Hours,
    cast(CAST(s.backup_size / 1024 / 1024 / 1024 AS INT) as varchar(14))
    + ‘ ‘ + ‘GB’ as bkSize,
    CAST(s.first_lsn AS varchar(50)) AS first_lsn,
    CAST(s.last_lsn AS varchar(50)) AS last_lsn,
    m.physical_device_name
    FROM msdb.dbo.backupset s
    inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
    WHERE s.database_name = @db_name
    ORDER BY backup_start_date desc,
    backup_finish_date

    Reply
  • mr.lemonjello
    May 14, 2014 5:45 am

    I put together a stored proc to capture this information so I could put in a SQL Agent job and have it email when there aren’t any recent backups. … I know, I know. The backup job should alert you when it fails, but I have had to go oncall after a person who ignored those alerts so this is nice safety check.

    https://gallery.technet.microsoft.com:443/scriptcenter/Procedure-reports-8994befe

    Reply
  • i want to report
    databases and recovery model and last backup date
    please send script

    Reply
  • Ludovico Caldara
    February 26, 2015 9:06 pm

    here’s a script that works better, IMO.
    it makes use of with() and rank() over() to avoid supplemental joins or subqueries.

    Reply
  • this script doesnt work in sql server 2000 ..error getting invalid object

    Reply
  • how to check size of db since last backup??? Or how to check size for differntial backup??

    Reply
    • SP_HELPDB for size of the database and query MSDB.DBO.BACKUPSET table to get the size of differential backup. May be this will help
      select
      database_name
      ,CAST(ROUND(((backup_size/1024)/1024),2) AS DECIMAL (10,2)) AS [Backup size in MB]
      FROM msdb.dbo.backupset
      where type = ‘I’

      Reply
  • If SQL is on VM and sysadmin is taking snapshot every night then you might need to add is_snapshot = 0

    Reply
  • How to write a script to take full backup once in a week and differential backup all days.. ?

    Reply
  • Syed Mohd Naqi Zaidi
    September 11, 2016 10:53 am

    How to script to take full backup stroage the database ?

    Reply
  • Hi All,

    could you please explain the steps to
    Perform failover testing database clusters

    Regards
    Shiva

    Reply
  • I use the following query:

    with backups as (select database_name,type,case type when ‘D’ then ‘DATABASE’ when ‘I’ then ‘DIFFERENTIAL’ when ‘L’ then ‘LOG’ else ‘OTHER’ end type_desc,recovery_model
    ,(select backupmediafamily.physical_device_name from msdb.dbo.backupmediafamily where backupmediafamily.media_set_id = backupset.media_set_id) physical_device_name
    ,backup_start_date,datediff(ms,backup_start_date,backup_finish_date) backup_duration
    ,backup_size/1024 backup_size,compressed_backup_size/1024 compressed_backup_size
    ,(select count(1) from msdb.dbo.restorehistory where restorehistory.backup_set_id = backupset.backup_set_id) restore_count
    from msdb.dbo.backupset)
    ,latest_backups as (select database_name,type,max(backup_start_date) backup_start_date
    from backups
    group by database_name,type)
    select backups.*
    from latest_backups
    join backups on latest_backups.database_name = backups.database_name and latest_backups.type = backups.type and latest_backups.backup_start_date = backups.backup_start_date
    order by backups.backup_start_date desc,case backups.type when ‘D’ then 1 when ‘I’ then 2 when ‘L’ then 3 else 4 end desc

    Reply
  • Dominic Wirth
    June 30, 2017 6:13 pm

    That’s my statement:

    SELECT DB.[name], BS.[type], MAX(BS.backup_finish_date) AS LastBackup
    FROM sys.databases AS DB
    LEFT JOIN msdb.dbo.backupset AS BS ON DB.[name] = BS.database_name
    GROUP BY DB.[name], BS.[type];

    Reply

Leave a Reply