OK Simple-Talkers I have a interesting situation driving be nuttier than squirrel poo.<\/p>\n
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<\/u> 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.<\/p>\n
Do I ask too much of the RDBMS I love so?!?<\/p>\n
–STEP 1 BACK UP LOGS<\/strong><\/p>\n DECLARE @folder_name nvarchar(500) DECLARE @dirtree table (subdirectory nvarchar(255), depth int)<\/p>\n SET @folder_name = ‘\\SQLBackupBackup’ DECLARE backup_cur CURSOR FOR OPEN backup_cur<\/p>\n FETCH NEXT FROM backup_cur INTO @name<\/p>\n WHILE @@FETCH_STATUS = 0 IF NOT EXISTS (SELECT 1 FROM @dirtree WHERE subdirectory = @name) SET @file = @folder_name + ” + @name + ‘_tlog_’ + CONVERT(NVARCHAR, GETDATE(), 112) + LEFT(REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ‘:’, ”), 4)+ ‘.trn’ INSERT iDBA.backups.db_log_backups FETCH NEXT FROM backup_cur INTO @name CLOSE backup_cur –STEP 2 DELETE EXPIRED BACKUP FILES<\/strong><\/p>\n BEGIN TRAN DECLARE del_cur CURSOR FOR OPEN del_cur WHILE @@FETCH_STATUS = 0 BEGIN CATCH SELECT @err_msg = ERROR_MESSAGE() , @err_sev = ERROR_SEVERITY(), @err_state = ERROR_STATE() UPDATE iDBA.backups.db_log_backups FETCH NEXT FROM del_cur INTO @backup_file CLOSE del_cur IF @@TRANCOUNT > 0 COMMIT TRAN<\/p>\n","protected":false},"excerpt":{"rendered":" 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……<\/p>\n","protected":false},"author":199104,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-2561","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2561","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/199104"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=2561"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2561\/revisions"}],"predecessor-version":[{"id":41571,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2561\/revisions\/41571"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2561"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2561"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2561"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2561"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
DECLARE @name sysname
DECLARE @file nvarchar (1000)<\/p>\n
INSERT @dirtree
EXEC master.sys.xp_dirtree @folder_name<\/p>\n
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<\/p>\n
BEGIN
SET @folder_name = ‘\\SQLBackupBackup’ + @name<\/p>\n
EXECUTE master.dbo.xp_create_subdir @folder_name<\/p>\n
BACKUP LOG @name
TO DISK = @file
WITH RETAINDAYS = 1<\/p>\n
VALUES (@name, @file, GETDATE(), DATEADD(dd, 1, GETDATE()), NULL)<\/p>\n
END<\/p>\n
DEALLOCATE backup_cur<\/p>\n
DECLARE @backup_file nvarchar(1000)<\/p>\n
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<\/p>\n
FETCH NEXT FROM del_cur INTO @backup_file<\/p>\n
BEGIN
BEGIN TRY
EXEC master.sys.xp_delete_file 0, @backup_file
PRINT CAST(‘Deleted File: ‘ + @backup_file AS nvarchar(120))
END TRY<\/p>\n
IF @@ERROR <> 22049
BEGIN
DECLARE @err_msg NVARCHAR (2000)
DECLARE @err_sev INT
DECLARE @err_state INT<\/p>\n
RAISERROR (@err_msg, @err_sev, @err_state)
END
ELSE
BEGIN
PRINT CAST(‘Missing File: ‘ + @backup_file AS nvarchar(120))
END
END CATCH<\/p>\n
SET backup_removed_dt = GETDATE()
WHERE backup_full_file_nm = @backup_file<\/p>\n
END<\/p>\n
DEALLOCATE del_cur<\/p>\n