OK Simple-Talkers I have a interesting situation driving be nuttier than squirrel poo.

I have the following 2 steps in a scheduled SQL job I use to backup logs.  If for some reason a log file does not exist it will throw the error 22049.  I trap for it and want it to proceed with the remainder of the step before completing the job.  The issue I have is that if any error is raised in a SQL job step you have limited options: Quit Reporting Success, Quit Reporting Failure, or Go To Step X.  I need it to complete the step before making that decision.  That is not one of the options.  The action occurs on the first error – whether or not the error is handled gently.  If any error occurs the step stops.

Do I ask too much of the RDBMS I love so?!?

–STEP 1  BACK UP LOGS

DECLARE @folder_name nvarchar(500)
DECLARE @name sysname
DECLARE @file nvarchar (1000)

DECLARE @dirtree table (subdirectory nvarchar(255), depth int)

SET @folder_name = ‘\SQLBackupBackup’
   
INSERT @dirtree
EXEC master.sys.xp_dirtree @folder_name

DECLARE backup_cur CURSOR FOR
 SELECT name
 FROM master.sys.databases
 WHERE name NOT IN (‘master’, ‘model’, ‘msdb’, ‘tempdb’)
  AND recovery_model_desc <> ‘SIMPLE’
  AND DATEDIFF(day, create_date, GETDATE()) >= 1  – Must perform 1 full backup before you can do log backups.
  AND name NOT IN
   (
   SELECT database_nm
   FROM iDBA.backups.ignore_databases
   )
 ORDER BY name

OPEN backup_cur

FETCH NEXT FROM backup_cur INTO @name

WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @folder_name = ‘\SQLBackupBackup’ + @name

  IF NOT EXISTS (SELECT 1 FROM @dirtree WHERE subdirectory = @name)
   EXECUTE master.dbo.xp_create_subdir @folder_name

   SET @file = @folder_name + ” + @name + ‘_tlog_’ + CONVERT(NVARCHAR, GETDATE(), 112) + LEFT(REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ‘:’, ”), 4)+ ‘.trn’
   
   BACKUP LOG @name
   TO DISK = @file
   WITH RETAINDAYS = 1

   INSERT iDBA.backups.db_log_backups
   VALUES (@name, @file, GETDATE(), DATEADD(dd, 1, GETDATE()), NULL)

   FETCH NEXT FROM backup_cur INTO @name
  END

CLOSE backup_cur
DEALLOCATE backup_cur

–STEP 2 DELETE EXPIRED BACKUP FILES

BEGIN TRAN
 DECLARE @backup_file nvarchar(1000)

 DECLARE del_cur CURSOR FOR
  SELECT backup_full_file_nm
  FROM iDBA.backups.db_log_backups
  WHERE database_nm NOT IN (‘master’, ‘model’, ‘msdb’, ‘tempdb’)
   AND backup_expiration_dt <= GETDATE()
   AND backup_removed_dt IS NULL
  ORDER BY database_nm

 OPEN del_cur
  
 FETCH NEXT FROM del_cur INTO @backup_file

 WHILE @@FETCH_STATUS = 0
  BEGIN
   BEGIN TRY
    EXEC master.sys.xp_delete_file 0, @backup_file
    PRINT CAST(‘Deleted File:  ‘ + @backup_file AS nvarchar(120))
   END TRY

   BEGIN CATCH
    IF @@ERROR <> 22049
     BEGIN
      DECLARE @err_msg NVARCHAR (2000)
      DECLARE @err_sev INT
      DECLARE @err_state INT

      SELECT @err_msg = ERROR_MESSAGE() , @err_sev = ERROR_SEVERITY(), @err_state = ERROR_STATE()
      RAISERROR (@err_msg, @err_sev, @err_state)
     END
    ELSE
     BEGIN
      PRINT CAST(‘Missing File:  ‘ + @backup_file AS nvarchar(120))
     END
   END CATCH

   UPDATE iDBA.backups.db_log_backups
   SET backup_removed_dt = GETDATE()
   WHERE backup_full_file_nm = @backup_file

   FETCH NEXT FROM del_cur INTO @backup_file
  END

 CLOSE del_cur
 DEALLOCATE del_cur

IF @@TRANCOUNT > 0 COMMIT TRAN