Sql Server 2012 Restoring Master and Migrating a server

All we need is an easy explanation of the problem, so here it is.

I am migrating an entire SQL Server instance with 10 databases on it.

It has many jobs and users. What is best practice regarding the master database? Should I backup and restore this too, or script the users and jobs?

Also, is there another better way to migrate an entire SQL Server instance with all of its databases?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I dont see a need (nor I do it when migrating server) to do a restore of master (or system databases) on the destination server – unless you are trying to achieve something that you have not mentioned in your question.

I find it easy to use sp_help_revlogin to just move logins from one server to another.

so your migration steps would be like below: Server A = Old server ; Server B is NEW server.

  • script out logins from server A.
  • script out jobs from server A.
  • Backup and restore databases from Server A to Server B.
  • Recreate logins scripted out from server A to Server B.
  • Sync up orphan users (if any).
  • Recreate jobs scripted out from server A to server B. Make sure to move packages if any as well.
  • change compatibility level (if moving from lower to higher version).
  • update stats on all tables with fullscan

You can refer to my script at https://dba.stackexchange.com/a/41179/8783 (make sure you do a backup restore and not detach/attach method – now learned it !!).

Method 2

If you want to move the entire instance you can backup and restore the system databases, but make sure you get more than just master. msdb and model are also important. In fact msdb will contain all of your jobs and any ssis packages that you have stored in the instance. master primarily will be your logins and list of databases. model is the template for tempdb. If you’ve never made any changes to it you can probably skip it, but it doesn’t hurt to bring it along also.

Here is some light reading on the subject for you.

System Databases
Back Up and Restore of System Databases (SQL Server)

Also I should point out that you can only restore master and possible msdb on an instance that is the exact same build number. One way around this is to create your new instance, shut it down, and copy the master.mdf, mastlog.ldf, MSDBData.mdf and MSDBLog.ldf files to the locations they exist for the instance (copy/rename the originals first of course). Once you bring the instance back up you will need to re-patch back to your original level.

Method 3

Take a full backup of the master database from the source instance. Stop the destination/target instance’s agent service, and start the instance in single user mode, and restore the master database using the WITH REPLACE hint via the sqlcmd command line utility.

If, for some reason you cannot start the target database instance in single user mode, then scripting your jobs and users would be the better option.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply