SQL SERVER – Finding Last Backup Time for All Database

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

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

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

Reference: Pinal Dave (http://blog.SQLAuthority.com)

39 thoughts on “SQL SERVER – Finding Last Backup Time for All Database

  1. – for single db back up history i use

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

    — Get Backup History for required database

    SELECT TOP ( 30 )
    s.database_name,
    m.physical_device_name,
    cast(CAST(s.backup_size / 1000000 AS INT) as varchar(14))
    + ‘ ‘ + ‘MB’ as bkSize,
    CAST(DATEDIFF(second, s.backup_start_date,
    s.backup_finish_date) AS VARCHAR(4)) + ‘ ‘
    + ‘Seconds’ TimeTaken,
    s.backup_start_date,
    CAST(s.first_lsn AS varchar(50)) AS first_lsn,
    CAST(s.last_lsn AS varchar(50)) AS last_lsn,
    CASE s.[type]
    WHEN ‘D’ THEN ‘Full’
    WHEN ‘I’ THEN ‘Differential’
    WHEN ‘L’ THEN ‘Transaction Log’
    END as BackupType,
    s.server_name,
    s.recovery_model
    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

    Like

    • Hi Asim Bhai,
      Is there any to take backup of database for certain date and time.
      for example i want to get database backup for 12-12-2012 12:15:00.
      if you could suggest some way to do it,
      Thanks

      Like

    • i have a question here.
      how to write select statement to get what backup is taken to which file on which database?
      please answer to this as soon as possible!

      Like

  2. I use the following query:-

    SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(Backup_finish_date)))), ‘NEVER’) as DaysSinceLastBackup,
    ISNULL(Convert(char(10), MAX(backup_finish_date), 101), ‘NEVER’) as LastBackupDate
    FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A
    ON A.database_name = B.name AND A.type = ‘D’ GROUP BY B.Name ORDER BY B.name

    Like

  3. – Complete info about all databases

    SELECT
    database_id,
    convert(varchar(25), DB.name) as dbName,
    convert(varchar(10), Databasepropertyex(name, ‘status’)) as [Status],
    state_desc,
    (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘rows’) AS DataFiles,
    (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘rows’) AS [Data MB],
    (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘log’) AS LogFiles,
    (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘log’) AS [Log MB],
    user_access_desc AS [User access],
    recovery_model_desc as [Recovery model],
    CASE compatibility_level
    WHEN 60 THEN ’60 (SQL Server 6.0)’
    WHEN 65 THEN ’65 (SQL Server 6.5)’
    WHEN 70 THEN ’70 (SQL Server 7.0)’
    WHEN 80 THEN ’80 (SQL Server 2000)’
    WHEN 90 THEN ’90 (SQL Server 2005)’
    END AS [compatibility level],
    CONVERT(VARCHAR(20), create_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), create_date, 108) as [Creation date],

    — last backup
    ISNULL((SELECT TOP 1
    CASE TYPE WHEN ‘D’ THEN ‘Full’ WHEN ‘I’ THEN ‘Differential’ WHEN ‘L’ THEN ‘Transaction log’ END + ‘ – ‘ +
    ltrim(ISNULL(STR(ABS(DATEDIFF(day, GetDate(),Backup_finish_date))) + ‘ days ago’, ‘NEVER’)) + ‘ – ‘ +
    CONVERT(VARCHAR(20), backup_start_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), backup_start_date, 108) + ‘ – ‘ +
    CONVERT(VARCHAR(20), backup_finish_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), backup_finish_date, 108) +
    ‘ (‘ + CAST(DATEDIFF(second, BK.backup_start_date,
    BK.backup_finish_date) AS VARCHAR(4)) + ‘ ‘
    + ‘seconds)’
    FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),’-‘) AS [Last backup],

    CASE WHEN is_fulltext_enabled = 1 THEN ‘Fulltext enabled’ ELSE ” END AS [fulltext],
    CASE WHEN is_auto_close_on = 1 THEN ‘autoclose’ ELSE ” END AS [autoclose],
    page_verify_option_desc AS [page verify option],
    CASE WHEN is_read_only = 1 THEN ‘read only’ ELSE ” END AS [read only],
    CASE WHEN is_auto_shrink_on = 1 THEN ‘autoshrink’ ELSE ” END AS [autoshrink],
    CASE WHEN is_auto_create_stats_on = 1 THEN ‘auto create statistics’ ELSE ” END AS [auto create statistics],
    CASE WHEN is_auto_update_stats_on = 1 THEN ‘auto update statistics’ ELSE ” END AS [auto update statistics],
    CASE WHEN is_in_standby = 1 THEN ‘standby’ ELSE ” END AS [standby],
    CASE WHEN is_cleanly_shutdown = 1 THEN ‘cleanly shutdown’ ELSE ” END AS [cleanly shutdown]
    FROM sys.databases DB
    ORDER BY dbName, [Last backup] DESC, NAME

    Cheers!

    Like

  4. Pingback: SQL SERVER – Get Database Backup History for a Single Database Journey to SQL Authority with Pinal Dave

  5. Hi pinal,

    The script is much more useful to me also.

    I have a question regarding this.

    How can we get the failed backup operation list.
    In our office we use 5 databases. All the databases are sceduled for backup on every day night, this is scheduled via the job.

    if any one database get failed while executing job, the job shows in error. How can i know which data base backup get failed.

    Is there any history like this. ?

    Like

  6. Pingback: SQL SERVER – Get All the Information of Database using sys.databases Journey to SQL Authority with Pinal Dave

  7. Hi Pinal,

    I really need a big favor from you. I need to set up a process

    which will take a backup everyday and delete the previous

    days backup. Can you please help me with it? I would really

    appreciate your help.

    Thanks,
    Joben

    Like

    • Schedule through Maintenance–>management–>new management plan in sql server create one schedules
      1) for creating a backup of the day
      2) for deleting the previous days back up

      Like

  8. I have a backup “full” running each day to disk but the database last backup up always remains at none.

    Does this mean the instance is not able to carry out hot backups?

    Like

  9. Hi Pinal,

    Finally your script is working fine 2005 onwards

    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

    SQL Server 2000 and 7.0

    use master

    go

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

    Thanks & Regards
    Jayant Dass

    Like

  10. it’s for sql server 6.5

    SELECT sdb.Name AS DatabaseName,
    COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish), 101),’-‘) AS LastBackUpTime
    FROM sysdatabases sdb
    LEFT OUTER JOIN msdb.dbo.sysbackuphistory bus ON bus.database_name = sdb.name
    GROUP BY sdb.Name

    Thanks & Regards
    Jayant Dass

    Like

  11. Hi pinal,

    As your all posts are usefull. I need sql code to find out database name with all their previous backup datetime
    I am waiting for your reply.

    Thanks and regards,
    suhas

    Like

  12. Hi Pinnal,

    Please do the need full this is P1.

    I need a single script for below output for all databases.

    Database name,
    Database Size,
    Occurrence of the log backups (i.e. every 15, 30, 60 etc).

    Regards.

    prashanth Kumar

    Like

  13. no it gives one date only to all columns as below

    for script as :
    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

    master 10/22/2012
    tempdb –
    model 10/22/2012
    msdb 10/22/2012
    Actional 10/22/2012
    ScConfiguration 10/22/2012
    ScReporting 10/22/2012
    ScTrans 10/22/2012
    Vault 10/22/2012
    AuditTrail 10/22/2012
    BitKooDirectory 10/22/2012
    Keystone2 10/22/2012
    keystone2_audit 10/22/2012
    Keystone2Log 10/22/2012
    BitkooDeployer 10/22/2012
    TraceLogs 10/22/2012
    SmcWmArchive 10/22/2012
    SmcWmIs 10/22/2012
    SmcWmMws 10/22/2012
    SmcWmOptimize 10/22/2012
    SmcWmProcess 10/22/2012
    BIxPress 10/22/2012
    ProblemResolution 10/22/2012

    Like

  14. Pingback: SQL SERVER – Beginning New Weekly Series – Memory Lane – #001 « SQL Server Journey with SQL Authority

  15. Thank you All. Following SQL start,end and duration for all databases on your server

    SELECT sdb.Name AS DatabaseName,
    COALESCE(CONVERT(VARCHAR(20), MAX(bus.backup_start_date), 100),’-‘) AS StartTime,
    COALESCE(CONVERT(VARCHAR(20), MAX(bus.backup_finish_date), 100),’-‘) AS EndTime,
    isnull(datediff(mi,max(bus.backup_start_date),max(bus.backup_finish_date)),0) ‘BackupDuration’
    FROM sys.sysdatabases sdb
    LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
    GROUP BY sdb.Name

    Like

  16. Here is my code to check for the last database backup. It gives the database recovery model and the last time of a full and log backup.

    I hope it help someone

    SELECT a.name AS [Database Name],a.recovery_model_Desc AS [Recovery Model],
    (select MAX(b.backup_finish_date) from msdb..backupset b where b.type = ‘D’ and a.name=b.database_name) AS [Full Backup],
    (select MAX(b.backup_finish_date) from msdb..backupset b where b.type = ‘L’ and a.name=b.database_name) AS [Log Backup]
    FROM master.sys.databases a

    Like

  17. you can use this for SQL 2000

    SELECT a.name AS [Database Name],databasepropertyex(a.name, ‘Recovery’) as RecoveryModel,
    (select MAX(b.backup_finish_date) from msdb..backupset b where b.type = ‘D’ and a.name=b.database_name) AS [Full Backup],
    (select MAX(b.backup_finish_date) from msdb..backupset b where b.type = ‘L’ and a.name=b.database_name) AS [Log Backup]
    FROM master.dbo.sysdatabases a

    Like

  18. Error received on running the topmost(first) script in SQL Server 2005 as :

    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘sys.sysdatabases’.

    Please help me on this….

    Thank you.

    Like

  19. Hello,

    I need to create a Failed backups report . We have multiple servers and we need to have a report which helps us identify most recent failed backups on all the servers and the resective database names. I have created linked servers for all these multiple servers on single SQL server system. But I need a script which helps me get all tha failed backup jobs on all these servers. Can you please provide me with a script for this.

    Like

    • please check this Dharurkar
      select sdb.name as DateBaseName , COALESCE(convert(varchar(10), bus.backup_finish_date,101),’Till now this is datebases or not bacup’ ) as LastBacupdate into #DatabasesnotBackup from sys.sysdatabases sdb
      left join msdb.dbo.backupset bus
      on sdb.name=bus.database_name
      where bus.backup_finish_date is null

      select * from #DatabasesnotBackup
      drop table #DatabasesnotBackup

      Like

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

    Like

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

    Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

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

    Like

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

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