DECLARE @DbName SYSNAME; SET @DbName = 'MyDatabase'; BACKUP DATABASE @DbName TO DISK = 'C:\MyDatabase.bak' WITH INIT, NAME = 'MyDatabase-FullBackup', STATS = 5;
This, sure enough, is a problematic code, given you're running under NETWORK SERVICE privileges. The output I am getting from it (because I am trying to write to c:\ to which NETWORK SERVICE doesn't have write access) is following when running in SSMS:
Msg 3201, Level 16, State 1, Line 4
Cannot open backup device 'C:\MyDatabase.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 4
BACKUP DATABASE is terminating abnormally.
Both surface in SSMS's Query Analyzer output as exceptions, but it looks like (maybe I'm talking gibberish here) that the first filesystem-related one is handled internally by the BACKUP statement which then replaces the message with a more generic "backup is terminating abnormally".
If I then try to handle the exceptions in my script like so
BEGIN TRY DECLARE @DbName SYSNAME; SET @DbName = 'MyDatabase'; BACKUP DATABASE @DbName TO DISK = 'C:\MyDatabase.bak' WITH INIT, NAME = 'MyDatabase-FullBackup', STATS = 5; END TRY BEGIN CATCH SELECT ERROR_NUMBER() , ERROR_MESSAGE() END CATCH
then I am only intercepting (well, in this case only showing with SELECT) the 3013 exception, obviously.
Question: is it at all possible to programmatically access the first exception (as it makes far better sense for logging problems during the script execution)?