Thursday, April 4, 2013

how to restore TDE encrypted databases when you lose the master key and also to restore SSRS when you lose the encryption key


A good way to restore databases when you lose your master key or the server certificate in sql server 2008 R2 is as follows:

1) get the backups from the tapes, get the complete "f:\program files\microsoft sql server" directory with all subfolders and files
2) install sql server 2008 R2 on a new machine preferably with the same service account as the previous installation and also with the same collation settings
3) now backup the "f:\program files\microsoft sql server" directory from the newly installed sql server, including the system database and user database files; you can do this only after stopping sql services.
4) replace the old backup directory from the tape on top of the newly installed location; what this means is that we are rewriting the complete installation directory and taking it back to the old state.
5) restart sql server and you can connect with SA or any other user who have admin access
6) its not so easy but it works as we have successfully done this

A good way to restore SQL Server Reporting services if SSRS is corrupted and you don't have the encryption key to re initialize the setup.
this means if you face this error, "the report server installation is not initialized, (rsReportServerNotActivated) then you can do the below step.

1) perform the same steps as 1), 2), 3) , 4) but this time the folder should be SSRS installation directory, it looks like this: "F:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER"

after replacing the installation directory with the backup one, start the SSRS config manager and do the 'report manager url' and ' service account' settings.
and when you go to "encryption keys" tab, you would automatically see that everything would be in sync.
quickly backup the encryption key and that's it you restored your report services to your original state.

No comments:

Post a Comment