Click here to monitor SSC

Do I Ask Too Much of My Beloved RDBMS?

Published 16 October 2007 4:45 pm

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

4 Responses to “Do I Ask Too Much of My Beloved RDBMS?”

  1. Tim Ford says:

    Ahh, if I only coded my CATCH properly…

    This works much better:

    BEGIN CATCH
    IF @@ERROR <> 22049 –File Missing Error
    BEGIN
    PRINT @@error
    END
    ELSE
    BEGIN
    PRINT CAST(‘Missing File: ‘ + @backup_file AS nvarchar(120))
    END
    END CATCH

  2. Phil Factor says:

    I know that there are a lot of scripts like this doing the rounds, but surely xp_delete_file is an undocumented extended stored procedure which is due for the chop?

    I actually set about trying to write a Scripting.FileSystemObject routine in a stored procedure to do the job, but discovered that the FILES collection doesn’t accept an index value, damn it, so it can’t be used in SQL Server. I have to confess I use xp_cmdshell to do this sort of job, but getting the date from a DIR command is potentially scarey stuff because the routine will fail if you change the date format of the server from the Control panel. But then I stand over my servers with a loaded Service Revolver. Of course SQL Backup does this for you (They built in all the nice stuff from the late lamented Maintainance Plan)

  3. Haywood says:

    Phil, have a look at the FOR formatting options. They’re quite handy for getting file timestamps instead of trying to hack up the DIR output – been there, got the t-shirt.

    FOR %I IN (“YourFileNameHere”) DO echo %~nxtI

Leave a Reply