BACKUP DATABASE Failure generates 2 errors:Why? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

BACKUP DATABASE Failure generates 2 errors:Why?

Author

Topic

AlfieNoakes
Starting Member

14 Posts

Posted - 2003-09-30 : 08:21:09

I'm writing a stored procedure called CMN_BACKUP which will automatically backup a database.

It works well, but, if the BACKUP DATABASE command fails, it seems to raise TWO errors!

Server: Msg 911, Level 16, State 1, Procedure CMN_BACKUP, Line 39
Could not locate entry in sysdatabases for database 'CascadeCPARRP'. No entry found with that name. Make sure that the name is entered correctly.

Server: Msg 3013, Level 16, State 1, Procedure CMN_BACKUP, Line 39
BACKUP DATABASE is terminating abnormally.

When I use @@error, I get error code 3013, but I want 911, because it seems to have a far more helpful description.

Does anyone know why BACKUP DATABASE raises two errors, and also, how to get the first error, and not the second?

Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-30 : 11:23:14

Why don't you paste your sproc?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

AlfieNoakes
Starting Member

14 Posts

Posted - 2003-09-30 : 11:30:32

Ok Here it is:

(Please note that this procudure does work, but I'm causing an error on purpose by specifying a database that I know doesn't actually exist,to see if I can log the error)

------------------------------------------------

CREATE PROCEDURE dbo.CMN_BACKUP

@txtDatabaseName varchar(60),
@varFullBackupFilePath nvarchar(60),
@blnIncludeLog bit

AS

/*

CMN_BACKUP, written by Robert Graff

Bare in mind that the @varFullBackupFilePath variable is relative to the databsae machine,
not necessarily the machine you are running on.

*/

DECLARE @varBackupName varchar(60)
DECLARE @varDataFileName varchar(60)
SET @varBackupName = @txtDatabaseName + ' backup'
SET @varDataFileName = @txtDatabaseName + '_Data'

IF (@blnIncludeLog = 1)
BEGIN
--backup the Database, with Log

BACKUP DATABASE @txtDatabaseName
TO DISK = @varFullBackupFilePath WITH INIT , NOUNLOAD ,
NAME = @varBackupName, NOSKIP , STATS = 10, NOFORMAT

EXEC dbo.CMN_LOG @@ERROR, 'CMN_BACKUP',1,'RGraff'

END
ELSE
BEGIN

--Only back up the data (MDF) file

BACKUP DATABASE @txtDatabaseName
FILE = @varDataFileName
TO DISK = @varFullBackupFilePath WITH INIT , NOUNLOAD ,
NAME = @varBackupName, NOSKIP , STATS = 10, NOFORMAT

EXEC dbo.CMN_LOG @@ERROR, 'CMN_BACKUP',2,'RGraff'

END

GO

--------------------------------------

So the problem is that when I call CMN_LOG, @ERROR = 3013

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-30 : 11:38:32

I would guess that the backup calls another procedure to get info about the database. It is the child process that returns 911 error but then the calling process returns the 3013 error. @@error will only hold the last error adn I don't think there will be any way of getting the first one from sql. You could probably get it from a client app (VB?) though.
Maybe execute the backup using osql and put the output into a temp table or read the log file on completion?
Could also create a job with the backup command, execute it, look at sysjobhistory then delete the job.

All a lot of work to get the error message.
If your SP is called from the agent then the error messages should be there.
have you seen
http://www.nigelrivett.net/BackupAllDatabases.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources