Script for Database Backup Failure

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 Database Backup Failure

Author

Topic

vaddi
Posting Yak Master

145 Posts

Posted - 2006-09-15 : 13:53:56

Hello

I have a script which mails the dba when a database bakcup fails.

The script is :

declare @to VARCHAR(255),
@subject VARCHAR(255),
@message VARCHAR(8000),
@date varchar(100)

set @message = ''

SET NOCOUNT ON;
DECLARE
@rv INT,
@from VARCHAR(64),
@server VARCHAR(255);
SELECT
@from = 'testsql2000@is.depaul.edu',
@server = 'smtp.depaul.edu';

set @date = convert(varchar(100), getdate(),109)
Select @message = @message + char(13) + Char(13) + @@servername + '-'+ 'Backup Status Failed' + Char(13)+ '-' + @date

EXEC @rv = dbo.xp_smtp_sendmail
@from = N'Testsql2000@depaul.edu',
@to = N'dvaddi@depaul.edu',
@message = @message,
@subject = N'Test Database backup Failure',
@server = @server;
GO

I would like to know how I can change the script so, that I get the database name , or the particular database name whose backup has failed.
I am giving the above script as step 2 , in the job of the database backup.

When I give the parameter , db_name() , its giving me Master database name. SO I wanted to know how I can change it to get the name of the particular database.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-15 : 14:23:30

db_name() isn't going to give you the name of the database whose backup failed.

How are you backing up the database? Via a maintenance plan, customer backup stored procedure, tape backup software, etc...?

Tara Kizer

vaddi
Posting Yak Master

145 Posts

Posted - 2006-09-15 : 14:28:00

The format i am using is scheduling the job , giving the format:

BACKUP DATABASE [TEST] TO DISK = N'D:\mssql\backup\test\test_full.bak' WITH INIT , NOUNLOAD , NAME = N'Test full backup', SKIP , STATS = 10, NOFORMAT

Thanks

anilkdanta
Starting Member

25 Posts

Posted - 2006-09-18 : 07:15:19

Are you going to execute this job for multiple databases? Or a single database ?

vaddi
Posting Yak Master

145 Posts

Posted - 2006-09-18 : 10:58:48

I will be executing this job on mulitple databases. But will be implementing as a step 2 , in each job of the database backup.

anilkdanta
Starting Member

25 Posts

Posted - 2006-09-20 : 02:59:54

You can either way in Your Step1

1)If you are comfortable with DTS packages and looping its pretty easy.
OR
2)Otherwise select database names from the master.dbo.sysdatabases and loop through each record with a cursor or without cursor and execute the Backup command for each record (databasename).

Which way you wanted to do?

vaddi
Posting Yak Master

145 Posts

Posted - 2006-09-20 : 14:05:57

I would like to work on with the DTS package.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources