All we need is an easy explanation of the problem, so here it is.
My goal is to have one backup file per database per day, with 15 minute recovery objective. I am scheduling agent jobs as follows:
- Once a day at 00:00, create a full backup of each database specified
- Every 15 minutes, create a transaction log backup of each database specified
Ignoring all the code to iterate through the required databases, this is the core logic of part 1 above:
DECLARE @dbName nvarchar(50) = (SELECT DatabaseName FROM #DbsToBackup WHERE (Id = @current)); DECLARE @dbNameAndDate nvarchar(200) = @dateString + '_' + @dbName; DECLARE @dbSpecificContainerUrl nvarchar(MAX) = @containerUrl + @dbNameAndDate + '.bak'; DECLARE @verifyError nvarchar(200) = N'Verify failed. Backup information for database ' + @dbName + ' not found.'; BACKUP DATABASE @dbName TO URL = @dbSpecificContainerUrl WITH CREDENTIAL = @containerCredential ,NOINIT ,NAME = @dbNameAndDate ,COMPRESSION ,CHECKSUM ;
Then, the transaction log backup logic is as follows (same steps for grabbing the database name, date strings, etc.:
BACKUP LOG @dbName TO URL = @dbSpecificContainerUrl WITH CREDENTIAL = @containerCredential ,NOINIT ,NAME = @dbNameAndDate ,SKIP ,COMPRESSION ,CHECKSUM ;
Part 1 works on the first attempt, but part 2 (and any subsequent re-run of part 1) immediately fails with this:
A nonrecoverable I/O error occurred on file "https://…..Test.bak:"
The file https://….Test.bak exists on the remote endpoint, and WITH
FORMAT was not specified. Backup cannot proceed.. [SQLSTATE 42000] (Error 3271) BACKUP LOG is terminating abnormally. The step failed.
I get that the .bak file is already there, but I thought that
NOINIT forced an append which would add the transaction log data into the existing .bak file.
I’m probably missing something simple but can somebody please advise?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Based on the documentation, it seems that is not supported.
Appending to existing backup blobs is not supported. Backups to an
existing blob can only be overwritten by using the WITH FORMAT option.
However, when using file-snapshot backups (using the WITH
FILE_SNAPSHOT argument), the WITH FORMAT argument is not permitted to
avoid leaving orphaned file-snapshots that were created with the
original file-snapshot backup.
@dbSpecificContainerUrl contains the
@dateString (preferably in ISO 8610 format) so each log backup gets its own file.
set @dateString = FORMAT(GetUtcDate(),'yyyy-MM-ddTHH:mm:ssZ')
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