March 16, 2014

Verify a success of database backups

10 Comments

One of the most important tasks on a DBA’s to-do list is backing up databases on a regular basis. This is because reliable backups are the most important tool in ensuring data recovery. Therefore, it is important for the DBA to check for database backups and validate that they have been successfully created and saved in a secure location.

SQL Server keeps information about each successful backup in the msdb database. Information about each successful backup operation is stored in the backupset table, and information about each backup physical file in the backupmediafamily table. I wrote the following query, which can be used to check all databases’ backup status for any given SQL Server instance:

USE [msdb]
GO

;WITH [MostRecentBackupStatus_CTE]
AS
(
	SELECT  bsfull.[server_name] ,
			bsfull.[database_name] ,
			bsfull.[backup_finish_date] AS [last_full_backup] ,
			bsdiff.[backup_finish_date] AS [last_diff_backup] ,
			bstlog.[backup_finish_date] AS [last_tran_backup] ,
			DATEDIFF(dd, bsfull.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_full_backup] ,
			DATEDIFF(dd, bsdiff.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_diff_backup] ,
			DATEDIFF(hh, bstlog.[backup_finish_date], CURRENT_TIMESTAMP) AS [hours_since_tranlog_backup] ,
			( SELECT    [physical_device_name]
			  FROM      [msdb]..[backupmediafamily] bmf
			  WHERE     bmf.[media_set_id] = bsfull.[media_set_id]
			) AS [full_backup_location] ,
			( SELECT    [physical_device_name]
			  FROM      [msdb]..[backupmediafamily] bmf
			  WHERE     bmf.[media_set_id] = bsdiff.[media_set_id]
			) AS [diff_backup_location] ,
			( SELECT    [physical_device_name]
			  FROM      [msdb]..[backupmediafamily] bmf
			  WHERE     bmf.[media_set_id] = bstlog.[media_set_id]
			) AS [tlog_backup_location]
	FROM    [msdb]..[backupset] AS bsfull
			LEFT JOIN [msdb]..[backupset] AS bstlog ON bstlog.[database_name] = bsfull.[database_name]
													   AND bstlog.[server_name] = bsfull.[server_name]
													   AND bstlog.[type] = 'L'
													   AND bstlog.[backup_finish_date] = ( (SELECT	MAX([backup_finish_date])
																							FROM	[msdb]..[backupset] b2
																							WHERE	b2.[database_name] = bsfull.[database_name]
																									AND b2.[server_name] = bsfull.[server_name]
																									AND b2.[type] = 'L') )
			LEFT JOIN [msdb]..[backupset] AS bsdiff ON bsdiff.[database_name] = bsfull.[database_name]
													   AND bsdiff.[server_name] = bsfull.[server_name]
													   AND bsdiff.[type] = 'I'
													   AND bsdiff.[backup_finish_date] = ( (SELECT	MAX([backup_finish_date])
																							FROM	[msdb]..[backupset] b2
																							WHERE	b2.[database_name] = bsfull.[database_name]
																									AND b2.[server_name] = bsfull.[server_name]
																									AND b2.[type] = N'I') )
	WHERE   bsfull.[type] = N'D'
			AND bsfull.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
												 FROM   [msdb]..[backupset] b2
												 WHERE  b2.[database_name] = bsfull.[database_name]
														AND b2.[server_name] = bsfull.[server_name]
														AND b2.[type] = N'D') )
			AND EXISTS ( SELECT [name]
						 FROM   [master].[sys].[databases]
						 WHERE  [name] = bsfull.[database_name] )
			AND bsfull.[database_name] <> N'tempdb'
)
SELECT  c.[server_name] ,
        c.[database_name] ,
        d.[recovery_model_desc] ,
        c.[last_full_backup] ,
        c.[last_diff_backup] ,
        c.[last_tran_backup] ,
        c.[days_since_full_backup] ,
        c.[days_since_diff_backup] ,
        c.[hours_since_tranlog_backup] ,
        c.[full_backup_location] ,
        c.[diff_backup_location] ,
        c.[tlog_backup_location]
FROM    [MostRecentBackupStatus_CTE] c
        INNER JOIN [master].[sys].[databases] d ON c.[database_name] = d.[name];
GO

This query returns the following columns:

server_name – Specifies the name of the SQL Server
database_name – Specifies the name of the database.
recovery_model_desc – Specifies the recovery model of the database.
last_full_backup – Specifies the date time of the latest full backup.
last_diff_backup – Specifies the date time of the latest differential backup.
last_tran_backup – Specifies the date time of the latest transactional log backup.
days_since_full_backup – Number of days since the last successful full backup.
days_since_diff_backup – Number of days since the last successful differential backup.
hours_since_tranlog_backup – Number of days since the last successful log backup
full_backup_location – Specifies the physical name of the full backup file.
diff_backup_location – Specifies the physical name of the differential backup file.
tlog_backup_location – Specifies the physical name of the transactional backup file.

I have created a SSIS package that runs this script on a daily basis on each of my Production SQL Servers and then copy that information into our CMS database. This approach helps me to quickly identify the databases where backup is not successful.

Please share your approach, which you use to accomplish this task. It will be interesting to know that.

10 thoughts on “Verify a success of database backups”

David Todd says:

March 17, 2014 at 8:09 pm

Hi Basit,

I noted that sets that are restores from other servers are included in the result as well. Wondering if the outside select needs a where clause

where
c.server_name = @@servername

How would you change it to work for SQL 2000? Yes, I still have some SQL 2000 servers in production.

Regards
David

LikeLike

Reply
- Basit Farooq says:
  
  March 17, 2014 at 9:27 pm
  
  Hi David,
  
  Yes, you can include that in WHERE clause.
  
  We don’t have SQL Server 2000, I think your best bet is to review MSDN docs for msdn of SQL Server 2000.
  
  Regards,
  
  Basit
  
  LikeLike
  
  Reply
davidtodd2010 says:

March 17, 2014 at 10:16 pm

Hi Basit

Thanks for the thoughts.

I’ve removed the CTE and come up with this:
use msdb
go

select
l.server_name
, l.database_name
, l.recovery_model_desc
, l.last_full_backup
, l.last_diff_backup
, l.last_tran_backup
, datediff( dd, l.last_full_backup, current_timestamp ) as days_since_full_backup
, datediff( dd, l.last_diff_backup, current_timestamp ) as days_since_diff_backup
, datediff( hh, l.last_tran_backup, current_timestamp ) as hours_since_tranlog_backup
, (
select
bmf.physical_device_name
from dbo.backupmediafamily bmf
where
bmf.media_set_id = l.Last_Full_Media_ID
) as full_backup_location
, (
select
bmf.physical_device_name
from dbo.backupmediafamily bmf
where
bmf.media_set_id = l.Last_Diff_Media_ID
) as diff_backup_location
, (
select
bmf.physical_device_name
from dbo.backupmediafamily bmf
where
bmf.media_set_id = l.Last_Tlog_Media_ID
) as tlog_backup_location
from (
select
latest.server_name
, latest.database_name
, latest.recovery_model_desc
, max( bsfull.backup_finish_date ) AS last_full_backup
, max( bsdiff.backup_finish_date ) AS last_diff_backup
, max( bstlog.backup_finish_date ) AS last_tran_backup
, max( bsfull.media_set_id ) as Last_Full_Media_ID
, max( bsdiff.media_set_id ) as Last_Diff_Media_ID
, max( bstlog.media_set_id ) as Last_Tlog_Media_ID
from (
select
si.server_name
, si.database_name
, d.recovery_model_desc
, si.type
, max( si.backup_finish_date ) as last_backup_finish_date
from msdb.dbo.backupset si
inner join master.sys.databases d
on d.name = si.database_name
where
1 = 1
and si.database_name N’tempdb’
and si.server_name = @@servername
group by
si.server_name
, si.database_name
, d.recovery_model_desc
, si.type
) latest
left outer join msdb.dbo.backupset bsfull
on bsfull.server_name = latest.server_name
and bsfull.database_name = latest.database_name
and bsfull.type = latest.type
and bsfull.backup_finish_date = latest.last_backup_finish_date
and bsfull.type = N’D’
and bsfull.server_name = @@servername
left outer join msdb.dbo.backupset bstlog
on bstlog.server_name = latest.server_name
and bstlog.database_name = latest.database_name
and bstlog.type = latest.type
and bstlog.backup_finish_date = latest.last_backup_finish_date
and bstlog.type = N’L’
and bstlog.server_name = @@servername
left outer join msdb.dbo.backupset bsdiff
on bsdiff.server_name = latest.server_name
and bsdiff.database_name = latest.database_name
and bsdiff.type = latest.type
and bsdiff.backup_finish_date = latest.last_backup_finish_date
and bsdiff.type = N’I’
and bsdiff.server_name = @@servername
where
1 = 1
and latest.database_name N’tempdb’
and latest.server_name = @@servername
group by
latest.server_name
, latest.database_name
, latest.recovery_model_desc
) l
order by
l.server_name
, l.database_name
;

