4

Is it possible to query the last successful backup date (and perhaps the type of backup) of each database in SQL Sever 2008 R2?

3 Answers 3

6

Try this:

SELECT Database_Name,
CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Last_Backup, 
DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last
FROM MSDB.dbo.BackupSet
WHERE Type = 'd'
GROUP BY Database_Name
6
  • 1
    Add an INNER JOIN to MASTER.dbo.sysdatabases (join on columns msdb.dbo.backupset Database_name, master.dbo.sysdatabases name) and you can omit databases backed up but since dropped.
    – jl01
    Jan 20, 2012 at 22:02
  • Would this include databases that have never been backed up? Jan 23, 2012 at 20:01
  • @jl01: Good catch! Jan 23, 2012 at 20:08
  • @KyleBrandt: This would only include databases that are part of the backup set. See: sqlmag.com/article/tsql3/using-msdb-backup-tables Jan 23, 2012 at 20:09
  • 1
    @OliverAsmus: Okay, so I think I can include those by just doing a RIGHT OUTER JOIN to sysdatabases, group on master.dbo.sysdatabases.name, and adding a OR TYPE IS NULL to the WHERE clauses Jan 23, 2012 at 20:12
7

Yes, as long as the history data is still in msdb database. The following block of code will get your the last 100 backups including full, differential and log backups.

SELECT TOP 100 
    s.database_name, 
    m.physical_device_name,
    CAST(DATEDIFF(second, s.backup_start_date,
    s.backup_finish_date) AS VARCHAR(100)) + ' ' + 'Seconds' TimeTaken,
    s.backup_start_date,
    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() -- Remove this line for all the database
    ORDER BY backup_start_date DESC, backup_finish_date
    GO

1

If you want to use Powershell this is what I have. If you have SQL 2008 SMO DDLs on your computer use line 1. If you have SQL 2005 SMO delete line 1, remove the # and use line 2. If all else fails delete both lines 1 and 2, remove the # and use line 3. Replace Your_Server_Name_Here with the name of the server you're checking.


Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop
# Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop
# [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$srvObject = = new-object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList 'Your_Server_Name_Here'
$srvObject.Databases | Select Name, LastBackupDate, LastDifferentialBackupDate, LastLogBackupDate

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.