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.

Solarwinds

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)

Solarwinds
, , ,
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

  • aasim abdullah
    November 4, 2010 2:48 pm

    — 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

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

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

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

    Reply
  • — 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!

    Reply
    • Good script.
      Could you please add the below columns to the script and re-send.
      ========
      backupsize(MB),
      Physical DeviceName,
      Backedup By Username

      Reply
  • Rajesh.Dharmakkan
    November 11, 2010 5:23 pm

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

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

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

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

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

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

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

    Reply
  • The above scripts are really very useful.
    Thank You for your help.
    Nilesh

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

    Reply
  • Can some one help me, how to get the db backup info for all instance in a single domain.

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

    Reply
  • Hi Pinal,
    Can you please explain about system databases?

    Reply
  • Sathiskumar Selvaraju
    November 13, 2012 12:51 am

    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

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

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

    Reply
  • Sahul Hameed
    March 7, 2013 7:42 pm

    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.

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

    Reply
  • Rhicha Dharurkar
    September 5, 2013 9:04 pm

    hi pinal,

    how to query for list of databases which are never backed up, and save that result into new table..?

    please help

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

      Reply

Leave a Reply

Menu