复制数据库的好方法是什么？通常,我会备份现有数据库,然后将其还原到新数据库.但是,鉴于加密数据,这是否可行？我是否仍然可以使用现有的对称密钥和密码加密并更重要的是解密新数据库中的数据？
我可能想要使用DTS仅传输现有架构.在新数据库中创建新的对称密钥/密码.然后编写即席查询以传输数据,使用现有密钥/密码进行解密,并使用新数据库中的新密钥/密码进行加密.
解决方法
在你做任何事情之前,确保你有一个你的密钥备份(可能你已经这样做了)：
USE myDB
GO
BACKUP MASTER KEY TO FILE = 'path_to_file'
ENCRYPTION BY PASSWORD = 'password'
GO
从这个article：
When you create a Database Master Key,a copy is encrypted with the supplied password and stored in the current database. A copy is also encrypted with the Service Master Key and stored in the master database. The copy of the DMK allows the server to automatically decrypt the DMK,a feature known as “automatic key management.” Without automatic key management,you must use the OPEN MASTER KEY statement and supply a password every time you wish to encrypt and/or decrypt data using certificates and keys that rely on the DMK for security. With automatic key management,the OPEN MASTER KEY statement and password are not required.
