SQL Server - Backup Error Message

Asked By dbapra

13-Feb-08 05:30 PM

Hi,

Im trying to capture the error message that is thrown while backup.

BACKUP DATABASE TestPradDB TO DISK =
'D:\SQL\Backup\TestPradDB_20080213_Full.bak'

I get the below msg when I try to back up my DB.

Msg 3202, Level 16, State 2, Line 1
Write on "D:\SQL\Backup\TestPradDB_20080213_Full.bak" failed: 112(There is
not enough space on the disk.)
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

I need to capture this msg and store it in a log table.
To do this I tried the below code...

SET @ErrorID = @@ERROR

SELECT @FailureReason = CONVERT(VARCHAR,Text)
FROM sys.messages m
INNER JOIN sys.syslanguages l
ON l.MsgLangID = m.Language_ID
AND l.Name = 'us_english'
WHERE m.Message_ID = @ErrorID

this query returns : %hs is terminating abnormally.

And also I see 2 error msgids in the msg on management studio
Msg 3202, and Msg 3013

However @@Error returns only the latest msgid ie 3013.
Is there a way to capture the first msgid ?

I believe there is a way to do this as I see the same msg being logged in
sql server log.

I assume I may have to build the exact string adding the exact path and the
text in sys.messages. But to do that I would need to capture both the MsgIDs.

What does the ID 112 in "Write on
space on the disk.)" mean? Is it OS level msgid?

Thanks,
Prad

13-Feb-08 06:04 PM

dbaprad (dbaprad@) writes:

Have you tried to run the BACKUP statement in TRY CATCH:

BEGIN TRY
BACKUP ...
END TRY
BEGIN CATCH
SELECT @failurereason = error_message()
END CATCH

You will still only get one of the errors. Which I don't know off hand.
If you need to get both, you will need to run the script from a client
program and use an API that is able to get both messages for you. If
you use SqlClient it should work, if you use the connection option
.FireUserErrorsAsInfoEventMesseages. (Please check the docs for the
exact name.)


Yes, that is an error from an operating-system call. You see this with

NET HELPMSG 112

(which is good when the text in parens only says "Error not found").


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

13-Feb-08 08:36 PM

Thanks for the reply...

Yes I tried TRY CATCH first but then it somewhat similar, I dont get a
conclusive msg as I get in management studio which made me think of
sys.messages and make use of @@Error to get the text...

Runnig the program from client app : Im not into any .Net kind of stuff and
also Im planning to create a scheduled job to do this...

Any TSQL command or system proc to get the text from the OS ErrID 112?

Thanks again...
Prad

14-Feb-08 05:34 PM

dbaprad (dbaprad@) writes:

You can run a command-line job from a scheduled job. You can also run
an Active-X task, but if you do VBscript, the error handling in ADO is
poor.


As I said in my previous post, you can use NET HELPMSG from the command
line. In the Platfrom SDK you can do the translation with FormatMessage.
I don't know if there is any .Net interface you can call from a CLR
stored procedure.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx