Verify a success of database backups

01One 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

  1. 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

    Like

  2. 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

    Like

  3. 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

    Like

  4. 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

    Like

  5. 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

    Like

  6. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s