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)?
Thank you!
Best Answer
You can use extended events to capture all errors in your session (this will include some internal errors in your case that the user doesn't get to see normally).
Use the following code to do this (SQL Server 2008 minimum)
Of course you can create the event session only once and just start/stop it on demand. The session is persistent and remains on the server until you drop it.
The ring buffer target is resident in memory and with default settings uses up to 4 MB to capture events before it starts deleting old events (on a FIFO basis). The buffer is also released when you stop the session.