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)
CREATE EVENT SESSION ErrorCatcher ON SERVER
ADD EVENT sqlserver.error_reported (WHERE severity >= 16)
ADD TARGET package0.ring_buffer
WITH (
EVENT_RETENTION_MODE = NO_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 1 SECONDS
)
ALTER EVENT SESSION ErrorCatcher ON SERVER STATE = START
BEGIN TRY
BACKUP DATABASE MyDatabase
TO DISK = 'C:\MyDatabase.bak'
WITH INIT,
NAME = 'MyDatabase-FullBackup',
STATS = 5;
END TRY
BEGIN CATCH
DECLARE @target_data XML
WAITFOR DELAY '00:00:01' -- Wait for the MAX_DISPATCH_LATENCY time
SELECT @target_data = CAST(xet.target_data AS xml)
FROM sys.dm_xe_session_targets AS xet
JOIN sys.dm_xe_sessions AS xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'ErrorCatcher'
SELECT
n.value('(@name)[1]', 'varchar(50)') AS event_name,
n.value('(@package)[1]', 'varchar(50)') AS package_name,
n.value('(@id)[1]', 'int') AS id,
n.value('(@version)[1]', 'int') AS version,
DATEADD(hh,
DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
n.value('(data[@name="error"]/value)[1]', 'int') as error,
n.value('(data[@name="severity"]/value)[1]', 'int') as severity,
n.value('(data[@name="duration"]/value)[1]', 'int') as state,
n.value('(data[@name="user_defined"]/value)[1]', 'varchar(5)') as user_defined,
n.value('(data[@name="message"]/value)[1]', 'varchar(max)') as message
FROM @target_data.nodes('RingBufferTarget/event') AS q(n);
END CATCH
ALTER EVENT SESSION ErrorCatcher ON SERVER STATE = STOP
DROP EVENT SESSION ErrorCatcher ON SERVER
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.
So in Configuration Manager, you configured the SQL Agent service account, correct? That is the account that will execute SQL Agent jobs, by default.
This query will verify your settings:
SELECT servicename, service_account
FROM sys.dm_server_services
go
Do you get the error when executing the job via a schedule, when executing manually, or both?
If you are using T-SQL commands to perform the backup, what happens when you paste the commands into a query window and manually execute? Can you reproduce the error there as well?
Best Answer
Dan,
I'm assuming the account running the agent job is a sysadmin. In this case, the
DEVinstance will need to have its' service account or service sid for theDATABASE ENGINEgiven access to the folder.SQL Server agent doesn't do the restore, it connects to SQL Server and impersonates the account if non sysadmin and then continues. In this case you're connecting to the database engine and issuing a
RESTORE DATABASEcommand which will be processed by the engine. SQL Agent isn't touching the backups, the engine is.Edit: Explaining the posted agent output
This is letting you know that the Agent job was executed as the SQLAgent$MyAppDEV local account. This is most likely due to the job owner being sysadmin.
Here is the actual error, we can't open the file. We don't know why yet.
This is explaining why we can't open the file, the OS returned error 5. This can be validated by checking the OS return code by executing
net helpmsg 5on a command prompt.Some extra notes about what is happening, but this isn't relevant to the actual error or issue.