Written by • November 29, 2018• 12:08• Database administration, Database Optimization & Structure

Monitoring Backups Across Instances

HomeDatabase administration, Database Optimization & StructureMonitoring Backups Across Instances

Introduction

In the last two or three months, I have been asked twice for a solution native to SQL Server that consolidates a backup report for several SQL Server instances across an enterprise. This question came from friends that did not necessarily want to spend money buying a tool but were more inclined to leverage the capabilities of SQL Server. I have thought about two possible ways to achieve this:

  1. Using Linked Servers, catalog views, SQL Agent Jobs and Database Mail
  2. Using Central Management Server

In this article, I will demonstrate the first and hope we shall have a second part of the article sometime later.

Scenario

My environment consists of a set of three instances sitting on separate servers on AWS. These “servers” are actually Amazon EC2s running SQL Server 2017 RTM CU5. We are also going to be taking advantage of Amazon Simple Email Service to configure Database Mail. In production, you can definitely use your on-premise Email Servers and achieve the same goals. You will notice later in this article that the hostname (and thus the instance names) are the same. This is because the servers were cloned from the same Amazon Machine Image (pardon the “laziness”). This will not likely be the case in production.

Take a Few Backups

Let’s start by taking a few backups of databases sitting on these three instances. This will generate the data we shall work with. We shall then verify the backups are captured in the system tables msdb.dbo.backupset and msdb.dbo.backupmediafamily. The full descriptions of these tables can be reviewed on this Microsoft Documentation or simply be using sp_columns.

-- Listing 1: Taking Backups on the Instances
-- Backup a single DB with one stripe
backup database newdb to disk='newdb.bak'

-- Backup all DBs in the instance with timestamp in the backupset name
exec sp_MSforeachdb @command1=
'declare @path varchar(300)
set  @path=''M:\MSSQL\BACKUP\?_Backup'' + convert(varchar(10),getdate(),110) + ''.bak''
print @path
backup database [?] to disk=@path'

-- Backup a single large DB with four stripes
backup database [PieceMealDB] to
disk='M:\MSSQL\BACKUP\PieceMealDB_01.bak',
disk='M:\MSSQL\BACKUP\PieceMealDB_02.bak',
disk='M:\MSSQL\BACKUP\PieceMealDB_03.bak',
disk='M:\MSSQL\BACKUP\PieceMealDB_04.bak'
with
stats=10
Describing msdb.dbo.backupset

Fig 3. Describing msdb.dbo.backupset

Checking Backups

The following script takes advantage of two catalog views backupset and backupmediafamily to examine the history of backups created on an instance of SQL Server. The backupset catalog contains a row for each backup set. A backup set is defined as the content of a backup operation that is added to a media set. A media set is an order collection of media to which one or more backup operations have written.

-- Listing 2: Check Backups using msdb tables --
PRINT 'Checking Databases Successfully Backed Up'
use msdb
go
select bus.database_name,bus.type, case bus.type when 'D' then 'Full' when 'I' then 'Differential' when 'L' then 'Log' end backup_type, bus.backup_start_date, bus.backup_finish_date,
(((DATEPART(HH,bus.backup_finish_date))- (DATEPART(HH,bus.backup_start_date)))*3600) +
(((DATEPART(MI,bus.backup_finish_date)) - (DATEPART(MI,bus.backup_start_date)))*60) +
(((DATEPART(SS,bus.backup_finish_date)) - DATEPART(SS,bus.backup_start_date)))
[backup_time (secs)], bus.backup_size,
bmf.physical_device_name 
from backupset bus
join backupmediafamily bmf on bus.media_set_id=bmf.media_set_id
where bus.backup_start_date >= (getdate() - 7)
order by bus.backup_start_date desc
Sample Output of Backup Checks

Fig 5. Sample Output of Backup Checks

Checking Backups on Other Instances

