Sql-Server
證書、非對稱密鑰或私鑰文件無效或不存在
我正在嘗試將數據庫從另一台伺服器還原到我的本地主機伺服器。該數據庫已加密(bak 文件),所以我必須對其進行解密,這很好,我擁有所有證書文件和密碼,但是出於某種原因,我仍然收到以下錯誤:
The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.
這是我正在執行的程式碼:
CREATE CERTIFICATE [Certificate1] FROM FILE = 'C:\Location of the certs' WITH PRIVATE KEY ( FILE = 'C:\Location of the certs' , DECRYPTION BY PASSWORD = 'password' );
PS。我不是備份和恢復方面的專家。
以下是將加密數據庫還原到不同伺服器(localhost)的步驟。
1.創建證書:
CREATE CERTIFICATE [CertficateName] FROM FILE = 'C:\FolderName\NameOfCert.cer' WITH PRIVATE KEY ( FILE = 'C:\FolderName\NameOfCertKey.key' , DECRYPTION BY PASSWORD = 'YourPassword' );
USE Master ; Open Master Key Decryption by password = 'YourPassword' Backup master key to file = 'C:\SQL FodlerName\MasterKeyName.key' ENCRYPTION BY PASSWORD = 'YourPassword'; GO
3.恢復萬能鑰匙
Use master restore master key FROM FILE = 'C:\FolderName\MasterKeyName.key' DECRYPTION BY PASSWORD = 'YourPassword' ENCRYPTION BY PASSWORD = 'YourPassword'
4.這是你的最後一步,在這個階段要小心,因為我花了一段時間才知道每個命令都需要單獨執行:
Alter Database [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
接下來單獨執行
USE MASTER OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourPassword';
下一個命令單獨執行
RESTORE DATABASE [DatabaseName] FROM DISK = 'C:\Folder\FULL\NameoftheBakFilethat ourAreRestoring.BAK' WITH Replace , STATS = 5
不要忘記將數據庫設置回多使用者模式。