SQL Server 2005 - Error_Message() not showing full message

Question

I have encapsulated a backup database command in a Try/Catch and it appears that the error message is being lost somewhere. For example:

BACKUP DATABASE NonExistantDB TO DISK = 'C:\TEMP\NonExistantDB.bak'

..gives error:
Could not locate entry in sysdatabases for database 'NonExistantDB'. No entry found with that name. Make sure that the name is entered correctly. BACKUP DATABASE is terminating abnormally.

Whereas:

BEGIN TRY
    BACKUP DATABASE NonExistantDB TO DISK = 'C:\TEMP\NonExistantDB.bak'
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH

... only gives error: BACKUP DATABASE is terminating abnormally.

Is there a way to get the full error message or is this a limitation of try/catch?

Answer 1

It's a limitation of try/catch.

If you look carefully at the error generated by executing

 BACKUP DATABASE NonExistantDB TO DISK = 'C:\TEMP\NonExistantDB.bak'

you'll find that there are two errors that get thrown. The first is msg 911, which states

Could not locate entry in sysdatabases for database 'NonExistantDB'. No entry found with that name. Make sure that the name is entered correctly.

The second is the 3013 message that you are displaying. Basically, SQL is only returning the last error.

Answer 2

It is a limitation, that I just ran into myself, of the try/catch block in SQL 2005. I don't know if it still exists or not in 2008.

SQL 2005 Error Handling