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
No comments:
Post a Comment