Stay Backed Up by Monitoring Your Backup Process

  • Comments posted to this topic are about the item Stay Backed Up by Monitoring Your Backup Process

  • I love the idea of the central monitoring server using linked servers....I use one too. Makes my life so easy.

    I use a similar process but its a little less complicated. I just send an email if a backup has not been completed for more than 24 hours. justs sends a generic email if it has not. This script gets scheduled with the agent to run once a day. looks like this; (modifed to work in your enviroment)

    declare @server_name nvarchar(250)

    declare db_crsr_DBS cursor for

    select server_name from [Monitordb].[dbo].[Remote_Databases]

    open db_crsr_DBS

    fetch next from db_crsr_DBS into @server_name

    while @@fetch_status = 0

    begin

    DECLARE @cmd nvarchar(2000)

    set @cmd = '

    Select '''+@server_name+''' as ServerName, a.name as [DB_Name], backup_type, Backup_Date, getdate() as Date_Inserted

    from ['+@server_name+'].[master].[dbo].[sysdatabases] a

    left join

    (select database_name, Backup_Type = Case type when ''D'' then ''Database''

    When ''I'' then ''Database Differential''

    When ''L'' then ''Log''

    When ''F'' then ''File or Filegroup''

    Else ''Error'' End,

    max(backup_finish_date) backup_date

    from ['+@server_name+'].[msdb].[dbo].[backupset] where backup_finish_date <= getdate()

    group by database_name,Type ) B

    on a.name=b.database_name

    where a.name != ''tempdb'' and Backup_Date < dateADD(hh,-24,getdate()) and a.status not between 500 and 600

    or Backup_date is null and a.name != ''tempdb'' and a.status not between 500 and 600'

    create table #just_temporary (ServerName nvarchar(150), [DB_Name] nvarchar(150), backup_type nvarchar(25),

    Backup_Date datetime, Date_Inserted datetime)

    insert into #just_temporary

    exec (@cmd)

    if exists (select top 1 * from #just_temporary)

    begin

    declare @mailcmd nvarchar(2000)

    SET @mailcmd = 'Select convert(nvarchar(30),a.name) as [DB_Name], backup_type, Backup_Date

    from ['+@server_name+'].[master].[dbo].[sysdatabases] a

    left join

    (select database_name, Backup_Type = Case type when ''D'' then ''Database''

    When ''I'' then ''Database Differential''

    When ''L'' then ''Log''

    When ''F'' then ''File or Filegroup''

    Else ''Error'' End,

    max(backup_finish_date) backup_date

    from ['+@server_name+'].[msdb].[dbo].[backupset] where backup_finish_date <= getdate()

    group by database_name,Type ) B

    on a.name=b.database_name

    where a.name != ''tempdb'' and Backup_Date < dateADD(hh,-24,getdate()) and a.status not between 500 and 600

    or Backup_date is null and a.name != ''tempdb'' and a.status not between 500 and 600'

    declare @mybody nvarchar (150)

    set @mybody = 'The following databases require a backup on instance '+@server_name+';

    '

    declare @mysubject nvarchar(200)

    set @mysubject = 'Missing backups on server '+@server_name+'.'

    EXEC msdb.dbo.sp_send_dbmail @recipients='geoffa@geoffa.com',

    @subject = @mysubject,

    @body = @mybody,

    @query = @mailcmd,

    @query_result_header = 0,

    @query_result_width = 600

    end

    drop table #just_temporary

    fetch next from db_crsr_DBS into @server_name

    end

    close db_crsr_DBS

    deallocate db_crsr_DBS

    Thanks for sharing your process.

  • Why not just use Policy Based Management and a Central Management Server? It's quick and easy to setup!

  • Does this work if you use external software such as ArcServe that calls SQL Server APIs?

  • I expect it does, as long as these backups are logged in MSDB.

  • Paul Brewer (4/22/2013)


    Why not just use Policy Based Management and a Central Management Server? It's quick and easy to setup!

    Good point! The simple truth is this was set up in the days of SQL 2005, which did not have policy-based management, but I will certainly look into it.

  • How about using Powershell instead of creating linked servers to every SQL Server in your environment? I like the logic you have, a table with the list of servers, a table for exclusions, but using something as heavy as linked servers seems a bit over the top.

    One other thing I prefer in my life, is an email regardless of success or failure. That way, I know that the "checker" is working. Relying on getting an email only if there's a failure is not a good habit for a DBA to get into.

  • sqldba.today (4/22/2013)


    How about using Powershell instead of creating linked servers to every SQL Server in your environment? I like the logic you have, a table with the list of servers, a table for exclusions, but using something as heavy as linked servers seems a bit over the top.

    One other thing I prefer in my life, is an email regardless of success or failure. That way, I know that the "checker" is working. Relying on getting an email only if there's a failure is not a good habit for a DBA to get into.

    Re Powershell: I chose to set this up, along with a bunch of other check scripts running against the linked servers, a number of years ago. Starting from scratch again, I would certainly consider Powershell today.

    Re mail: I quite agree, and that was part of why I set up this check. This script is only one step in a multi-step Agent job, which runs every day and mails its success or failure. So I know whether is has run (success/failure) or not (no mail).

  • Just an update for the Check_Backups script ... currently if the servers have different collation the script will fail.

    <code>

    USE [MonitorDB]

    GO

    IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE name = 'Check_Backups' and type = 'P')

    EXEC ('CREATE PROC [dbo].[Check_Backups] AS PRINT ''STUB VERSION'' ')

    GO

    ALTER PROC [dbo].[Check_Backups]

    @Server_Name SYSNAME = @@SERVERNAME

    , @days_back TINYINT = 7 -- # of days for backup history retrieval. Default = 7

    , @min_fulls TINYINT = 1 -- min. # of days with full backup required in period defined by @days_back. Default = 1

    , @min_diffs TINYINT = 6 -- min. # of days with differential backup required in period defined by @days_back. Default = 6

    , @min_logs TINYINT = 7 -- min. # of days with log backup required in period defined by @days_back. Default = 7

    AS

    /* Record backup information from a remote server in a holding table on the monitor server for reporting/alerting.

    Steps:

    1) Per server that is queried, record backup details in a table variable ('@backups');

    2) Cleanup monitor server holding table ('backup_check') for server being queried;

    3) Update monitor server holding table with info recorded in step 1 and include some logic to print warnings, based on @min_x params specified.

    Follow up:

    Email alerts can be sent through the SP 'Mail_Results_Check_Backups'. This SP will mail any entries marked by the word 'Check' in a tabular format.

    Exclusions:

    Databases can be excluded from this check by entering the database name in the table msdb.dbo.ExcludeFromMaintenance on the linked server

    (column name 'DatabaseName', type SYSNAME)

    */

    -- Determine whether exclusion table exists on linked server (@exclude_table=1) or not (@exclude_table=0)

    -- Uses a global temp table to store a value and pass on to a variable; improvements are welcome!

    SET NOCOUNT ON

    CREATE TABLE ##CheckBackupsTmp ([Exists] BIT)

    INSERT ##CheckBackupsTmp EXEC ('SELECT CASE WHEN (SELECT [id] FROM ['+@Server_Name+'].msdb.dbo.sysobjects WHERE [name] = ''ExcludeFromMaintenance'' AND [type] =''U'')> 0 THEN 1 ELSE 0 END')

    DECLARE @exclude_table BIT

    SELECT @exclude_table = [Exists] FROM ##CheckBackupsTmp

    DROP TABLE ##CheckBackupsTmp

    -- Build the SQL command string.

    DECLARE @cmd NVARCHAR(MAX)

    SELECT @cmd = N''

    SELECT @cmd = @cmd + '

    SET NOCOUNT ON -- Record basic database device and backup info in a temp table

    DECLARE @backups TABLE (

    [database_name] SYSNAME

    , [type] CHAR(1)

    , [last_backup] DATETIME

    , [count] SMALLINT

    , [backup_days] INT

    )

    INSERT @backups

    SELECT bs.[database_name]

    , bs.[type]

    , MAX(bs.backup_finish_date)

    , COUNT(*)

    , COUNT(DISTINCT DATEPART(DY,bs.backup_finish_date)) -- # of distinct days on which at least one backup was made, per backup type (to check against the minimum number of daily backups required)

    FROM [' + @Server_Name + '].msdb.dbo.backupset bs

    INNER JOIN

    [' + @Server_Name + '].msdb.dbo.backupmediafamily bmf

    ON bs.media_set_id = bmf.media_set_id

    WHERE bs.backup_start_date > DATEADD(day,DATEDIFF(day,0,GETDATE()),0)-' + CONVERT(VARCHAR(3),@days_back) + ' -- 00:00 at date specified by @days_back

    AND bs.backup_start_date < DATEADD(day,DATEDIFF(day,0,GETDATE()),0) -- 00:00 today

    GROUP BY bs.database_name

    , bs.[type]

    DELETE backup_check WHERE server_name = ''' + @Server_Name + ''' -- Delete old info from monitor server holding table

    INSERT INTO backup_check -- Update monitor server holding table

    SELECT DISTINCT

    ''' + @Server_Name + '''

    , d.name

    , d.create_date

    , d.recovery_model

    , (SELECT [count] FROM @backups WHERE type = ''D'' AND database_name = d.name COLLATE DATABASE_DEFAULT) -- Record # of full backups during sampling interval

    , (SELECT [count] FROM @backups WHERE type = ''I'' AND database_name = d.name COLLATE DATABASE_DEFAULT) -- Record # of differential backups during sampling interval

    , CASE

    WHEN d.recovery_model != 3

    THEN (SELECT [count] FROM @backups WHERE type = ''L'' AND database_name = d.name COLLATE DATABASE_DEFAULT) -- Record # of log backups during sampling interval

    ELSE -1 -- or print negative number to indicate recovery model = simple

    END

    , (SELECT MAX([last_backup]) FROM @backups WHERE type = ''D'' AND database_name = d.name COLLATE DATABASE_DEFAULT) -- Record date/time of last full backup

    , (SELECT MAX([last_backup]) FROM @backups WHERE type = ''I'' AND database_name = d.name COLLATE DATABASE_DEFAULT) -- Record date/time of last diff backup

    , (SELECT MAX([last_backup]) FROM @backups WHERE type = ''L'' AND database_name = d.name COLLATE DATABASE_DEFAULT) -- Record date/time of last log backup

    , CASE

    WHEN ISNULL((SELECT [backup_days] FROM @backups WHERE type = ''D'' AND database_name = d.name COLLATE DATABASE_DEFAULT),0) < '+ CONVERT(VARCHAR(3),@min_fulls) + '

    THEN ISNULL((SELECT CONVERT(VARCHAR(6),[backup_days]) FROM @backups WHERE type = ''D'' AND database_name = d.name COLLATE DATABASE_DEFAULT),''0'') + ''(!)''

    ELSE (SELECT CONVERT(VARCHAR(6),[backup_days]) FROM @backups WHERE type = ''D'' AND database_name = d.name COLLATE DATABASE_DEFAULT)

    END -- Print # of days with at least one full backup + warning if # of days with a full backup is below @min_fulls.

    , CASE

    WHEN ISNULL((SELECT [backup_days] FROM @backups WHERE type = ''I'' AND database_name = d.name COLLATE DATABASE_DEFAULT),0) < '+ CONVERT(VARCHAR(3),@min_diffs) + '

    THEN ISNULL((SELECT CONVERT(VARCHAR(6),[backup_days]) FROM @backups WHERE type = ''I'' AND database_name = d.name COLLATE DATABASE_DEFAULT),''0'') + ''(!)''

    ELSE (SELECT CONVERT(VARCHAR(6),[backup_days]) FROM @backups WHERE type = ''I'' AND database_name = d.name COLLATE DATABASE_DEFAULT)

    END -- Print # of days with at least one diff backup + warning if # of days with a diff backup is below @min_diff.

    , CASE

    WHEN d.recovery_model != 3 -- if recovery is not simple

    AND ISNULL((SELECT [backup_days] FROM @backups WHERE type = ''L'' AND database_name = d.name COLLATE DATABASE_DEFAULT),0) < '+ CONVERT(VARCHAR(3),@min_logs) + '

    THEN ISNULL((SELECT CONVERT(VARCHAR(6),[backup_days]) FROM @backups WHERE type = ''L'' AND database_name = d.name COLLATE DATABASE_DEFAULT),''0'') + ''(!)''

    ELSE (SELECT CONVERT(VARCHAR(6),[backup_days]) FROM @backups WHERE type = ''L'' AND database_name = d.name COLLATE DATABASE_DEFAULT)

    END -- Print # of days with at least one log backup + warning if # of days with a log backup is below @min_logs.

    FROM [' + @Server_Name + '].master.sys.databases d

    LEFT OUTER JOIN @backups b

    ON b.database_name = d.name COLLATE DATABASE_DEFAULT

    WHERE d.state = 0 -- online databases only

    AND d.name NOT IN (''model'',''tempdb'') -- exclude certain system dbs

    '

    -- Extend query to exclude databases in local exclusion table, if that exists

    SELECT @cmd = @cmd

    + CASE WHEN @exclude_table = 0

    THEN ''

    ELSE '

    AND d.name NOT IN -- do not report if DB exists in exclusion table

    (SELECT DatabaseName

    FROM [' + @Server_Name + '].[msdb].dbo.[ExcludeFromMaintenance] ) -- exclude database(s) in local exclusion table'

    END

    -- Execute query and store results in holding table

    --PRINT @cmd

    EXEC sp_executesql @cmd

    </code>

  • Thanks for the update; we are fortunate to have identical collations..

  • Hi Willem G.. Thank you so much for sharing backup report script and monitoring across all remote database in single email report.

    1. Full backup configured by thru SQL maintenance plan with two different timing

    Schedule 1. Every day 3:00 PM - full backup

    schedule 2. Every day 12:00 AM - full backup

    Last full backup Report result is not shown 12:00 AM instead of 3:00 PM date & time display in Email report.

    2. Transaction log backup configured every day 30 min

    Last TLOG Backup Report result is not shown pervious schedule date and time instead of 12:00 AM date & time display in Email report.

    3. I need to configure SQL schedule job as below script for receiving daily backup reports. Please suggest this script can be configured in job.

    EXEC Check_Remote_Servers @proc = 'Check_Backups'

    GO

    EXEC Mail_Results_Check_Backups

    @recipients = 'rel.ancd@ace.com',

    @profile_name = 'database_mail'

    GO

    4. Remote_Databases table - there is no data available but email receiving result all linked server databases.

    Thanks

    ananda

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply