Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have a database that has an encryption. The encryption key was lost.
This database is on a SQL Server 2008 R2 server, and my mandate is to migrate this database on a more recent server.
Is there a means to perform the restoration if I do not know the encryption key?
Is there a means to perform the restoration if I do not know the encryption key?
No. Currently if you lose the old server you lose the database. So immediately decrypt the database and take a backup.
The SQL Server service account has access to the Server Master Key, which grants access to the Database Master Key in the Master database, which in turn grants access to the Database Encryption Key of the TDE database.
So assuming that you have the old server still running, you can log into that server and remove database encryption from the database, then wait for the database files to be decrypted (it's a background process), and take a backup, which will be unencrypted.
See eg https://www.sqlservercentral.com/steps/stairway-to-tde-removing-tde-from-a-database and https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-ver15
