SQL Server – Make Encrypted Differential Backup to Same Media

backupencryptionsql-server-2016sql-server-2017

I create encrypted backup as follow:

BACKUP DATABASE XXX 
TO DISK = 'C:\Backup\XXX.bak'  
WITH INIT, FORMAT,
     ENCRYPTION  (ALGORITHM = AES_256, SERVER CERTIFICATE = MyCertificate)

Then I try to add differential backup to same file:

BACKUP DATABASE XXX TO DISK = 'C:\Backup\XXX.bak'
WITH DIFFERENTIAL,
     ENCRYPTION  (ALGORITHM = AES_256, SERVER CERTIFICATE = MyCertificate)

but get error:

The backup cannot be performed because 'ENCRYPTION' was requested after the media was formatted with an incompatible structure. To append to this media set, either omit 'ENCRYPTION' or create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.

What should be done to store encrypted differential backups in a single file?

Upd:
I know how to get diff backup in another file. If it is not possible to get diff backup in the same file, where I can find documentation on this behaviour?

Best Answer

As Aaron noted, there's no reason to retain multiple differential backups, as you would only use the latest, and only as an optimization.

The limitation on one encrypted backup per file is in the main doc page for Encrypted Backups:

The following are restrictions that apply to the encryption options:

. . .

Appending to an existing backup set option is not supported for encrypted backups.

Backup Encryption

Related Question