After Restoring MSBD perform the following two steps

  • Comments posted to this topic are about the item After Restoring MSBD perform the following two steps

  • You might want to fix the title to say MSDB instead of MSBD.... :hehe:

  • Oh!!! thanks Jay Zach. now updated msdb..

  • 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

  • Thanks for the script.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply