0

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?

5

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).
  • run DBCC UPDATEUSAGE
  • update stats on all tables with fullscan
  • Run DBCC CHECKDB ('<db_name_goes_here>' ) WITH ALL_ERRORMSGS,NO_INFOMSGS, DATA_PURITY

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 !!).

  • 1
    agreed. restoring an old master to a fresh install is bad juju – swasheck Jul 26 '13 at 15:33
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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.