Is it (always) necessary to backup a certificate with private key?

Question

I'm starting a project to encrypt database files. I've created a master key and a certificate the following way:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master_K3y';
GO
CREATE CERTIFICATE MY_DB_CERT WITH SUBJECT = 'Some subject here';
GO

I have two options to backup the certificate: with or without a private key. I have no idea where this private key comes from, as I did not provide one. Perhaps it was generated for me when creating the certificate?

In any case, my first backup statements did not provide a private key clause.

USE master;
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Master_K3y';
GO
BACKUP MASTER KEY TO FILE = 'MASTER_KEY.bak'
ENCRYPTION BY PASSWORD = 'yeK_r3tsaM';
GO
BACKUP CERTIFICATE MY_DB_CERT TO FILE = 'MY_DB_CERT.bak';
GO

I am wondering if that is enough to restore everything in a working state, say when moving to a new server? If not, what is the use-case for backing up a certificate without a (the?) private key?

Answer 1

The code above will only backup the PUBLIC key portion of the certificate. This however by itself, is useless.

The private key is the part needed to decrypt the database - without this the database cannot be decrypted and therefore cannot be accessed.

sqlity Backup Certificate explanation

To back up a certificate you can use the BACKUP CERTIFICATE statement. In its simplest form, it looks like this:

 BACKUP CERTIFICATE ACertificate TO FILE ='C:\temp\ACertificate.cert';

But what about the Private Key? The above statement creates a backup of the public portion of the key only. That is however not the important part. The important part is the private key of the certificate. To create a backup of the private key too, we have to add the WITH PRIVATE KEY clause to the BACKUP CERTIFICATE statement:

 BACKUP CERTIFICATE ACertificate    TO FILE
 ='C:\temp\ACertificate.cert'   WITH PRIVATE KEY(
     FILE = 'C:\temp\ACertificate.prvk',
     ENCRYPTION BY PASSWORD = '**********'   );