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:
- Because I'm using the same certificate to encrypt each database, the error confuses me
- I cannot use
WITH FORMATotherwise 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)?