Something else you may need to do when moving system databases to a different server is to restore the Service Master Key, the password for which should be kept in a secure location, preferably not on the SQL Server box.

Any encrypted data and objects will have been encrypted using the Service Master Key on the old server and because the Service Master Key is automatically generated when SQL Server is installed, there will be a mismatch between the current Service Master Key and the old one. This prevents, for example, Replication from being enabled because the system cannot create a Linked Server.

It is therefore necessary to restore the old instance’s Service Master Key from a backup. If no backup of the old Service Master Key exists, as long as the old instance is still available a backup can be taken there:

USE master;

GO

BACKUP SERVICE MASTER KEY TO FILE = 'C:\My_backup_location\My_server_name_service_master_key.bak'

ENCRYPTION BY PASSWORD = <password>

GO

To restore the Service Master Key use:

RESTORE SERVICE MASTER KEY FROM FILE = 'C:\My_backup_location\My_server_name_service_master_key.bak'

DECRYPTION BY PASSWORD = <password> FORCE --where <password> is the one used for encryption

GO

Regards

Lempster