Hi,As part of a log shipping routine that uses SQLBackup by Red-Gate, I have a cursor that performs the Tran Backups and moves them ready for restoring on the DR machine. I want to trap any errors in the backup process and email notification of this to the DBA's.In the code below you will see after the backup code I have attempted to use @@ERROR to trap any errors but this appears to always return 0 even if I force an error by including a non-existent database or similar. Can anyone help me find a way to trap an error if one occurs and successfully send the email please.USE MASTER;declare @dbname sysnamedeclare @cmd varchar(2000)declare @DateTag varchar(20) DECLARE @Msg varchar(200) DECLARE @MailSubject varchar(200)declare csr cursor forselect [DatabaseName] from master.dbo.LogShipping_Out where Active = 1 order by 1 open csr fetch next from csr into @dbname while @@fetch_status = 0beginset @DateTag = convert(varchar(8),getdate(),112)+replace(convert(varchar(10),getdate(),108),':','')set @cmd = N'exec master..sqlbackup ''-SQL "backup log [' + @dbname + '] to disk =[D:\TRN_BAKS\' + @dbname + '\LOG_' + @dbname + '_' + @DateTag + '.sqb] WITH COMPRESSION = 3, THREADCOUNT = 4, ERASEFILES = 168h, COPYTO = [\\AMCATDR\D$\TRLOG_LOADING\AMCATSQL_LOGS\' + @dbname + ']" '''print @cmdexec (@cmd)IF (@@ERROR <> 0)BEGIN set @MailSubject = N'** LOG SHIPPING ERROR ** - ''' + @dbname + ''' Transaction Log Backup Failed' set @Msg = N'The Transaction Log Backup for database ''' + @dbname + ''' Failed.' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Data Admins' , @recipients = 'datateam@kkk.co.uk' , @subject = @MailSubject , @body = @Msg , @importance = 'high'; END fetch next from csr into @dbnameendclose csrdeallocate csr
Get error status from Backup procedure
Posted - 2008-09-30 : 09:24:22