Using Linked Servers, we can extract data from remote instances. In this case, we shall use a simple linked server to extract backup history information from the msdb databases of two remote instances. The security configuration for these Linked Servers totally depends on you but we have kept is very simple here for the purpose of our objective. Listing 3 shows the script that can utilize these Linked servers to aggregate backup history data.

A Simple Linked Server

Fig 6. A Simple Linked Server

 

Linked Server for Two Remote Instances

Fig 7. Linked Server for Two Remote Instances

-- Listing 3: Checking Backups using msdb tables across Linked Servers
use msdb
go

with srva as (
select bus.server_name instance, bus.database_name,bus.type, 
case bus.type when 'D' then 'Full' 
when 'I' then 'Differential' 
when 'L' then 'Log' end backup_type
, bus.backup_start_date, bus.backup_finish_date,
(((DATEPART(HH,bus.backup_finish_date))- (DATEPART(HH,bus.backup_start_date)))*3600) +
(((DATEPART(MI,bus.backup_finish_date)) - (DATEPART(MI,bus.backup_start_date)))*60) +
(((DATEPART(SS,bus.backup_finish_date)) - DATEPART(SS,bus.backup_start_date)))
[backup_time (secs)], bus.backup_size,
bmf.physical_device_name 
from backupset bus
join backupmediafamily bmf on bus.media_set_id=bmf.media_set_id
where bus.backup_start_date >= (getdate() - 3)
)

, srvb as (
select bus.server_name instance, bus.database_name,bus.type, 
case bus.type when 'D' then 'Full' 
when 'I' then 'Differential' 
when 'L' then 'Log' end backup_type
, bus.backup_start_date, bus.backup_finish_date,
(((DATEPART(HH,bus.backup_finish_date))- (DATEPART(HH,bus.backup_start_date)))*3600) +
(((DATEPART(MI,bus.backup_finish_date)) - (DATEPART(MI,bus.backup_start_date)))*60) +
(((DATEPART(SS,bus.backup_finish_date)) - DATEPART(SS,bus.backup_start_date)))
[backup_time (secs)], bus.backup_size,
bmf.physical_device_name 
from [10.0.1.155].msdb.dbo.backupset bus
join [10.0.1.155].msdb.dbo.backupmediafamily bmf on bus.media_set_id=bmf.media_set_id
where bus.backup_start_date >= (getdate() - 3)
)
, srvc as (
select bus.server_name instance, bus.database_name,bus.type, 
case bus.type when 'D' then 'Full' 
when 'I' then 'Differential' 
when 'L' then 'Log' end backup_type
, bus.backup_start_date, bus.backup_finish_date,
(((DATEPART(HH,bus.backup_finish_date))- (DATEPART(HH,bus.backup_start_date)))*3600) +
(((DATEPART(MI,bus.backup_finish_date)) - (DATEPART(MI,bus.backup_start_date)))*60) +
(((DATEPART(SS,bus.backup_finish_date)) - DATEPART(SS,bus.backup_start_date)))
[backup_time (secs)], bus.backup_size,
bmf.physical_device_name 
from [10.0.1.83].msdb.dbo.backupset bus
join [10.0.1.83].msdb.dbo.backupmediafamily bmf on bus.media_set_id=bmf.media_set_id
where bus.backup_start_date >= (getdate() - 3)
)

select * from srva
union 
select * from srvb
union
select * from srvc;

Incorporating SES and Database Mail

The next step we take is to automate this check and mail the result set to Database Administrators. The steps required would be as follows in summary:

    1. Configure Amazon SES. You can learn how to quickly set up email on AWS using the documentation provided at Amazon SES Quick Start. When using on-premise email service, this will not be necessary for the DBA.
    2. Configure Database Mail. This article is not intended to demonstration Database Mail so we just give a screenshot of the SQL mail account configuration:
      SQL Mail Account Settings

      Fig 7. SQL Mail Account Settings

      • The port number when using SES to send emails is 587 NOT 25
      • Amazon SES requires a secure connection thus the checkbox identified in lilac (Fig. 7) must be selected
      • Basic authentication using the SMTP credentials is required (i.e. anonymous authentication is not allowed).

      We just need to be aware of a few things when using Amazon SES for Database Mail:

    3. Configure SQL Agent to use the Mail Profile. SQL Server Agent must be configured to use the mail profile created during the Database Mail configuration for the agent jobs to fire emails. (See Fig. 8)
    4. Create a staging table. A staging table will contain the aggregated result set for all backup history data from the instances we have targeted using Linked Servers. The table DDL is shown in Listing 4.
