We all know that Database backups are the bread and butter task of the database administration job. We cannot have any database running without backups. Without proper and prompt backup it is not possible to recover the database in the event of data corruption and failure.
Therefore, it is critical to monitor the backups continuously.
In This scenario we can add one more step in our Backup User Databases job which monitoring to find any databases that have not been backed up in certain number of hour.
In our admin database we can create one store procedure uspMonitorBackups. Below I have mentioned the code of the stored procedure uspMonitorBackups, which queries the msdb.dbo.bacupset table for the last backup.
USE [admin]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[uspMonitorBackups]‘) AND type in (N’P', N’PC’))
DROP PROCEDURE [dbo].[uspMonitorBackups]
GO
CREATE PROCEDURE uspMonitorBackups @backuptype char(1), @numOfMinutes int
as
–D = Database
–L = Log
–Example: Exec admin.dbo.uspMonitorBackups ‘D’,24
declare @dbname varchar(2000)
select name as Databasename
from master.dbo.sysdatabases
where name not in (‘master’,'model’,'msdb’,'tempdb’,'LiteSpeedLocal’)
and name not in
(select distinct database_name
from msdb.dbo.backupset
group by database_name
having datediff(minute, max(backup_start_date),getdate()) < @numOfMinutes
)
Job step which need to add in Backup User Databases job
Exec admin.dbo.uspMonitorBackups ‘D’,24
The Stored procedure accepts two parameters. The first parameter is type of database backup and second parameter is number of hours
No comments:
Post a Comment