Created
November 30, 2021 14:49
-
-
Save Pauwelz/d4c2c4bef12844f246f4fe8fd7c4f220 to your computer and use it in GitHub Desktop.
Store DTA Backup in Azure Blob Storage
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| USE [BizTalkDTADb] | |
| GO | |
| /****** Object: StoredProcedure [dbo].[dtasp_BackupTrackingDatabase] Script Date: 11/30/2021 2:48:23 PM ******/ | |
| SET ANSI_NULLS ON | |
| GO | |
| SET QUOTED_IDENTIFIER ON | |
| GO | |
| ALTER PROCEDURE [dbo].[dtasp_BackupTrackingDatabase] | |
| @nvcFolder nvarchar(1024), | |
| @nRetryRowID int, | |
| @fSuccess int OUTPUT | |
| AS | |
| declare @dtNow datetime | |
| declare @localized_string_BackupFailed nvarchar(256) | |
| set @localized_string_BackupFailed = N'A failure occurred while backing up the database.' | |
| set @dtNow = GetUTCDate() | |
| set @fSuccess = 0 --this means failure. | |
| --we need to do a backup of the database | |
| declare @DBName nvarchar(128), @ServerName nvarchar(256), @nvcDT nvarchar(32), @BackupLocation nvarchar(2000), @separator nvarchar(1)=N'\', @DestinationType nvarchar(10)=N'DISK' | |
| IF left( ltrim( @nvcFolder ), 4 ) = N'http' | |
| SET @separator=N'/' | |
| SET @DestinationType=N'URL' | |
| set @nvcDT = replace(@dtNow, ' ', '_') | |
| set @nvcDT = replace(@nvcDT, ':', '_') | |
| select @DBName = db_name(), @ServerName = replace( cast( isnull( serverproperty('servername'), '' ) as nvarchar ), '\', '_' ) /* this will be a file path */ | |
| if right( @nvcFolder, 1 ) = @separator | |
| SET @BackupLocation=@nvcFolder + @ServerName + N'_' + @DBName + N'_' + @nvcDT + N'.bak' | |
| else | |
| SET @BackupLocation=@nvcFolder + @separator + @ServerName + N'_' + @DBName + N'_' + @nvcDT + N'.bak' | |
| --select @BackupLocation | |
| EXEC ('Backup Database [' + @DBName + '] to ' + @DestinationType + '=N''' + @BackupLocation + '''') | |
| if @@ERROR <> 0 | |
| BEGIN | |
| if (@nRetryRowID = -1) --this is not a retry of a previously failed backup | |
| INSERT INTO dta_ArchiveHistory (dtTimeStamp, dtLastFailure, nFailureCount, nSuccess, nvcBackupLocation) VALUES (@dtNow, @dtNow, 1, -1, @BackupLocation) | |
| else | |
| UPDATE dta_ArchiveHistory set dtLastFailure = @dtNow, nFailureCount = nFailureCount + 1, nSuccess = -1, nvcBackupLocation = @BackupLocation WHERE nID = @nRetryRowID | |
| RAISERROR(@localized_string_BackupFailed, 16, 1) | |
| END | |
| else | |
| BEGIN | |
| if (@nRetryRowID = -1) | |
| INSERT INTO dta_ArchiveHistory (dtTimeStamp, nvcBackupLocation) VALUES (@dtNow, @BackupLocation) | |
| else | |
| UPDATE dta_ArchiveHistory set dtTimeStamp = @dtNow, dtLastFailure = null, nFailureCount = 0, nSuccess = 0, nvcBackupLocation = @BackupLocation WHERE nID = @nRetryRowID | |
| set @fSuccess = 1 --we successfully backed up the database | |
| END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment