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?

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
  • 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 '12 at 22:02
  • Would this include databases that have never been backed up? – Kyle Brandt Jan 23 '12 at 20:01
  • @jl01: Good catch! – OliverAsmus Jan 23 '12 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 – OliverAsmus Jan 23 '12 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 – Kyle Brandt Jan 23 '12 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, privacy policy and cookie policy

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