2

I am attempting to backup a database to my Azure Blob storage. The databases are relatively large... between 1 and 30 Gig.

I have successfully backed up the first day, but on subsequent days, I get an error stating that the file already exists and that I need to use the WITH FORMAT.

But I need to do a differential backup, as the load of data on a full backup would be very slow and pricey.

I get the error "MyBackup.bak exists on the remote endpoint, and WITH FORMAT was not specified.".

What I am trying to do is:

BACKUP DATABASE [MyDatabase]' 
TO URL = 'https://myaccount.blob.core.windows.net/dbbackups/Container/', MyDatabase.bak', 
WITH CREDENTIAL = 'AzureDBBackupsContainer'
, DIFFERENTIAL
, COMPRESSION;

I have seen that a differential backup is supported when using URL backups.

Is this actually not possible, or am I doing something wrong? Do I need to somehow change the file name each day?

3

You are correct, this is not possible in Azure cloud. You can do this for premise backup which I highly discourage. It just makes your restore more complicated. But in Azure if file name already exists in your container only way to use that same name is to use WITH FORMAT which will create a new media set.

I tested this and I get following error message:

Msg 3296, Level 16, State 1, Line 1 The file https://storageacocunt/sqlbackfiles/testFull.bak exists on the remote endpoint, and WITH FORMAT was not specified. Backup cannot proceed. Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.

From Books online:

BACKUP (Transact-SQL) | Microsoft Docs

FORMAT

Specifies that a new media set be created. FORMAT causes the backup operation to write a new media header on all media volumes used for the backup operation. The existing contents of the volume become invalid, because any existing media header and backup sets are overwritten.

You can check this with RESTORE HEADERONLY command. If you take a fullbackup and then a differential WITH FORMAT, you will see backup type change from 1 to 5.

I suggest you create a new for each file.

This link has example about generating a new filename for each backup.

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.