RSSBuilding a centralized backup monitoring solution
Oct 19
Backup, Monitoring, T-SQL 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))