Restoring full backup of TDE database to alternate server. The media family is incorrectly formed.

Question

I am trying to restore a full backup of a TDE encrypted database to an alternate server.
I overwrite an existing database on the destination server - but the restore fails even if I try creating a new database.

Prior to encrypting the databases, I was able to do this without any errors.

I have imported the certificate from the source server to the destination server and generated the database encryption key from the certificate using the same algorithm as was used for the encryption key on the source server.

The passwords for the the Master Key are different between the two servers.
The password used to encrypt the certificate backup file on the source server is different from the password for the Master Key.
When I created the certificate on the destination server, I gave it the same name as the certificate on the source server and used the password for the source certificate to decrypt.

Source:

CREATE MASTER KEY ENCRYPTION
BY PASSWORD='MasterPW@Source';
GO

CREATE CERTIFICATE My_Cert
WITH
SUBJECT='Database_Encryption';
GO

BACKUP CERTIFICATE My_Cert
TO FILE = 'C:\temp\My_Cert'
WITH PRIVATE KEY (file='C:\temp\My_Cert.pvk',
ENCRYPTION BY PASSWORD='CertPW@Source');
GO

USE SourceDB
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE My_Cert;
GO

Destination:

CREATE MASTER KEY ENCRYPTION
BY PASSWORD='MasterPW@Destination';
GO

CREATE CERTIFICATE My_Cert
FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\My_Cert'
WITH PRIVATE KEY
(
	FILE='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\My_Cert.pvk',
	DECRYPTION BY PASSWORD='CertPW@Source'
);
GO

USE DestinationDB
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE My_Cert;
GO

I used the following options when generating the database backup:
COPY_ONLY,
NOFORMAT,
NOINIT,
SKIP,
NOREWIND,
NOUNLOAD,
COMPRESSION

I copied the .bak file to the destination server with a simple robocopy command.

I tried to restore the database to the destination server with the following options:
FILE=1,
NOUNLOAD,
REPLACE

The restore operation fails with the message:
"The media family on device 'D:\Backup\SourceDB.bak' is incorrectly formed. SQL Server cannot process this media family." and the database gets stuck in a (Restoring...) state.

My research to this point has found three suggested causes for the error:

1. TDE encrypted database cannot be restored if the backup was compressed; but I have the same problem regardless of compressed or uncompressed backup.
2. The .bak file of a TDE encrypted database will get corrupted if transferred to a different server without using a binary protocol; but it is my understanding that RoboCopy uses a binary transfer protocol.
3. The SQL Server version of the source database is greater than the version of the destination database; the version of my source is Microsoft SQL Server 2019 (RTM-CU16-GDR) (KB5014353) - 15.0.4236.7 (X64), and the version of my destination is Microsoft SQL Server 2019 (RTM-CU12) (KB5004524) - 15.0.4153.1 (X64)

Is the slight difference in the SQL Server versions enough to cause this error?
Have I done something incorrectly in the encryption and/or backup-restore process?

Any solutions or advice would be greatly appreciated.

Answer 1

Hi @James E. Gerberich,

Here are some steps you can take to troubleshoot and resolve this issue:

Ensure that the backup file is not corrupted: Try to restore the backup file to the original server to make sure that the file is not corrupted. If the restore is successful on the original server, then the issue may be with the backup file being transferred or stored incorrectly.

Verify the version: For example, if the backup file was created with a newer version of SQL Server than the one you are trying to restore it to, it may not be compatible.

Try restoring to a different server: If the issue persists, try restoring the backup file to a different server to see if the problem is specific to the target server.

Please refer to this blog:

http://dotnetguts.blogspot.com/2010/06/restore-failed-for-server-restore.html

In addition, here is a similar thread for you:

https://learn.microsoft.com/en-us/answers/questions/235583/the-media-family-on-device-incorrectly-formed-for

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

The problem was the difference in SQL Server versions. Even though both instances are SQL Server 2019 the destination server was a few updates behind the source server. Once the destination instance was updated to the identical build number, the restore operation completed with no errors.