September 3, 2014 at 1:11 pm
We created a new SQL Server instance, on a new virtual machine, to replace one of our servers that has a production instance of SQL Server. On the new instance we will restore the msdb database, as it stores the agent jobs, restore the user databases, and copy over the logins. We don't use the Model database, and of course we are not going to restore the TempDB from backup. However, do we need to restore the master database? We know it stores login info, but we plan on copying those over using the microsoft stored procedures. Any feedback will be appreciated.
September 3, 2014 at 1:13 pm
If you are just concerned about logins then I wouldn't but are you worried about anything in particular?
September 3, 2014 at 1:27 pm
Logins are the main thing that came to mind when thinking about the master database, so I posted just to make sure there is not something else in there we need to worry about. Thanks for the feedback.
September 3, 2014 at 1:30 pm
The master database contains all instance level information so logins, endpoints, linked servers etc.
Best way forward (imho) is to completely review your existing instance, script out or make a note of anything you want to bring across and then re-create in the new environment.
September 3, 2014 at 2:32 pm
Thank you for the help. Much appreciated!
September 4, 2014 at 5:29 am
bpowers (9/3/2014)
We created a new SQL Server instance, on a new virtual machine, to replace one of our servers that has a production instance of SQL Server. On the new instance we will restore the msdb database, as it stores the agent jobs, restore the user databases, and copy over the logins. We don't use the Model database, and of course we are not going to restore the TempDB from backup. However, do we need to restore the master database? We know it stores login info, but we plan on copying those over using the microsoft stored procedures. Any feedback will be appreciated.
System databases (especially MSDB) should not be restored across instances, script out any required objects and migrate them
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 5, 2014 at 7:31 am
Out of curiousity, if this was a DR situation would it then be acceptable to restore the msdb backup?
September 5, 2014 at 8:34 pm
bpowers (9/5/2014)
Out of curiousity, if this was a DR situation would it then be acceptable to restore the msdb backup?
No, I would still script out and recreate in DR.
Regards,
SQLisAwe5oMe.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply