Way to save the other error message in sql server

user_9286749 0
Viewed 660

I Just want to save this error message in database

So I run this script

BEGIN TRY
        RESTORE HEADERONLY FROM DISK = 
        'C:\ZZ_SQLBACKUP\DBABackupTracer_20181107_230PM_F_2.BAK'
END TRY
BEGIN CATCH
        SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_LINE () AS ErrorLine  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_MESSAGE() AS ErrorMessage; 
END CATCH

So the query gave me this result

But the error message "The volume on device 'C:\ZZ_SQLBACKUP\DBABackupTracer_20181107_230PM_F_2.BAK' is empty." was not included inside the column

Is there anyway to save that sentence?

Thank you in advanvce :)

sql-server
1 Answers

There is a way to script it, but it is a bit unconventional, and you need admin rights to run the dbcc command. You cannot use a try..catch block, because the error you want to capture is a client-only message. So, the message must be sent back to the client. You can then pull the last output sent to the client and place it in a table somewhere for analysis. For instance:

RESTORE HEADERONLY FROM DISK = 'C:\ZZ_SQLBACKUP\DBABackupTracer_20181107_230PM_F_2.BAK'
if @@error = 3013
begin
  declare @tbl table (err_msg nvarchar(max))

  insert into @tbl
  execute ('dbcc outputbuffer(@@spid)')

  select top 50 * from @tbl
end

If the backup fails, then the output buffer is captured and sent to the table. Otherwise the output buffer is ignored. I included select top 50 * from @tbl so you could see how the output buffer is formatted. It isn't pretty, but it does include the full text of the error.

If you want to save the error in a permanent table, you will likely want other columns too, so that you can pull just the data for the most recent error(s). Change select top 50 * from @tbl to something like:

insert into your_error_log_table (row_id, database_name, error_date, error_msg)
select row_number() over (order by (select 1)), 'your db name', getdate(), err_msg
from   @tbl

Another solution would be to script the backups to run from a Command Prompt or Power Shell and pipe/capture the output to a log file. That would certainly make the error message more readable.

user_8220720
Related