Freigeben über


Master DB restore & Service Master key

If we don’t have a recent master backup and have to make use of the files from the DR server, then as long as our target instance matches the path and build requirements we can restore master and bring it up. If the build is different , we will get an error. If the path for the system databases are different, then we will not be able to start SQL instance after restore of master and have to start the instance with trace flags to prevent recovery of system databases and then alter the path of the system databases to point to the current path. Also you will need to change several configurations like sys.servers, local logins, etc.

The biggest question when we are doing such type of restore is related to encryption of SMK, however  as far as encryption is concerned, if the SQL Server on the machine to which the master database is restored to starts with the same service account as the original server we should be covered because the Service Master Key is encrypted using the service account and the machine account so service account decryption should still be valid.

For a complete list of various metadata maintained in master that might need tweaks, refer to Manage Metadata When Making a Database Available on Another Server Instance (SQL Server) (https://technet.microsoft.com/en-us/library/ms187580.aspx)