Post reply

After Restoring MSBD perform the following two steps

  • September 20, 2010 at 11:31 pm

    #229427

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

  • September 21, 2010 at 5:28 am

    #1224690

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

  • September 22, 2010 at 1:22 am

    #1225355

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

  • September 28, 2010 at 2:30 am

    #1228193

    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

  • May 18, 2016 at 7:16 am

    #1879864

    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