0

We have long saved our hourly transaction log backups throughout the day by starting a new t-log file nightly, after the full backup is complete, and then appending to it hourly for the rest of the day.

Recently I have moved the nightly full backups to Azure blob storage which is working great, but the t-logs write the first file and after that are not able to append. 1 minute of googling confirmed that append is not supported on either block blobs nor page blobs (I had already tried it both ways to no avail) but also showed that there is a third type of blob, the Append Blob.

That sure sounds like the ticket! But I can't figure out how to specify which type of blob to use.

To switch between Block Blob and Page Blob I change which credential is being used (SAS token or shared key) but not sure how to use Append Blob, or whether that's just plain not supported for SQL backups. I'm guessing it comes down to how the initial blob/file is created, but have yet to find any info online about whether what I'm trying to do there is possible or not.

This is MS SQL Server 2022 and the backup is currently performed by an agent job, not a maint plan.

Improve this question
4
  • I'm curious what mechanism do you use to append? i.e. what is the content of the SQL Agent job that does this? (i.e. the T-SQL being used). Do you get an error when trying to append? All I could see was that Append might need a different API (that mightn't be supported by SQL Agent) Commented Sep 17, 2023 at 23:20
  • @Nick.Mc The filename for the t-log backup contains the date, and is generated as the final step of the nightly full backup job "WITH INIT". Then, the hourly job uses the same filename format (meaning the filename will be the same until the date changes) but using "WITH NOINIT". When backing up to disk, since NOINIT is specified and the backup file already exists, it will append. When backing up to blob storage I get: The file [blob url to file] exists on the remote endpoint, and WITH FORMAT was not specified. Backup cannot proceed. BACKUP LOG is terminating abnormally. Commented Sep 18, 2023 at 13:23
  • Reading around, apparently this might work if you pre create an empty append blob beforehand. This might have some useful powershell that will let you do it 4sysops.com/archives/… Commented Sep 18, 2023 at 23:51
  • 1
    @Nick.Mc Thanks, that looked promising. I tried creating the empty blob in advance as an Append Blob, and could see it in the storage explorer listed as an Append Blob but unfortunately when I try to write the t-log to it, I still get the same errror, "The file t-log-test.bak exists on the remote endpoint, and WITH FORMAT was not specified. Backup cannot proceed." I have gone back to writing the hourly t-log backup to disk for now. Commented Sep 25, 2023 at 23:44

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.