Cannot catch the error when the backup file is in use

zynrgzynrg Posts: 16
edited October 23, 2008 5:01PM in SQL Backup Previous Versions
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

Comments

  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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
  • peteypetey Posts: 2,358 New member
    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 (database) from: 
      e:\temp\pubs.sqb
     
    Error 580: Failed to open file.  Message:  (Cannot open file "e:\temp\pubs.sqb". The process cannot access the file because it is being used by another process.)
     
    SQL Backup exit code: 580
    

    Also note that exit code 580 is returned, not 500.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Hi Petey,

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

    Thanks, :D

    Neo
Sign In or Register to comment.