February 25, 2014 at 8:58 am
We recently had to kill one of our dev servers. This is my 1st rebuild of a previously existing server. I have bak's of every database, but should I restore master, msdb, and model?
There are also a bunch of SQL Agent jobs that I will have to re-create by looking at the production versions and creating them manually on the new dev server. I'm not sure if this has an impact on whether or not I should restore the system db's, but it seems that it would since that information is stored in msdb right?
Any advice would be greatly appreciated.
February 25, 2014 at 9:52 am
Restoring the system databases would help you in retaining the server level settings. If you restore MSDB, you would get all your old jobs. But if you need jobs from production, you can delete the existing jobs and recreate them in test.
--
SQLBuddy
February 25, 2014 at 11:51 am
You may want to read through this article - http://www.mssqltips.com/sqlservertip/2571/restoring-sql-server-system-databases-msdb-and-model/
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 26, 2014 at 7:52 am
Restoring master will give you the logins to SQL Server as well. If you have a ton of them or aren't able to manually recreate them then you need to restore master.
February 26, 2014 at 7:59 am
If you restore master, you will need to fix the logins for any orhpaned users., Not typically an issue however, if one of the orphaned users was the owner of one of the databases you're restoring, it will cause an error (easy to fix).
Here's a simple script to correct this:
http://stackoverflow.com/questions/2872267/script-to-dynamically-fix-ophaned-users-after-db-restore
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 26, 2014 at 9:06 am
Thanks for all the advice! I ended up deciding against restoring the system db's. There's too much complexity in the security and the jobs to chance bringing it down and having to start over. Thanks again though.
February 26, 2014 at 9:27 am
robin.pryor (2/26/2014)
Thanks for all the advice! I ended up deciding against restoring the system db's. There's too much complexity in the security and the jobs to chance bringing it down and having to start over. Thanks again though.
As long as the computer has the same name and the same user account is used to run the sql server services it shouldn't be an issue to restore the system databases, just make sure the collation is correct too when you reinstall the instance
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 26, 2014 at 9:51 am
Yeah, it should not be an issue if you follow the steps correctly. Moreover, it's a dev server and you get good experience on restoring system databases which will be really useful in the future.
--
SQLBuddy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply