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
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
-
1Add 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.– jl01Jan 20, 2012 at 22:02
-
-
-
@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
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
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