When did I last backup my databases with MSSQL

  • You backup your databases? Say yes, if not, you should not be in this business, your server will crash, and you will loose your job...
  • Yes, you backup your databases, but when was that? Do you keep track of all your backups?
  • Microsoft SQL Server does keep track of your backups in the msdb..backupset table.
  • You just need to query it to know when the backup_finish_date column to know when the last backups where done.
  • This is a very nice and easy way of auditing your backups.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Last backups SQL Server 2005 - SQL Server 2008

  1. USE master;
  2. go
  3. SELECT CAST(db.name AS VARCHAR(30)) AS 'Database',
  4.        isnull(CONVERT(CHAR(12), MAX(backup_finish_date), 106), 'No backup') AS 'Last backup',
  5.        isnull(STR(ABS(DATEDIFF(DAY, CURRENT_TIMESTAMP, MAX(backup_finish_date)))), 'Never') AS 'Days ago'
  6. FROM sys.sysdatabases db
  7. LEFT outer join msdb..backupset bk ON bk.database_name = db.name
  8.                and bk.type =  'd'
  9. GROUP BY db.name
  10. ORDER BY db.name
  11. go
Database                       Last backup  Days ago
------------------------------ ------------ ----------
AdventureWorks                 13 Aug 2007         146
AdventureWorksDW               No backup    Never
AdventureWorksLT               No backup    Never
master                         22 Aug 2007         137
model                          No backup    Never
msdb                           22 Aug 2007         137
ReportServer                   No backup    Never
ReportServerTempDB             No backup    Never
sql911                         06 Jan 2008           0
tempdb                         No backup    Never

(10 row(s) affected)
  • The sys.sysdatabases gives you all the databases.
  • The left outer join on msdb..backupset will give you all the databases even if there are no entry in the msdb..backupset.
  • The max(backup_finish_date) gives you the last backup date for that one database.

Last backups SQL Server 2000

  1. USE master;
  2. go
  3. SELECT CAST(db.name AS VARCHAR(30)) AS 'Database',
  4.        isnull(CONVERT(CHAR(12), MAX(backup_finish_date), 106), 'No backup') AS 'Last backup',
  5.        isnull(STR(ABS(DATEDIFF(DAY, CURRENT_TIMESTAMP, MAX(backup_finish_date)))), 'Never') AS 'Days ago'
  6. FROM sysdatabases db
  7. LEFT outer join msdb..backupset bk ON bk.database_name = db.name
  8.                and bk.type =  'd'
  9. GROUP BY db.name
  10. ORDER BY db.name
  11. go
Database                       Last backup  Days ago
------------------------------ ------------ ----------
AdventureWorks                 13 Aug 2007         146
AdventureWorksDW               No backup    Never
AdventureWorksLT               No backup    Never
master                         22 Aug 2007         137
model                          No backup    Never
msdb                           22 Aug 2007         137
ReportServer                   No backup    Never
ReportServerTempDB             No backup    Never
sql911                         06 Jan 2008           0
tempdb                         No backup    Never

(10 row(s) affected)
  • Note that the SQL Server 2000 version is almost identical to the SQL Server 2005/2008.
  • The only difference is that up [including] to SQL Server 2000, the database names were stored in sysdatabases and since SQL Server 2005 they became stored in sys.sysdatabases.