SQL Server database backup times

It seems to be a common issue for those in charge of databases. How to check when a database was last backed up.

Well, you could always check the location of the backup files (if using SQL Agent jobs to create flat-file backups) or check the recovery points available in your backup software (such as Microsoft’s DPM). You can also check the properties of the database itself in SQL Server Management Studio. This is fine if you only have a couple of databases in your instance, however in large instances hosting many databases, its often time consuming and tedious to do it this way.

Using the script below you can get the last date and time a log and full backup was taken (as well as recovery model information) for each database in a SQL Server instance:

DECLARE @dbname VARCHAR(200)
DECLARE @lbackup VARCHAR(50)
DECLARE @fbackup VARCHAR(50)
DECLARE @recmodl VARCHAR(20)
DECLARE @bckuptbl TABLE(DBName VARCHAR(200), logbackup VARCHAR(50), fullbackup VARCHAR(50), recovmod VARCHAR(20))
DECLARE c CURSOR FOR SELECT name FROM master.sys.sysdatabases
OPEN c

FETCH NEXT FROM c INTO @dbname
WHILE @@FETCH_STATUS=0 BEGIN
      SET @fbackup = (SELECT CONVERT(VARCHAR(50), MAX(backup_finish_date)) FROM msdb.dbo.backupset WHERE database_name = @dbname AND type = 'D')
      SET @lbackup = (SELECT CONVERT(VARCHAR(50), MAX(backup_finish_date)) FROM msdb.dbo.backupset WHERE database_name = @dbname AND type = 'L')
      SET @recmodl = (SELECT recovery_model_desc from master.sys.databases WHERE name = @dbname)
      IF @fbackup IS NULL
            BEGIN
                  SET @fbackup = 'Never'
            END
      IF @lbackup IS NULL
            BEGIN
                  SET @lbackup = 'Never'
            END
      INSERT INTO @bckuptbl (DBName, logbackup, fullbackup, recovmod) VALUES (@dbname, @lbackup, @fbackup, @recmodl)
      FETCH NEXT FROM c INTO @dbname
END

CLOSE c
DEALLOCATE c
SELECT DBName, fullbackup, logbackup, recovmod FROM @bckuptbl ORDER BY DBName ASC

When run you should expect to see something like the output below:
sql-results

Share and Enjoy:
  • Digg
  • StumbleUpon
  • Technorati
  • del.icio.us
  • Twitter
  • blogmarks
  • HackerNews
  • Tumblr
  • Posterous
  • email
Bookmark the permalink. Follow any comments here with the RSS feed for this post.
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Your email address will not be published. Required fields are marked *

This blog is kept spam free by WP-SpamFree.