Move a TDE Protected Database to Another SQL Server
I'm not a DBA by any means. However, I recently had to move an encrypted database from one server to another and found that I had to compile instructions from several parts of the Internet. In the interest of serving as documentation for myself, and to possibly be helpful to others, I've decided to share them here.
As with all guides on the Internet, please use common sense. Don't execute things you don't understand, especially in production environments. As with all things here, please provide feedback if something is wrong or can be clarified. Thanks!
- Detach the TDE protected database from the source server.
USE master; GO EXEC master.dbo.sp_detach_db @dbname = N'<DATABASE NAME>'; GO
- Move or copy the database files from the source server to the same location on the destination server.
- Create a database master key on the destination instance of SQL Server.
USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<CHOSE A PASSWORD>'; GO
- Create a backup of the server certificate and the private key file on the source server. NOTES: a) You can retrieve the <CERTIFICATE NAME> from sys.certificates. b) Not setting a location stores the files in the default directory for the instance, e.g. C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA
BACKUP CERTIFICATE <CERTIFICATE NAME> TO FILE = '<LOCATION AND NAME TO BACK UP CERT>' WITH PRIVATE KEY ( FILE = '<LOCATION AND NAME TO BACK UP PRIVATE KEY>', ENCRYPTION BY PASSWORD = '<PASSWORD TO ENCRYPT KEY WITH>' ); GO
- Move or copy the backup of the server certificate and the private key file from the source server to the same location on the destination server. Store an additional copy of both of these files in a secure location.
- Recreate the server certificate by using the original server certificate backup file on the destination server. The password must be the same as the password that was used when the backup was created.
CREATE CERTIFICATE <NAME OF CERTIFICATE ON SOURCE SERVER> FROM FILE = '<LOCATION OF THE BACKED UP CERTIFICATE>' WITH PRIVATE KEY ( FILE = '<LOCATION OF THE BACKED UP PRIVATE KEY>', DECRYPTION BY PASSWORD = '<ENCRYPTION PASSWORD WHEN BACKUP WAS CREATED>' ); GO
- Attach the database that you have moved on the destination server.. The path of the database files must be the location where you have stored the database files.
CREATE DATABASE [<DATABASE NAME>] ON ( FILENAME = N'<LOCATION OF MDF FILES FROM EARLIER STEP>' ), ( FILENAME = N'<LOCATION OF LDF FILES FROM EARLIER STEP>' ) FOR ATTACH; GO