SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rebuilding a server


Rebuilding a server

Author
Message
Robin
Robin
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 304
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.
sqlbuddy123
sqlbuddy123
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2422 Visits: 2243
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
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6728 Visits: 7394
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" ;-)
Markus
Markus
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3057 Visits: 3778
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.



MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6728 Visits: 7394
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" ;-)
Robin
Robin
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 304
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.
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20360 Visits: 17244
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" ;-)
sqlbuddy123
sqlbuddy123
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2422 Visits: 2243
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search