Script for the backup failure - mail

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Script for the backup failure - mail

Author

Topic

vaddi
Posting Yak Master

145 Posts

Posted - 2006-08-25 : 10:59:47

Hello

I have script which gives all the backup status to the dba mail box. How can I modify this to get only when the backup fails.

USE MASTER
GO

DECLARE @dbname VARCHAR(100)
Declare @Status varchar(100)
Declare @Message VARCHAR(8000)

set nocount on

set @Message = ''

select
bmf.physical_device_name,
RIGHT(bmf.physical_device_name, CHARINDEX('\', REVERSE(bmf.physical_device_name))-1) as physical_device_file,
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
bs.type,
bs.first_lsn,
bs.last_lsn,
bs.checkpoint_lsn,
bs.database_backup_lsn
into #backup
from
msdb.dbo.backupset bs,
msdb.dbo.backupmediafamily bmf
where bmf.media_set_id = bs.media_set_id
and bs.backup_finish_date is not null
AND bs.type = 'D'
AND bs.backup_start_date = (select max(backup_start_date) from msdb.dbo.backupset WHERE type = bs.type and database_name = bs.database_name)
order by bs.database_name, bs.backup_start_date asc

select @message = @message + char(13) + Char(13) + 'Backup Status' + Char(13)

DECLARE GetBackup CURSOR FOR
select database_name, backup_finish_date from #backup order by database_name

OPEN GetBackup
FETCH NEXT FROM GetBackup INTO @dbname, @Status

WHILE @@FETCH_STATUS = 0
BEGIN
select @message = @message + @dbname + ' backup up on ' + @Status + Char(13)
FETCH NEXT FROM GetBackup INTO @dbname, @Status
END
Close GetBackup
Deallocate GetBackup

drop table #backup

print @message

EXEC master.dbo.xp_smtp_sendmail
@FROM = N'testsql2000@is.depaul.edu',
@TO = N'dvaddi@depaul.edu',
@server = N'smtp.depaul.edu',
@subject = N'Status of sqlserver!',
@type = N'text/html',
@message = @message

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources