16 October 2007

Do I Ask Too Much of My Beloved RDBMS?

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

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 1715 times – thanks for reading.

  • Rate
    [Total: 0    Average: 0/5]
  • Share

Related articles

Also in Blogs

Ten Years Later

It’s hard to believe, but Simple Talk has now been going for over ten years. Thanks to brilliant pieces from our writers, hard work from the team here, and countless valuable contributions from you, our readers, we’re currently receiving one million page views a month, and sitting on a hefty 2,500 articles from over 370 … Read more

Join Simple Talk

Join over 200,000 Microsoft professionals, and get full, free access to technical articles, our twice-monthly Simple Talk newsletter, and free SQL tools.

Sign up