Is it possible to query the last successful backup date (and perhaps the type of backup) of each database in SQL Sever 2008 R2?
Sql-server – SQL Server 2008 R2 Query Last Successful Database Backup For All DBs
backupsql serversql-server-2008-r2
Related Solutions
The msdb
database keeps history of where, when, who, how big etc. of backups. I have often needed to know where a backup went or who did it or when was the last backup.
This is set to return the last two days of backup history. You can put a specific database in the value, if you leave it blank it will return for all databases. This query works for 2005+. Just tested in 2012.
/*
Find Where DB Backups Went Physical Location
For last two days.
backupset.type
D --> FULL
I --> DIff or incrimental
L --> Log backups
*/
DECLARE @dbname sysname
SET @dbname = ''
SELECT
@@servername [ServerName]
,master.sys.sysdatabases.name [DatabaseName]
,msdb.dbo.backupset.backup_start_date [Backup Date]
,msdb.dbo.backupset.user_name
,datediff(second, msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date) [Duration-seconds]
,msdb.dbo.backupmediafamily.physical_device_name [File Location]
,msdb.dbo.backupset.type
FROM
msdb.dbo.backupmediafamily,
master.sys.sysdatabases
LEFT OUTER JOIN
msdb.dbo.backupset
ON master.sys.sysdatabases.name = msdb.dbo.backupset.database_name
WHERE
msdb.dbo.backupset.type in( 'D', 'I', 'L')
AND msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
and msdb.dbo.backupset.backup_start_date > getdate() - 2
AND master.sys.sysdatabases.name not in ('pubs','northwind', 'tempdb','adventureworks')
AND master.sys.sysdatabases.name like '%' + @dbname + '%'
ORDER BY
master.sys.sysdatabases.name
,msdb.dbo.backupset.backup_start_date
,msdb.dbo.backupset.backup_finish_date
,msdb.dbo.backupmediafamily.physical_device_name
,msdb.dbo.backupset.type
Sadly, backupset
does not contain failed backups, and I don't know of anywhere else in msdb
these may be stored, unless you can rely on sysjobhistory
, which doesn't contain all of time (depending on your retention settings), and which would ignore any backup attempts that were made outside the context of a job, and which - in the case of a job that backs up many databases - would not provide differentiation about which database actually failed, unless it happened to happen early on in the job - this is because the messaging is quite verbose but gets truncated.
If you absolutely know that Job n
only backs up the one database, and that every failure of that job means that the database wasn't backed up (since the job could also fail after the backup succeeded, e.g. trying to shrink or perform other maintenance), then you could use a query like this:
DECLARE @job sysname, @db sysname;
SELECT @job = N'Job 1', @db = N'db_name';
SELECT
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
[Total Time] = CAST((DATEDIFF(SECOND, bs.backup_start_date,bs.backup_finish_date))
AS varchar(30))+ ' secs',
CAST(bs.backup_size/1024/1024 AS decimal(10,2)) AS 'Backup Size(MB)',
h.[message]
FROM msdb.dbo.sysjobhistory AS h
INNER JOIN msdb.dbo.sysjobs AS j
ON h.job_id = j.job_id
AND h.step_id = 0
LEFT OUTER JOIN msdb.dbo.backupset AS bs
ON bs.database_name = @db
AND
ABS(DATEDIFF(SECOND, bs.backup_start_date, CONVERT(DATETIME,convert(char(8),h.run_date)
+ ' ' + STUFF(STUFF(RIGHT('0'+CONVERT(char(6),h.run_time),6),3,0,':'),6,0,':')))) < 5
WHERE j.name = @job
ORDER BY bs.backup_start_date;
Yes, it's really ugly, because sysjobhistory
still, in SQL Server 2014 even, stores run_date
and run_time
as separate integers. I bet whoever made that decision is still on the background of dartboards all over building 35. It also assumes that the backup is the very first step in the job, hence the rather less than scientific date/time comparison to make sure we've properly correlated the right instance of the job to the right instance of the backup. Oh, how I wish I could redesign the schema for backups and jobs.
If you want broader scope outside of the job, you can look for failed backups in the SQL Server error log (if they haven't been cycled away):
EXEC sp_readerrorlog 0, 1, 'BACKUP failed'; -- current
EXEC sp_readerrorlog 1, 1, 'BACKUP failed'; -- .1 (previous)
EXEC sp_readerrorlog 2, 1, 'BACKUP failed'; -- .2 (the one before that)
....
(But I don't know of a nice and easy way to incorporate that output into your existing query.)
You can also correlate "missing" successful backups from the default trace, e.g.
DECLARE @path nvarchar(260);
SELECT
@path = REVERSE(SUBSTRING(REVERSE([path]),
CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;
SELECT dt.DatabaseName, dt.StartTime, bs.backup_start_date, bs.backup_finish_date,
[Status] = CASE WHEN bs.backup_start_date IS NULL
THEN 'Probably failed'
ELSE 'Seems like success'
END
FROM sys.fn_trace_gettable(@path, DEFAULT) AS dt
LEFT OUTER JOIN msdb.dbo.backupset AS bs
ON dt.DatabaseName = bs.database_name
AND ABS(DATEDIFF(SECOND, dt.StartTime, bs.backup_start_date)) < 5
WHERE dt.EventClass = 115 -- backup/restore events
AND UPPER(CONVERT(nvarchar(max),dt.TextData)) LIKE N'BACKUP%DATABASE%'
--AND dt.DatabaseName = N'db_name' -- to filter to a single database
--AND bs.database_name = N'db_name'
ORDER BY dt.StartTime;
Of course this also relies on the data from the default trace cycling away, the database name not having changed, etc. And unfortunately, the default trace doesn't differentiate between successful and failed backups, and the start time will not precisely match the MSDB data, but as long as you're not running backups in a loop, this should be okay for eyeballing. I've tried to incorporate these issues into the query.
Finally, you may want to use a FULL OUTER JOIN
there, in case backupset has longer history than the default trace. This changes the semantics of [Status]
slightly.
You also might want to give this nasty thing a try, though I didn't have much luck with it. I was only able to see the current or most recent status, so that only helped when the job failed the last time it ran, and - like sysjobhistory
- wasn't able to obtain information about any backups that were attempted but not through a job.
Related Question
- MS SQL Server 2008 R2 – Network Backup
- Sql-server – Get last full backup and transaction log backup for each database
- SQL Server 2008 R2 – Resolving Database Backup Issues
- SQL Server Backup – Incremental Backup of SQL Server 2008 R2
- SQL Server 2008 R2 – Fixing Incorrect Last Backup Time in Query
- Sql-server – SQL Server Database backup regressed to historical date
- T-SQL Query for Date of Last Full Backup, Size, and Location
Best Answer
Try this: