When BACKUP DATABASE
generates an error, it actually generates two. Unfortunately TRY/CATCH
is not capable of capturing the first error; it only captures the second error.
I suspect your best bet to capture the real reason behind a failed backup is to automate your backups through SQLCMD (with -o
to send output to a file), SSIS, C#, PowerShell etc. All of which will give you much greater control over capturing all of the errors.
The SO answer in the comment suggests using DBCC OUTPUTBUFFER
- while it's possible, this does not seem like child's play at all. Feel free to have fun with this procedure from Erland Sommarskog's site, but this still doesn't seem to work well in combination with TRY/CATCH
.
The only way I seemed to be able to capture the error message with spGET_LastErrorMessage
is if the actual error does get thrown. If you wrap it in a TRY/CATCH
the error gets swallowed and the stored procedure does nothing:
BEGIN TRY
EXEC sp_executesql N'backup that fails...';
END TRY
BEGIN CATCH
EXEC dbo.spGet_LastErrorMessage;
END CATCH
In SQL Server < 2012 you can't re-raise the error yourself, but you can in SQL Server 2012 and newer. So these two variations work:
CREATE PROCEDURE dbo.dothebackup
AS
BEGIN
SET NOCOUNT ON;
EXEC sp_executesql N'backup that fails...';
END
GO
EXEC dbo.dothebackup;
EXEC dbo.spGET_LastErrorMessage;
Or in 2012 and above, this works, but to a large degree defeats the purpose of TRY/CATCH
, since the original error still gets thrown:
CREATE PROCEDURE dbo.dothebackup2
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
EXEC sp_executesql N'backup that fails...';
END TRY
BEGIN CATCH
THROW;
END CATCH
END
GO
EXEC dbo.dothebackup2;
EXEC dbo.spGET_LastErrorMessage;
In both of these cases, the error is still thrown to the client, of course. So if you're using TRY/CATCH
to avoid that, unless there is some loophole I'm not thinking of, I'm afraid you'll have to make a choice... either give the user the error and be able to capture details about it, or suppress both the error and the actual reason.
I had a similar problem when running powershell scripts from a sql server job.
If you do not have remote PowerShell enabled on both servers and
firewall access between the servers configured properly it will have
issues.
Run a PowerShell script on a different server - from inside a SQL Server job
the way I have solved this problem is through a proxy account.
I have created a proxy account in sql server,
that had all the relevat permissions to the remote server, folders and files that I needed and run the sql job as that account.
a proxy account in my case was a domain account, and I have granted this account the privileges on the remote server.
this might help with proxy:
How to get the AD account associated to a Proxy?
From this link below you can see:
powershell problem when running on a remote server - access is denied
As far as workarounds, there is this answer on SU. you can read the article in detail that the answer points to fully understand, but this is the main command to adjust permissions:
If you decide you want to allow others, what you do is run the
command:
Set-PSSessionConfiguration -Name Microsoft.PowerShell -showSecurityDescriptorUI
Notice that this action could have a serious impact on your system so
we ask you to confirm that you really want to do this.
Best Answer
Did you use RAISERROR with severity 16?
This is "user defined error" and should be picked up by SQL Server agent
Did you set the job step to retry (
Retry Interval
andRetry Attempts
)Finally, is the error terminating (severity 20) such that the CATCH block isn't hit?