Backup TO URL WITH NOINIT fails because file already exists

All we need is an easy explanation of the problem, so here it is.

There’s a similar question here but I cannot work out the solution because the circumstances are slightly different, so please bear with me; I’ve been wresting with the official help docs all day.

My goal is to have one backup file per database per day, with 15 minute recovery objective. I am scheduling agent jobs as follows:

  1. Once a day at 00:00, create a full backup of each database specified
  2. 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.

Method 1

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.

Method 2

Ensure that @dbSpecificContainerUrl contains the @dateString (preferably in ISO 8610 format) so each log backup gets its own file.

eg

set @dateString = FORMAT(GetUtcDate(),'yyyy-MM-ddTHH:mm:ssZ')

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply