SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stay Backed Up by Monitoring Your Backup Process


Stay Backed Up by Monitoring Your Backup Process

Author
Message
Willem G
Willem G
Old Hand
Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)

Group: General Forum Members
Points: 355 Visits: 550
Comments posted to this topic are about the item Stay Backed Up by Monitoring Your Backup Process
Geoff A
Geoff A
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4320 Visits: 1825
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.
Paul Brewer
Paul Brewer
SSC Eights!
SSC Eights! (899 reputation)SSC Eights! (899 reputation)SSC Eights! (899 reputation)SSC Eights! (899 reputation)SSC Eights! (899 reputation)SSC Eights! (899 reputation)SSC Eights! (899 reputation)SSC Eights! (899 reputation)

Group: General Forum Members
Points: 899 Visits: 1328
Why not just use Policy Based Management and a Central Management Server? It's quick and easy to setup!
Sailor
Sailor
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1772 Visits: 861
Does this work if you use external software such as ArcServe that calls SQL Server APIs?



Willem G
Willem G
Old Hand
Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)

Group: General Forum Members
Points: 355 Visits: 550
I expect it does, as long as these backups are logged in MSDB.
Willem G
Willem G
Old Hand
Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)

Group: General Forum Members
Points: 355 Visits: 550
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.
sqldba.today
sqldba.today
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 629
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.
Willem G
Willem G
Old Hand
Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)

Group: General Forum Members
Points: 355 Visits: 550
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).
dougjjj
dougjjj
SSC Eights!
SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)

Group: General Forum Members
Points: 963 Visits: 648
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>



Willem G
Willem G
Old Hand
Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)

Group: General Forum Members
Points: 355 Visits: 550
Thanks for the update; we are fortunate to have identical collations..
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search