Introduction
Are you confident that all corporate databases that require backup are in fact being backed up? Are you sure no databases were added since you last made your selection in the SQL Maintenance Plans? Would you notice if a SQL server is not being backed up at all, for instance because the SQL Agent process hangs or is simply not running on a server? How would you know about failed backups if the corresponding email notification never makes it to your inbox?
Securing corporate data(bases) is a DBA's first and foremost priority. In my view, the only way to make sure databases are secured is not to rely on notifications or paper procedures, but to actively check whether backups have been made.
This article outlines a solution developed for that specific purpose, as part of a larger monitoring effort: for each database in a group of SQL servers, check whether it is being backed up on a regular basis. After completion of the check an email alert is sent detailing situations that require further investigation, if any. By default the script looks back one week to check whether your log or differential backups are supplemented by the full backup required for a restore.
In our company, the check described below is part of a multi-step SQL Agent job. I hope to discuss some of the other checks in a future article. They include, for example, notifications when database devices require extension and notifications on failed logins and unusual SQL error log entries. All checks run from a dedicated ‘monitor’ server and are performed against linked servers defined on the monitor server. As a result, extending the check(s) to a new SQL server is only a matter of adding a new linked server to the monitor server.
How does the backup check work?
The starting point is a wrapper stored procedure on the monitor server called ‘check_remote_servers’, the code of which is shown in Listing 1 (the code assumes the MonitorDB exists; see Setup below for more details).
Listing 1: cre_check_remote_servers.sql
USE [MonitorDB]
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE name = 'Check_Remote_Servers' and type = 'P')
EXEC ('CREATE PROC [dbo].[Check_Remote_Servers] AS PRINT ''STUB VERSION'' ')
GO
ALTER PROC [dbo].[Check_Remote_Servers]
@proc SYSNAME
, @par1 VARCHAR(50) = ''
, @par2 VARCHAR(50) = ''
, @par3 VARCHAR(50) = ''
, @par4 VARCHAR(50) = ''
AS
/* Execute a stored procedure against all linked servers defined on this server.
** Params: @proc = name of stored procedure to be executed
** @par1/2/3/4 = name and content of parameter(s) to be supplied to @proc
** Example: EXEC Check_Remote_Servers @proc = 'Check_Backups', @par1 = '@days_back=14'
** This will execute the procedure 'Check_Backups' against all linked servers with the parameter '@days_back' set to '14' instead of the default value '7'
*/SET NOCOUNT ON
DECLARE @server_name SYSNAME
, @cmd NVARCHAR(MAX)
DECLARE @linked_servers TABLE ([server_name] SYSNAME)
INSERT @linked_servers
SELECT name
FROM master.sys.servers
SELECT @server_name = MIN([server_name]) FROM @linked_servers
WHILE @server_name IS NOT NULL
BEGIN
PRINT 'Starting proc ''' + @proc + ''' on server ''' + @server_name + ''''
SET @cmd = 'EXEC [' + @proc + '] @Server_Name = [' + @server_name + '] '
+ CASE WHEN @par1 != '' THEN ', '+ @par1 ELSE '' END
+ CASE WHEN @par2 != '' THEN ', '+ @par2 ELSE '' END
+ CASE WHEN @par3 != '' THEN ', '+ @par3 ELSE '' END
+ CASE WHEN @par4 != '' THEN ', '+ @par4 ELSE '' END
EXEC sp_executesql @cmd
DELETE @linked_servers WHERE [server_name] = @server_name
SELECT @server_name = MIN([server_name]) FROM @linked_servers
END
GOThis procedure reads the names of all linked servers defined on the monitor server and, for each of them, starts a second procedure (supplied as parameter @proc), passing the name of the linked server to that second procedure. The wrapper procedure allows us to query all linked servers for various purposes (checks).
In this case the first procedure supplied to the wrapper is one that will record the names of all active user databases on all linked servers for later reference. Thus we ensure that checks are done on up-to-date information. Code samples for creation of the holding table and the stored procedure are given in Listings 2 and 3 below.
Listing 2: cre_table_Remote_Databases.sql
USE [Monitordb] GO CREATE TABLE [dbo].[Remote_Databases]( [server_name] [SYSNAME], [database_name] [SYSNAME] ) GO
Listing 3: cre_sp_List_Remote_Databases.sql
USE [Monitordb]
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE name = 'List_Remote_Databases' and type = 'P')
EXEC ('CREATE PROC [dbo].[List_Remote_Databases] AS PRINT ''STUB VERSION'' ')
GO
ALTER PROC [dbo].[List_Remote_Databases]
@Server_Name SYSNAME = @@SERVERNAME
AS
/* Create procedure to record the names of all active databases on a linked server in a local holding table */SET NOCOUNT ON
DELETE [remote_databases]
WHERE [server_name] = @Server_Name
DECLARE @cmd NVARCHAR(500)
SELECT @cmd = N'
INSERT INTO [MonitorDB].[dbo].[Remote_Databases]
SELECT ''' + @Server_Name + ''', [name]
FROM [' + @Server_Name + '].master.sys.databases
WHERE database_id > 4
AND state = 0'
-- PRINT @cmd -- For debug purposes
EXEC sp_executesql @cmd -- Execute this command against @Server_Name
GOListing 4: cre_table_Backup_Check.sql
USE [Monitordb] GO CREATE TABLE [dbo].[Backup_Check]( [Server_Name] [SYSNAME], [Database_Name] [SYSNAME], [Create_Date] [SMALLDATETIME], [Recovery_Model] [TINYINT], [Fulls] [SMALLINT] NULL, [Diffs] [SMALLINT] NULL, [Logs] [SMALLINT] NULL, [Last_Full] [SMALLDATETIME] NULL, [Last_Diff] [SMALLDATETIME] NULL, [Last_Log] [SMALLDATETIME] NULL, [BackupDays_Full] [VARCHAR](6) NULL, [BackupDays_Diff] [VARCHAR](6) NULL, [BackupDays_Log] [VARCHAR](6) NULL )
Listing 5: cre_sp_Check_Backups.sql
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) -- Record # of full backups during sampling interval
, (SELECT [count] FROM @backups WHERE type = ''I'' AND database_name = d.name) -- 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) -- 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) -- Record date/time of last full backup
, (SELECT MAX([last_backup]) FROM @backups WHERE type = ''I'' AND database_name = d.name) -- Record date/time of last diff backup
, (SELECT MAX([last_backup]) FROM @backups WHERE type = ''L'' AND database_name = d.name) -- Record date/time of last log backup
, CASE
WHEN ISNULL((SELECT [backup_days] FROM @backups WHERE type = ''D'' AND database_name = d.name),0) < '+ CONVERT(VARCHAR(3),@min_fulls) + '
THEN ISNULL((SELECT CONVERT(VARCHAR(6),[backup_days]) FROM @backups WHERE type = ''D'' AND database_name = d.name),''0'') + ''(!)''
ELSE (SELECT CONVERT(VARCHAR(6),[backup_days]) FROM @backups WHERE type = ''D'' AND database_name = d.name)
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),0) < '+ CONVERT(VARCHAR(3),@min_diffs) + '
THEN ISNULL((SELECT CONVERT(VARCHAR(6),[backup_days]) FROM @backups WHERE type = ''I'' AND database_name = d.name),''0'') + ''(!)''
ELSE (SELECT CONVERT(VARCHAR(6),[backup_days]) FROM @backups WHERE type = ''I'' AND database_name = d.name)
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),0) < '+ CONVERT(VARCHAR(3),@min_logs) + '
THEN ISNULL((SELECT CONVERT(VARCHAR(6),[backup_days]) FROM @backups WHERE type = ''L'' AND database_name = d.name),''0'') + ''(!)''
ELSE (SELECT CONVERT(VARCHAR(6),[backup_days]) FROM @backups WHERE type = ''L'' AND database_name = d.name)
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
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
EXEC sp_executesql @cmd
GOListing 6: cre_sp_Mail_Results_Check_Backups.sql
USE [MonitorDB]
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE name = 'Mail_Results_Check_Backups' and type = 'P')
EXEC ('CREATE PROC [dbo].[Mail_Results_Check_Backups] AS PRINT ''STUB VERSION'' ')
GO
ALTER PROCEDURE [dbo].[Mail_Results_Check_Backups]
@recipients VARCHAR(MAX) = 'someone@company.com'
, @profile_name SYSNAME = '<your_mail_profile>'
AS
-- This SP runs following the SP 'Check_Backups' and will send an email notification for possible follow-up.
IF EXISTS (SELECT 1
FROM Monitordb.dbo.Backup_Check
WHERE CHARINDEX('!', [Backupdays_Full]) > 0
OR CHARINDEX('!', [BackupDays_Diff]) > 0
OR CHARINDEX('!', [BackupDays_Log]) > 0
)
BEGIN -- If a condition for possible follow-up is detected, send email alert
DECLARE @subject VARCHAR(200)
, @body NVARCHAR(MAX)
, @xml NVARCHAR(MAX)
SELECT @subject = 'SQL Backup warnings'
SET @body = '<html><body>
<p><font size="2" face="monaco">
(This mail was sent by the procedure ''' + DB_NAME() + '.' + OBJECT_SCHEMA_NAME (@@PROCID) + '.' + OBJECT_NAME(@@PROCID) + ''') <BR><BR>
This table lists database backup situations that might require follow-up, and is based on values retrieved by the SP ''' + DB_NAME() + '.dbo.Check_Backups''. <BR><BR>
Records with an exclamation mark (!) in the three last columns should be investigated.<BR><BR>
''Young'' database may also show up because the required number of backups has not yet been reached. <BR>
<table border=1 cellpadding="5">
<p><font size="2" face="monaco">
<tr>
<th>Server</th> <th>Database</th> <th>Created</th> <th>Fulls</th> <th>Diffs</th> <th>Logs</th> <th>Last<br/>Full</th> <th>Last<br/>Diff</th> <th>Last<br/>Log</th> <th>Days<br/>/Full</th> <th>Days<br/>/Diff</th> <th>Days<br/>/Log</th> </tr>'
SELECT @xml = CAST(
(SELECT LEFT(Server_Name,30) AS 'td','' -- SQL Server
, LEFT(Database_Name,30) AS 'td','' -- Database
, CONVERT(CHAR(11),Create_Date,20) AS 'td','' -- Created
, ISNULL(Fulls,0) AS 'td','' -- SQL Full backups
, ISNULL(Diffs,0) AS 'td','' -- SQL Differential backups
, ISNULL(Logs,0) AS 'td','' -- SQL Log backups
, ISNULL(CONVERT(CHAR(16),Last_Full,20),'--') AS 'td','' -- Last_Full
, ISNULL(CONVERT(CHAR(16),Last_Diff,20),'--') AS 'td','' -- Last_Diff
, ISNULL(CONVERT(CHAR(16),Last_Log, 20),'--') AS 'td','' -- Last_Log
, ISNULL([BackupDays_Full],0) AS 'td','' -- # Days on which full backups were made + (!) in case of a warning or warning
, ISNULL([BackupDays_Diff],0) AS 'td','' -- # Days on which diff backups were made or warning
, ISNULL([BackupDays_Log],0) AS 'td','' -- # Days on which log backups were made or warning
FROM Monitordb.dbo.Backup_Check
WHERE CHARINDEX('!', [Backupdays_Full]) > 0
OR CHARINDEX('!', [BackupDays_Diff]) > 0
OR CHARINDEX('!', [BackupDays_Log]) > 0
ORDER BY Server_Name, Database_Name
FOR XML PATH('tr'), ELEMENTS )
AS NVARCHAR(MAX))
SET @body = @body + @xml +'</p></table>
By default, each database must meet the following criteria over the past week:<BR>
- At least 1 full backup, AND <BR>
- At least 1 differential or full backup on each of the other days, AND <BR>
- At lease 1 log backup on each day <BR><BR></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile_name
, @recipients = @recipients
, @subject = @subject
, @body = @body
, @body_format = 'HTML'
END
GOThe Advantages
The check is dynamic: you do not need to maintain a list of databases to be checked and a new database does not get overlooked. As long as you maintain the linked server entries on the monitor server the code will do the rest. Also, the central setup removes the need for having mail functionality or code maintenance outside the monitor server. In case of failures, the central setup facilitates troubleshooting (single job, single output file, et cetera).
The procedure is parameter-driven so you can apply your own corporate rules (# of full/diff/log backups per day and # of days of backup history to ensure a full backup set for a restore). Optionally, you can exclude databases from the backup check by listing them in a special exclusion table in msdb on the linked server (see Listing 7 for table creation).
Listing 7: OPTION_cre_table_ExcludeFromMaintenance.sql
/* You can use this script to create an exclusion table on a linked server. ** Backup history of databases on the linked server with a name corresponding to en entry in this table will not be checked. */USE [msdb] GO CREATE TABLE [dbo].[ExcludeFromMaintenance]( [DatabaseName] [sysname] ) GO
Finally, no mail is sent in case there is nothing to report and alerts, if any, for all servers are combined into a single mail with a tabular layout for easy readability.
Setup
In our environment the SQL Agent job step for this check runs under an account with sysadmin privileges (to avoid problems querying linked servers). Other options are of course possible, but would require testing and, possibly, some tweaking.
The various tables and procedures on the monitor server are grouped in a database called 'MonitorDB’. Make sure you replace all references in the code samples if you use another name.
To set up this check, please proceed as follows:
- Choose a monitor server (SQL 2005/2008(r2) and create a small (e.g. 10 MB) database called 'MonitorDB'. Having a separate monitor server is not technically necessary. If you do not have the resources, use one of your regular SQL servers as monitor server;
- Create one or more linked servers on the monitor server and make sure you can access them with the account used for the check(s). (It is probably easiest to start with a sysadmin account and narrow down as needed if you find the solution useful);
- Make sure your monitor server has SQL Database Mail configured; record the name of a mail profile you can use for the account in the previous step;
- Create the holding tables in MonitorDB with the scripts 'cre_table_<table name>.sql' (Listings 2 and 4);
- Create the stored procedures in MonitorDB with the scripts 'cre_sp_<procedure name>.sql' (Listings 1, 3, 5, 6);
- If you want to make exclusions, create and populate the exclusion table in msdb on the relevant linked servers (Listing 7).
You are now ready to run a first check. As a test, from a query window, issue the commands below:
USE MonitorDB EXEC Check_Remote_Servers @proc = 'List_Remote_Databases'
Line(s) should show up indicating the SP was started on a linked server as seen below.
Next, run this command:
EXEC Check_Remote_Servers @proc = 'Check_Backups'
Again, you should see line(s) that show up indicating the SP was started on a linked server.
Finally, execute this command:
EXEC Mail_Results_Check_Backups @recipients = '<email address>', @profile_name = '<email profile>'
if you have a default mail profile the latter can be omitted. if situations requiring follow-up were found, a line will indicate that an email was queued. If not, you should see the message ‘Command(s) completed successfully.’
The mail layout is displayed below.
The first three columns in the table indicate the server name, database name and date of creation, followed by three columns with the total number of backups per type. Next, date and time of the last full, differential and log backups are shown. The last three columns indicate the number of distinct days on which a full, differential or log backup was made, respectively.
In case the check finds nothing to report (which would be a good thing), you can simulate a ‘problem’. Simply create a new database on one of your linked servers and rerun the check from the beginning (the new database will be picked up in in the first step (‘List_Remote_Databases’). You should now receive a notification mail containing a row for the new database.
If you find it useful to run a daily check on your backups in this way you can include it in a SQL Agent job on the monitor server. The step where you execute ‘List_Remote_Servers’ should be a first step, e.g. called ‘Update Remote DB Info’, followed by a step in which you combine ‘Check_Backups’ and the procedure to mail the results. The advantage of separating the first step is flexibility and transparency; it enables other checks (see Introduction, to be described later) to use the results regardless of the order in which they occur.
Note that the backup history reviewed starts at midnight of the first day considered and ends at midnight on the day the check is run, based on backup start time. So, for example, if you start the check at 3 AM on Feb. 10 and use default values all backups started between Feb. 3, 00:00 and Feb. 10, 00:00 are recorded.
Using non-default parameters
By default, the check investigates 7 days of backup history. In that period, it expects to find (at least) 1 day with a full backup, (at least) 6 days with a differential backup and 7 days with at least one log backup.
If you need other values you can change the parameters used by the stored procedure ‘Check_Backups’. The easiest way to do so, especially if these are your defaults, is by changing the default values at compile time. Simply change the value after the equal sign following the ALTER PROC statement.
The parameters used are:
- @days_back for the number of days of backup history to retrieve;
- @min_fulls for the minimum number of days with a full backup in the period defined by @days_back;
- @min_diffs for the minimum number of days with a differential backup in the period defined by @days_back;
- @min_logs for the minimum number of days with a log backup in the period defined by @days_back.
Alternatively, you can supply the new values at runtime through ‘Check_Remote_Servers’. ‘Check_Remote_Servers’ will accept up to four parameters in the format: ‘@par1 = ‘name=value’, to be passed on the next stored procedure. Supposing you want to examine 3 days of backup history and require 1 day with a full backup, 2 days with a differential and 3 days with a log backup, you could issue the following command:
EXEC Check_Remote_Servers @proc='Check_Backups' , @par1='@days_back=3' , @par2='@min_fulls=1' , @par3='@min_diffs=2' , @par4='@min_logs=3'
