Sunday, August 29, 2010

Missing backup script for SQL 2005 and 2008

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