Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

up vote 1 down vote accepted

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
share|improve this answer
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

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

share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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