I have a workaround for the issue you are seeing. I too am dealing with backup/restore functions in SQL server where I need to have logic to take different steps as the actions are tried.

In my case, I am just restoring SQL tran logs from a folder on SQL server and saving the tran log file names with restore status into a SQL table. It is a script based tran log shipping method as well as can be used to migrate to new SQL servers where I can cut over in near real time.

Because the try/catch ends up just sending the last error and not the first error SQL spits out, I am creating the SQL statement - in my case, a tran log restore statement, and running that through xp_cmdshell to run the command on the OS. It becomes a SQLCMD statement and captures the results into a txt file. Then as soon as the statement is ran, I read the txt file back into SQL, into a temp table where I can select the results. So the errors are isolated in the SQLCMD and returned into the txt file.

Then with the results from the text file into a temp table, I can make decisions on if I need to mark the tranlogs restored in my table or mark them to retry later. If the file is too early to restore, I mark it as done and move on. If the file is too late, I can write that status to an email or text message. In my case, I will be writing the status to a dashboard where once an alert is triggered, I can spawn off a Service now ticket.

This works well for me as well as gives me total control and visibility into exactly what is happening as tran log files are restored. So I’m posting in hopes of helping someone. I know some people are not a fan of using xp_cmdshell and it is not allowed in PCI based environments.

declare @execsql nvarchar(500) --long string use for the cmds
declare @dbname nvarchar(50) --database name
declare @bkdir nvarchar(30) --backup folder-Where tranlog backup files 
are placed
declare @DB_TRAN nvarchar(50) -- the filename of the tran log backup 
-- file 
-- I am using a cursor looping thro LogShipping..T_TRAN_FILES 
-- I have created a database and table to hold restore info 
-- LogShipping..T_TRAN_FILES --
select @dbname = 'Prod_Data'
select @bkdir = 'm:\mssql\backup\TranShipping'
select @DB_TRAN = 'PROD_DATA_20230712141434.trn'

set @execsql = REPLACE('SQLCMD -E -S'+@@servername+' -Q "RESTORE LOG 
['+@dbname+'] FROM  DISK = N'''+@bkdir+'\'+@DB_TRAN+''' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  STATS = 10 " - 
o'+@bkdir+'\'+@DB_TRAN+'.txt','\\','\')
print @execsql
exec master..xp_cmdshell @execsql, NO_OUTPUT
set @execsql = REPLACE('type '+@bkdir+'\'+@DB_TRAN+'.txt','\\','\')
--print @execsql
create table #file ( line_number int identity,  line_contents 
nvarchar(4000))
insert #file
exec master..xp_cmdshell @execsql
if (select count(*) from #file where line_contents like '%which is too 
early to apply to the database%') >0
begin
update LogShipping..T_TRAN_FILES set RestoreStatus = 1 where FileName = 
@DB_TRAN 
and DatabaseName=@dbname and RestoreStatus=0
end
if (select count(*) from #file where line_contents like '%RESTORE LOG 
successfully processed%')>0
begin
update LogShipping..T_TRAN_FILES set RestoreStatus = 1 where FileName = 
@DB_TRAN 
and DatabaseName=@dbname and RestoreStatus=0
end
-- jump out no need to try any more in this database until reint or lost 
-- tran 
-- log is copied
if (select count(*)  from #file where line_contents like '%which is too 
recent to apply to the database%' or line_contents like '%Exclusive 
access could 
not be obtained because the database is in use%') >0
begin
drop table #file
--break -- I have this running inside a cursor. Break and update my 
--table.
end
drop table #file