April 22, 2013 at 12:05 am
Comments posted to this topic are about the item Stay Backed Up by Monitoring Your Backup Process
April 22, 2013 at 4:33 am
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.
April 22, 2013 at 6:36 am
Why not just use Policy Based Management and a Central Management Server? It's quick and easy to setup!
April 22, 2013 at 7:03 am
Does this work if you use external software such as ArcServe that calls SQL Server APIs?
April 22, 2013 at 7:13 am
I expect it does, as long as these backups are logged in MSDB.
April 22, 2013 at 7:58 am
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.
April 22, 2013 at 7:19 pm
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.
April 23, 2013 at 1:37 am
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).
June 3, 2013 at 8:00 pm
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>
June 4, 2013 at 1:10 am
Thanks for the update; we are fortunate to have identical collations..
March 16, 2016 at 12:02 am
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