The changes from the above to run on SQL 2000 are:
change master.dbo.sysdatabases
Comment out all lines with recovery_model_desc

In SQL 2000 the recovery model was some horrible structure of flags from memory. But no apparent changes the backup tables.

Regards
David

LikeLike

Reply
Jon says:

March 18, 2014 at 8:43 am

Excellent post, Basit!
Up until now I use a job to check if the transaction log backups are performed. If one fails the job sends me an e-mail (database mail). Here is the code:

————————————————————————————————————–
DECLARE @recordcount int

set @recordcount = (select COUNT(*) from sysdatabases
where convert(varchar(50),DatabasePropertyEx(name,’Recovery’))= ‘FULL’ and name not in (select database_name from msdb..backupset where type = ‘L’ and backup_start_date > dateadd(day,-1,getdate())));

if @recordcount > 0
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ”,
@recipients = ‘dba@mycompany.nl’,
@body = ‘Transaction log backups have not been made’,
@subject = ‘Transaction log backup check’;
————————————————————————————————————–

The profile name is actually the name of the instance, hence I know where to check why transaction log backups have failed.
The job runs every four hours. It’s not perfect but I get the job done. 😉

Regards,
Jon

LikeLike

Reply
- Basit Farooq says:
  
  March 18, 2014 at 12:22 pm
  
  Thanks for comments. Glad that you liked it.
  
  LikeLike
  
  Reply
Lakshmi Narayna says:

March 25, 2014 at 12:45 pm

thank you very much for wonderful script.

by using above script , made some changes , insted of SSIS Notification i created job for mail notification .
thank you very much for innovative thought.

USE [msdb]
GO

;WITH [MostRecentBackupStatus_CTE]
AS
(
SELECT bsfull.[server_name] ,
bsfull.[database_name] ,
bsfull.[backup_finish_date] AS [last_full_backup] ,
bsdiff.[backup_finish_date] AS [last_diff_backup] ,
bstlog.[backup_finish_date] AS [last_tran_backup] ,
DATEDIFF(dd, bsfull.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_full_backup] ,
DATEDIFF(dd, bsdiff.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_diff_backup] ,
DATEDIFF(hh, bstlog.[backup_finish_date], CURRENT_TIMESTAMP) AS [hours_since_tranlog_backup] ,
( SELECT [physical_device_name]
FROM [msdb]..[backupmediafamily] bmf
WHERE bmf.[media_set_id] = bsfull.[media_set_id]
) AS [full_backup_location] ,
( SELECT [physical_device_name]
FROM [msdb]..[backupmediafamily] bmf
WHERE bmf.[media_set_id] = bsdiff.[media_set_id]
) AS [diff_backup_location] ,
( SELECT [physical_device_name]
FROM [msdb]..[backupmediafamily] bmf
WHERE bmf.[media_set_id] = bstlog.[media_set_id]
) AS [tlog_backup_location]
FROM [msdb]..[backupset] AS bsfull
LEFT JOIN [msdb]..[backupset] AS bstlog ON bstlog.[database_name] = bsfull.[database_name]
AND bstlog.[server_name] = bsfull.[server_name]
AND bstlog.[type] = ‘L’
AND bstlog.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
FROM [msdb]..[backupset] b2
WHERE b2.[database_name] = bsfull.[database_name]
AND b2.[server_name] = bsfull.[server_name]
AND b2.[type] = ‘L’) )
LEFT JOIN [msdb]..[backupset] AS bsdiff ON bsdiff.[database_name] = bsfull.[database_name]
AND bsdiff.[server_name] = bsfull.[server_name]
AND bsdiff.[type] = ‘I’
AND bsdiff.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
FROM [msdb]..[backupset] b2
WHERE b2.[database_name] = bsfull.[database_name]
AND b2.[server_name] = bsfull.[server_name]
AND b2.[type] = N’I’) )
WHERE bsfull.[type] = N’D’
AND bsfull.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
FROM [msdb]..[backupset] b2
WHERE b2.[database_name] = bsfull.[database_name]
AND b2.[server_name] = bsfull.[server_name]
AND b2.[type] = N’D’) )
AND EXISTS ( SELECT [name]
FROM [master].[sys].[databases]
WHERE [name] = bsfull.[database_name] )
AND bsfull.[database_name] N’tempdb’
)
SELECT c.[server_name] ,
c.[database_name] ,
d.[recovery_model_desc] ,
c.[last_full_backup] ,
c.[last_diff_backup] ,
c.[last_tran_backup] ,
c.[days_since_full_backup] ,
c.[days_since_diff_backup] ,
c.[hours_since_tranlog_backup] ,
c.[full_backup_location] ,
c.[diff_backup_location] ,
c.[tlog_backup_location]
into #TEMP
FROM [MostRecentBackupStatus_CTE] c
INNER JOIN [master].[sys].[databases] d ON c.[database_name] = d.[name];
GO
DECLARE @tableHTML NVARCHAR(MAX) ;
declare @subject varchar(100)
set @subject = ‘MostRecentBackupStatus_CTE ‘
set @subject = @subject + convert(varchar(11),GETDATE()-1)
SET @tableHTML =
N” +
N” +
N’
server_name
database_name
recovery_model_desc
last_full_backup
last_diff_backup
last_tran_backup
days_since_full_backup
days_since_diff_backup
hours_since_tranlog_backup
full_backup_location
diff_backup_location
tlog_backup_location
‘+
CAST((SELECT td = cast(isnull(server_name,0) AS VARCHAR(20)), ” ,
td = cast(isnull(database_name,0) AS VARCHAR(20)), ” ,
td = cast(isnull(recovery_model_desc,0) AS VARCHAR(20)), ” ,
td = cast(isnull(last_full_backup,0) AS VARCHAR(20)), ” ,
td = cast(isnull(last_diff_backup,0) AS VARCHAR(20)), ” ,
td = cast(isnull(last_tran_backup,0) AS VARCHAR(20)), ” ,
td = cast(isnull(days_since_full_backup,0) AS VARCHAR(20)), ” ,
td = cast(isnull(days_since_diff_backup,0) AS VARCHAR(20)), ”,
td = cast(isnull(hours_since_tranlog_backup,0) AS VARCHAR(20)), ” ,
td = cast(isnull(full_backup_location,0) AS VARCHAR(20)), ” ,
td = cast(isnull(diff_backup_location,0) AS VARCHAR(20)), ” ,
td = cast(isnull(tlog_backup_location,0) AS VARCHAR(20)), ”
From #TEMP
FOR XML PATH(‘tr’), TYPE) AS NVARCHAR(MAX)) + N’
‘

