Building a centralized backup monitoring solution

No Comments

Written by Luis Miguel – Originally published on October 19, 2010.

A complete solution would include sending data from several servers into a central server, in this example I’m going to show you how to do it for one server. Also the final presentation of the data should use some form of reporting tool that could display this data in a portal for easy consumption.

Step 1 – Create a table to store the backup information that will be captured periodically

USE utility
GO
CREATE TABLE [dba].[backups](
[backup_log_id] [int] IDENTITY(1,1) NOT NULL,
[server_name] [nvarchar](64) NULL,
[database_name] [nvarchar](128) NULL,
[start_date] [datetime] NULL,
[finish_date] [datetime] NULL,
[expiration_date] [datetime] NULL,
[backup_type] [nvarchar](64) NULL,
[backup_size] [int] NULL,
[physical_device_name] [nvarchar](128) NULL,
[backupset_name] [nvarchar](128) NULL,
[user_name] [nvarchar](64) NULL,
[backup_log_date] [datetime] NULL,
CONSTRAINT [PK_backups] PRIMARY KEY CLUSTERED(
[backup_log_id] ASC
)
WITH(
PAD_INDEX  = OFF,
STATISTICS_NORECOMPUTE  = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON
) ON [PRIMARY]
) ON [PRIMARY]
GO

Step 2 – Create the store procedure that will capture the data and save it to the backup information table

USE msdb
GO
CREATE PROCEDURE [dbo].[usp_backup_status]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @backup_log_date DATETIME
SET @backup_log_date = (SELECT DATEADD(dd, DATEDIFF(dd, 0, GetDate()), 0))
INSERT INTO utility.dba.backups
SELECT RTRIM(CONVERT(CHAR(100), SERVERPROPERTY('Servername'))) AS Server,
a.name,
b.backup_start_date,
d.backup_finish_date,
b.expiration_date,
CASE b.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
WHEN 'I' THEN 'Differential database'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
WHEN NULL THEN 'Null'
END AS backup_type,
b.backup_size / 1024000 AS backup_size,
c.physical_device_name,
b.name AS backupset_name,
b.user_name,
@backup_log_date AS backup_log_date
FROM sys.databases AS a
LEFT OUTER JOIN
(SELECT
database_name,
MAX(backup_finish_date) AS backup_finish_date
FROM msdb.dbo.backupset
GROUP BY database_name
) AS d
ON a.name = d.database_name
LEFT OUTER JOIN msdb.dbo.backupset AS b
ON (a.name = b.database_name) AND (b.backup_finish_date = d.backup_finish_date)
LEFT OUTER JOIN msdb.dbo.backupmediafamily AS c
ON b.media_set_id = c.media_set_id
ORDER BY a.name
END

Step 3 – Create a job to run the stored procedure as frequently as needed

Step 4 – Query the “backups” table to see information about today’s backups

SELECT
[server_name],
[database_name],
[start_date],
[finish_date],
[expiration_date],
[backup_type],
[backup_size],
[physical_device_name],
[backupset_name],
[user_name],
[backup_log_date]
FROM [utility].[dba].[backups]
WHERE [backup_log_date] = (SELECT DATEADD(dd, DATEDIFF(dd, 0, GetDate()), 0))

Powered by Netfirms