Back up multiple databases to the same backup (BAK) file with encryption

Question

I have a script that we run on an ad-hoc basis that grabs the names of 15 databases, then iterates through that list to backup each one into a single .BAK file. This is then downloaded for use elsewhere (e.g. development). The key part of the TSQL script is as follows:

WHILE @current <= @end
BEGIN
    DECLARE @dbName nvarchar(50) = (SELECT DatabaseName FROM #DbList WHERE (OrderId = @current));
    DECLARE @backupFile nvarchar(200) = N'D:\Backups\' + @dateString + '_All.bak';  
    BACKUP DATABASE @dbName TO DISK = @backupFile WITH 
         COMPRESSION
        ,COPY_ONLY;
    SET @current = @current + 1;
END

This works great, but now I'd like to add encryption into the mix. Following tutorials on creating a SMK, a DMK and a certificate my code now becomes this:

    BACKUP DATABASE @dbName TO DISK = @backupFile WITH 
         COMPRESSION
        ,COPY_ONLY
        ,ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = MySQLBackupCertificate);

However, when executed only the first database in the list backs up, every other database gets an error, e.g.:

Processed 9856 pages for database 'DatabaseName01', file 'DatabaseName01_Data' on file 1.

Processed 2 pages for database 'DatabaseName01', file 'DatabaseName01_Log' on file 1.

BACKUP DATABASE successfully processed 9858 pages in 0.401 seconds (192.049 MB/sec).

Msg 3095, Level 16, State 1, Line 33 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.

From my searches there are some things I can't decipher, namely:

1. Because I'm using the same certificate to encrypt each database, the error confuses me
2. I cannot use WITH FORMAT otherwise each previous database will get wiped

I fully understand that my approach may be flawed (I don't know what I don't know and it's not through lack of reading!). I've only been able to find answers and tutorials that relate to multiple databases; single BAKs, but nothing also related to encryption.

Can anyone please advise the correct approach to save multiple databases to a single backup file (if indeed it is possible)?

Answer 1

But what part is not encrypted? This is the first part of my question; the encryption parameter is used for each database in the iteration so I don't understand the context of the error.

That's my fault, I just looked at the error and didn't read much else :| Apologies.

Can anyone please advise the correct approach to save multiple databases to a single backup file (if indeed it is possible)?

It is a current restriction.

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