If Exists (select * from #TEMP)
EXEC msdb.dbo.sp_send_dbmail
@recipients=’xyz@mail.com’,
@subject = @subject,
@profile_name = ‘dbmail’,
@body = @tableHTML,
@body_format = ‘HTML’

DROP TABLE #TEMP

LikeLike

Reply
Tips For Using Data Recovery Software says:

March 29, 2014 at 4:11 am

Hello there! This is my first visit to your blog! We are a group
of volunteers and starting a new project in a community in the same niche.

Your blog provided us beneficial information to work on. You have done a outstanding job!

LikeLike

Reply
Bruno says:

August 6, 2014 at 3:35 pm

Excellent script! I change it a little bit so it can work on SQL Server 2000. I also added columns for each backup size.

USE [msdb]
GO

SELECT c.[server_name] ,
c.[database_name] ,
databasepropertyex(c.[database_name], N’RECOVERY’) as recovery_model_desc,
c.[last_full_backup] ,
c.[last_diff_backup] ,
c.[last_tran_backup] ,
c.[days_since_full_backup] ,
c.[days_since_diff_backup] ,
c.[hours_since_tranlog_backup] ,
c.[full_backup_location] ,
c.[diff_backup_location] ,
c.[tlog_backup_location],
c.full_backup_size,
c.diff_backup_size,
c.tranlog_backup_size
FROM (SELECT bsfull.[server_name] ,
bsfull.[database_name] ,
bsfull.[backup_finish_date] AS [last_full_backup] ,
bsdiff.[backup_finish_date] AS [last_diff_backup] ,
bstlog.[backup_finish_date] AS [last_tran_backup] ,
DATEDIFF(dd, bsfull.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_full_backup] ,
DATEDIFF(dd, bsdiff.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_diff_backup] ,
DATEDIFF(hh, bstlog.[backup_finish_date], CURRENT_TIMESTAMP) AS [hours_since_tranlog_backup] ,
( SELECT [physical_device_name]
FROM [msdb]..[backupmediafamily] bmf
WHERE bmf.[media_set_id] = bsfull.[media_set_id]
) AS [full_backup_location] ,
( SELECT [physical_device_name]
FROM [msdb]..[backupmediafamily] bmf
WHERE bmf.[media_set_id] = bsdiff.[media_set_id]
) AS [diff_backup_location] ,
( SELECT [physical_device_name]
FROM [msdb]..[backupmediafamily] bmf
WHERE bmf.[media_set_id] = bstlog.[media_set_id]
) AS [tlog_backup_location],
bsfull.[backup_size] AS [full_backup_size],
bsdiff.[backup_size] AS [diff_backup_size],
bstlog.[backup_size] AS [tranlog_backup_size]
FROM [msdb]..[backupset] AS bsfull
LEFT JOIN [msdb]..[backupset] AS bstlog ON bstlog.[database_name] = bsfull.[database_name]
AND bstlog.[server_name] = bsfull.[server_name]
AND bstlog.[type] = ‘L’
AND bstlog.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
FROM [msdb]..[backupset] b2
WHERE b2.[database_name] = bsfull.[database_name]
AND b2.[server_name] = bsfull.[server_name]
AND b2.[type] = ‘L’) )
LEFT JOIN [msdb]..[backupset] AS bsdiff ON bsdiff.[database_name] = bsfull.[database_name]
AND bsdiff.[server_name] = bsfull.[server_name]
AND bsdiff.[type] = ‘I’
AND bsdiff.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
FROM [msdb]..[backupset] b2
WHERE b2.[database_name] = bsfull.[database_name]
AND b2.[server_name] = bsfull.[server_name]
AND b2.[type] = N’I’) )
WHERE bsfull.[type] = N’D’
AND bsfull.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
FROM [msdb]..[backupset] b2
WHERE b2.[database_name] = bsfull.[database_name]
AND b2.[server_name] = bsfull.[server_name]
AND b2.[type] = N’D’) )
AND EXISTS ( SELECT [name]
FROM [master].[dbo].[sysdatabases]
WHERE [name] = bsfull.[database_name] )
AND bsfull.[database_name] N’tempdb’) c
INNER JOIN [master].[dbo].[sysdatabases] d ON c.[database_name] = d.[name];
GO

LikeLike

Reply
Mahi says:

February 25, 2015 at 12:56 pm

is any of the field in this backupset database appendable?

LikeLike

Reply
Chris Huffman says:

June 12, 2015 at 6:57 pm

Your script does not work for backups that are split into multiple files. Here is a script that I have wrote that returns the backupmediafamily in a sub select query and returns multiple files into a comma seperated array.

Select bs.media_set_id,
case when bs.type = ‘D’ then ‘Full’
when bs.type = ‘I’ then ‘Diff’
when bs.type = ‘L’ then ‘Log’
when bs.type = ‘F’ then ‘filegroup’
when bs.type = ‘G’ then’ Differential file’
when bs.type = ‘P’ then ‘Partial’
when bs.type = ‘Q’ then ‘Differential partial’
END as backuptype,
bs.backup_start_date,bs.backup_finish_date,compatibility_level,software_name, database_name, convert(int,compressed_backup_size/backup_size*100) AS CompressionRatio,
backup_size/1024/1024 as backup_sizeMB, compressed_backup_size/1024/1024 as compressed_backup_sizeMB
,bmf.physical_block_size, user_name,first_lsn,last_lsn,recovery_model,first_recovery_fork_guid,last_recovery_fork_guid,
SUBSTRING((SELECT ‘,’ + bmf2.physical_device_name
FROM msdb.dbo.backupmediafamily bmf2
WHERE bmf2.media_set_id=bs.media_set_id
FOR XML PATH(”)
), 2, 1000000) as BackupFiles,
last_family_number
FROM msdb.dbo.backupset bs
Inner Join msdb.dbo.backupmediaset bms
ON bms.media_set_id = bs.media_set_id
Inner Join msdb.dbo.backupmediafamily bmf
ON bmf.media_set_id = bs.media_set_id

LikeLike

Reply