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 = '\\SQLBackup\Backup'
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 = '\\SQLBackup\Backup\' + @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