SQL Server backup to URL fails if file exists

Question

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?

Andrew Shepherd Andrew Shepherd · Accepted Answer · 2017-09-28T05:17:38

It's correct in stopping you, because you need to keep the first full backup. When you restore your database, you will

Restore from the full backup
Apply the differential backup

Your script should generate a unique name. I do this by appending the date and time to the backup filename:

DECLARE
   @now DATETIME,
   @datePrefix VARCHAR(MAX),
   @timePart VARCHAR(MAX),
   @backupFileName VARCHAR(MAX),
   @fullUrl VARCHAR(MAX),
   @blobServiceEndpoint VARCHAR(MAX)

SET @now = GETDATE();
SET @datePrefix = CONVERT(VARCHAR(8), GETDATE(), 112);
SET @timePart = RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(hh, @now)), 2) 
              + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(mi, @now)), 2)
              + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(ss, @now)), 2);
SET @backupFileName = 'MyDatabase_' + @datePrefix + @timePart + '.bak';
SET @blobServiceEndpoint = 'https://myaccount.blob.core.windows.net/';
SET @fullUrl = @blobServiceEndpoint + 'Container/' + @backupFileName;
BACKUP DATABASE [MyDatabase]' 
    TO URL = @fullUrl
    WITH CREDENTIAL = 'AzureDBBackupsContainer'
    , DIFFERENTIAL
    , COMPRESSION;

SQL Server backup to URL fails if file exists

1 Answers