-- Listing 4: Backup History Table DDL
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[backuphistory](
	[instance] [nvarchar](128) NULL,
	[database_name] [nvarchar](128) NULL,
	[type] [char](1) NULL,
	[backup_type] [varchar](12) NULL,
	[backup_start_date] [datetime] NULL,
	[backup_finish_date] [datetime] NULL,
	[backup_time (secs)] [int] NULL,
	[backup_size] [numeric](20, 0) NULL,
	[physical_device_name] [nvarchar](260) NULL
) ON [PRIMARY]
GO
SQL Agent Settings

Fig 8. SQL Agent Settings

We go ahead and schedule the script in listing 3 in an SQL Agent Job and we have the complete script in Listing 5.

-- Listing 5: Complete SQL Agent Job for Backup History Notification

USE [msdb]
GO

/****** Object:  Job [Enteprise Backup History Summary]    Script Date: 9/26/2018 10:16:46 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 9/26/2018 10:16:46 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Enteprise Backup History Summary', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'TWENTYTOWERS\Administrator', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Aggregate Backup History]    Script Date: 9/26/2018 10:16:46 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Aggregate Backup History', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'-- Check Backups using msdb tables --
-- Across Linked Servers
use msdb
go

truncate table [msdb].[dbo].[backuphistory];

with srva as (
select bus.server_name instance, bus.database_name,bus.type, 
case bus.type when ''D'' then ''Full'' 
when ''I'' then ''Differential'' 
when ''L'' then ''Log'' end backup_type
, bus.backup_start_date, bus.backup_finish_date,
(((DATEPART(HH,bus.backup_finish_date))- (DATEPART(HH,bus.backup_start_date)))*3600) +
(((DATEPART(MI,bus.backup_finish_date)) - (DATEPART(MI,bus.backup_start_date)))*60) +
(((DATEPART(SS,bus.backup_finish_date)) - DATEPART(SS,bus.backup_start_date)))
[backup_time (secs)], bus.backup_size,
bmf.physical_device_name 
from backupset bus
join backupmediafamily bmf on bus.media_set_id=bmf.media_set_id
where bus.backup_start_date >= (getdate() - 3)
)

, srvb as (
select bus.server_name instance, bus.database_name,bus.type, 
case bus.type when ''D'' then ''Full'' 
when ''I'' then ''Differential'' 
when ''L'' then ''Log'' end backup_type
, bus.backup_start_date, bus.backup_finish_date,
(((DATEPART(HH,bus.backup_finish_date))- (DATEPART(HH,bus.backup_start_date)))*3600) +
(((DATEPART(MI,bus.backup_finish_date)) - (DATEPART(MI,bus.backup_start_date)))*60) +
(((DATEPART(SS,bus.backup_finish_date)) - DATEPART(SS,bus.backup_start_date)))
[backup_time (secs)], bus.backup_size,
bmf.physical_device_name 
from [10.0.1.155].msdb.dbo.backupset bus
join [10.0.1.155].msdb.dbo.backupmediafamily bmf on bus.media_set_id=bmf.media_set_id
where bus.backup_start_date >= (getdate() - 3)
)
, srvc as (
select bus.server_name instance, bus.database_name,bus.type, 
case bus.type when ''D'' then ''Full'' 
when ''I'' then ''Differential'' 
when ''L'' then ''Log'' end backup_type
, bus.backup_start_date, bus.backup_finish_date,
(((DATEPART(HH,bus.backup_finish_date))- (DATEPART(HH,bus.backup_start_date)))*3600) +
(((DATEPART(MI,bus.backup_finish_date)) - (DATEPART(MI,bus.backup_start_date)))*60) +
(((DATEPART(SS,bus.backup_finish_date)) - DATEPART(SS,bus.backup_start_date)))
[backup_time (secs)], bus.backup_size,
bmf.physical_device_name 
from [10.0.1.83].msdb.dbo.backupset bus
join [10.0.1.83].msdb.dbo.backupmediafamily bmf on bus.media_set_id=bmf.media_set_id
where bus.backup_start_date >= (getdate() - 3)
)

insert into [msdb].[dbo].[backuphistory]
select * from srva
union 
select * from srvb
union
select * from srvc;
', 
		@database_name=N'msdb', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Query Member Servers for Backups]    Script Date: 9/26/2018 10:16:46 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Query Member Servers for Backups', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N''<H1><font face="Verdana" size="4">Enterprise Backup History Summary</H1>'' +
    N''<table border="1"><font face="Verdana" size="2">'' +
    N''<tr><th><font face="Verdana" size="2">Instance Name</th>'' +
	N''<th><font face="Verdana" size="2">Database Name</th>'' +
    N''<th><font face="Verdana" size="2">Backup Start Date</th>'' +
    N''<th><font face="Verdana" size="2">Backup Finish Date</th>'' +
	N''<th><font face="Verdana" size="2">Backup Time (secs)</th>'' +
    N''<th><font face="Verdana" size="2">Backup Size</th>'' +
    N''<th><font face="Verdana" size="2">Physical Device Name</th></tr>'' +
    CAST ( ( SELECT td = bus.instance,			'''',
					td = bus.database_name,       '''',
                    td = bus.backup_start_date, '''',
                    td = bus.backup_finish_date, '''',
                    td = (((DATEPART(HH,bus.backup_finish_date))- (DATEPART(HH,bus.backup_start_date)))*3600) +
(((DATEPART(MI,bus.backup_finish_date)) - (DATEPART(MI,bus.backup_start_date)))*60) +
(((DATEPART(SS,bus.backup_finish_date)) - DATEPART(SS,bus.backup_start_date))), '''',
                    td = bus.backup_size, '''',
                    td = bus.physical_device_name
              FROM backuphistory as bus
              WHERE bus.backup_start_date >= (getdate() - 7)
              ORDER BY bus.backup_start_date desc
              FOR XML PATH(''tr''), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N''</table>'' + 	''<p style="margin-top: 0; margin-bottom: 0">&nbsp;</p>
	<p style="margin-top: 0; margin-bottom: 0"><font face="Verdana" size="2">Thanks   
	and Regards,</font></p>  &nbsp;
	<p style="margin-top: 0; margin-bottom: 0"><font face="Verdana" size="2">Enterprise Database Operations</font></p>  
	<p>&nbsp;</p>''  ;

EXEC msdb.dbo.sp_send_dbmail @recipients=''[email protected];[email protected]'',
    @subject = ''Enterprise Backup History Summary'',
    @body = @tableHTML ,
    @body_format = ''HTML'' ;', 
		@database_name=N'msdb', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Mail Complete Result Set to Support]    Script Date: 9/26/2018 10:16:46 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Mail Complete Result Set to Support', 
		@step_id=3, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N''<H1><font face="Verdana" size="4">Enterprise Backup History Summary</H1>'' +
    N''<table border="1"><font face="Verdana" size="2">'' +
    N''<tr><th><font face="Verdana" size="2">Instance Name</th>'' +
	N''<th><font face="Verdana" size="2">Database Name</th>'' +
    N''<th><font face="Verdana" size="2">Backup Start Date</th>'' +
    N''<th><font face="Verdana" size="2">Backup Finish Date</th>'' +
	N''<th><font face="Verdana" size="2">Backup Time (secs)</th>'' +
    N''<th><font face="Verdana" size="2">Backup Size</th>'' +
    N''<th><font face="Verdana" size="2">Physical Device Name</th></tr>'' +
    CAST ( ( SELECT td = bus.instance,			'''',
					td = bus.database_name,       '''',
                    td = bus.backup_start_date, '''',
                    td = bus.backup_finish_date, '''',
                    td = (((DATEPART(HH,bus.backup_finish_date))- (DATEPART(HH,bus.backup_start_date)))*3600) +
(((DATEPART(MI,bus.backup_finish_date)) - (DATEPART(MI,bus.backup_start_date)))*60) +
(((DATEPART(SS,bus.backup_finish_date)) - DATEPART(SS,bus.backup_start_date))), '''',
                    td = bus.backup_size, '''',
                    td = bus.physical_device_name
              FROM backuphistory as bus
              WHERE bus.backup_start_date >= (getdate() - 7)
              ORDER BY bus.backup_start_date desc
              FOR XML PATH(''tr''), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N''</table>'' + 	''<p style="margin-top: 0; margin-bottom: 0">&nbsp;</p>
	<p style="margin-top: 0; margin-bottom: 0"><font face="Verdana" size="2">Thanks   
	and Regards,</font></p>  &nbsp;
	<p style="margin-top: 0; margin-bottom: 0"><font face="Verdana" size="2">Enterprise Database Operations</font></p>  
	<p>&nbsp;</p>''  ;

EXEC msdb.dbo.sp_send_dbmail @recipients=''[email protected];[email protected]'',
    @subject = ''Enterprise Backup History Summary'',
    @body = @tableHTML ,
    @body_format = ''HTML'' ;', 
		@database_name=N'msdb', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Executing this job results in the output shown in Fig 9. The table is created using very simple HTML and can be further developed to suit your needs.

Email Output of SQL Agent Job Execution

Fig 9. Email Output of SQL Agent Job Execution

Conclusion

We have gone through a simple method of aggregating backup history information (and possible any other data contained in system databases) using linked servers. We further went on to automate this process using SQL Agent, Database Mail, and a little HTML. This method may seem a little crude and I am sure there are tools out there which can do much better, but this would server purpose for those just starting out with SQL Server or environments on a low budget. With a little bit of creativity, you can further customize the scripts and adapt the scripts to other uses.

References

  1. Configuring Database Mail
  2. Getting Started with Amazon SES
  3. Linked Servers
  4. Backup History and Header Information
Tags: , , Last modified: September 22, 2021

About the Author /

Kenneth Igiri is an Enterprise Architect with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over fifteen years' experience in Information Technology with nine of those years focused on SQL Server and Oracle databases. His interests include Database Performance, HADR, Cloud Computing, Data Architecture and Enterprise Architecture. Asides from work, Kenneth teaches at Children's Church, writes faith-based fiction and helps small businesses grow. You can connect with Kenneth via his blog https://kennethigiri.com, LinkedIn, or on Amazon.com.

← Previous Story
Tracking Database Changes Using Working Folder Source Control
→ Next Story
Navigating SQL Server Error Logs

Related Posts

SQL Percentage Calculation

How to Get SQL Percentage Calculation as Easy as Pie 

March 14, 2024• SQL Server, Work with data

Percentage calculation in SQL can be a bit like traversing an unfamiliar labyrinth. If you’re a beginner, it’s easy to get lost in a...
Read More →

Best FREE SQL Server Tools for Database Developers and Administrators!

December 07, 2023• Tools & technologies • One Comment

This article has recently been extensively revised. In particular, we reworked the article structure, and navigation, and updated...
Read More →

dbForge Edge: Your Best Universal Tool for Multidatabase Challenges

June 06, 2023• MySQL, Oracle, PostgreSQL, SQL Server, Tools & technologies

Handling several database management systems simultaneously is inevitable in working on multiple projects. At the same time, each system...
Read More →

31 Must-Know SQL Server DBA Interview Questions – Are You Prepared?

November 01, 2022• SQL Server

This article will help you to prepare for SQL Server interviews. I have explained various popular SQL Server interview questions and...
Read More →

Comments are closed.

↑
Close