0

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?

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.

1

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')
2
  • As per the question, the goal is to have one backup file per database per day. I'm trying to minimise have so many files.
    – EvilDr
    May 14 at 16:01
  • That's not possible with backup to Azure Blob Storage, as you can't append new backups to a single file. You're free to put the log backups in a "subfolder" by seperating the day and time by '/'. eg /backups/mydb/20210514/tlog/tlog-02:15:00.bak May 14 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.