Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rebuilding a server Expand / Collapse
Author
Message
Posted Tuesday, February 25, 2014 8:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 21, 2014 10:59 AM
Points: 56, Visits: 158
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.
Post #1544987
Posted Tuesday, February 25, 2014 9:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 21, 2014 9:08 AM
Points: 1,194, Visits: 2,228
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
Post #1545022
Posted Tuesday, February 25, 2014 11:51 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 3,998, Visits: 7,172
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; They'll drag you down to their level and beat you with experience"
Post #1545095
Posted Wednesday, February 26, 2014 7:52 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:05 AM
Points: 1,316, Visits: 2,903
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.


Post #1545406
Posted Wednesday, February 26, 2014 7:59 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 3,998, Visits: 7,172
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; They'll drag you down to their level and beat you with experience"
Post #1545413
Posted Wednesday, February 26, 2014 9:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 21, 2014 10:59 AM
Points: 56, Visits: 158
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.
Post #1545450
Posted Wednesday, February 26, 2014 9:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 6,641, Visits: 14,226
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"
Post #1545461
Posted Wednesday, February 26, 2014 9:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 21, 2014 9:08 AM
Points: 1,194, Visits: 2,228
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



Post #1545473
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse