Cannot catch the error when the backup file is in use

zynrg · October 21, 2008 5:17PM

I have my stored procedures based on dbo.sqlbackup to restore databases, and they work fine except during backup files are in use.

I cannot catch the error because it returns a text message instead of a rowset as usual.

The process cannot access the file because it is being used by another process. SQL Backup exit code: 500 [SQLSTATE 42000] (Error 1). The step failed

SQL Backup version 5.2.0.2825
Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Please help!

Thank,

Neo

petey · October 22, 2008 2:46AM

The sqlbackup extended stored procedure can return 2 output values, a SQL Backup exit code and a SQL Server error code e.g.

DECLARE @exitcode INT
DECLARE @sqlerrorcode INT

EXEC master..sqlbackup '-sql "..."', @exitcode OUTPUT, @sqlerrorcode OUTPUT

An exit code value of 0 indicates no errors or warnings. An exit code value less than 500 indicates a warning i.e. the backup or restore succeeded, but a post-backup or post-restore process failed e.g. file deletion, copying, e-mailing etc. An exit code of 500 or more indicates an error.

The SQL Server error code is the value returned by SQL Server if there were any server errors.

You can use these 2 output values to detect errors and warnings in your stored procedure.

zynrg · October 22, 2008 1:59PM

Hi Petey,

Thanks for your quick answer.

I can catch the exit code, but the exit code is 500 (General SQL Backup error). It doesn't indicate anything.

Generally, I use a variable table to catch errors, such as

Insert into @ErrorTable EXEC master..sqlbackup '-sql "..."', @exitcode OUTPUT, @sqlerrorcode OUTPUT

It works perfectly; however, it doesn't return a dataset only when the backup file is used by another process.

Is there any way I can catch the error message?

Neo

petey · October 23, 2008 3:10AM

Could you please explain what sort of lock is being held on the file? On my system running SQL Backup 5.2, when a file is locked for exclusive access by another application, the following is returned in a result set when I attempt to restore from the file:

Restoring pubs &#40;database&#41; from: 
  e:\temp\pubs.sqb
 
Error 580: Failed to open file.  Message:  &#40;Cannot open file "e:\temp\pubs.sqb". The process cannot access the file because it is being used by another process.&#41;
 
SQL Backup exit code: 580

Also note that exit code 580 is returned, not 500.

zynrg · October 23, 2008 5:01PM

Hi Petey,

You are right. I will catch the exit code 580 and deal with it specificly.

Thanks,

Neo

Cannot catch the error when the backup file is in use

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk