From SQL Server 2014, SQL Server support the backup encryption . Which means we can encrypted our backup file to enhance the security for the backup media.
- Backup compression can also work with encryption.
Create the master key and certificate. Those will be used when we need to backup and restore the database.
Create test Database
Create the test database and insert some sample data then backup without encryption.
If we open the non backup file with notepad, we can find our data is present in the backup file as clear text.
Backup database with encryption
The content of the backup file has been scramble by encryption.
Restore encrypted database backup
To simulate restore to another server or if the server certificate is lost for some reason. Lets drop the certificate and restore.
Because there is no certificated therefore SQL server can not validate the backup. Restore will fail with below error:
Msg 33111, Level 16, State 3, Line 64
Cannot find server certificate with thumbprint '0x35FA80FB4FC5F4555B3F89FF914A98ABA212C5C3'.
Msg 3013, Level 16, State 1, Line 64
RESTORE DATABASE is terminating abnormally.
Lets restore the certificate and restore the database.
database successfully restore.