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

Full Server Rebuild Expand / Collapse
Author
Message
Posted Tuesday, December 1, 2009 8:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 6, 2009 2:00 PM
Points: 8, Visits: 23
I am going to be rebuilding my SQL 2005 server this week. By rebuild I mean backing up the databases/logs, etc and wiping and reinstalling the OS and SQL Server 2005 program files. My question is, what is the best way to restore my databases back onto the server once it is rebuilt? Should I just detach/reattach or use full backup/restore? How do the system databases figure into this, should I restore those as well or can I just use the fresh ones from reinstall? Any other details I should look into?

Thanks in advance for any advice you can give!
Post #826744
Posted Tuesday, December 1, 2009 8:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
I'd definitely do backups, but the easiest way to get the databases back online will be detach, re-attach.

On the system databases, it kind of depends on what you've got in them. The master database is probably fine to use from the re-install. That's usually less complicated than trying to recover the old version. But it depends on whether you've made changes to it. For example, are there any procs in it that have been set up for auto-run on startup?

With the msdb database, do you have scheduled jobs? If so, restoring is probably going to be easier than re-creating those.

On model, have you customized it to make your new databases have certain properties and/or objects?

With tempdb, of course, the main question is what size works best for it. You probably aren't backing that one up anyway, but you'll want to make sure the new one is the right size, which you can probably figure out from the existing one.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #826757
Posted Tuesday, December 1, 2009 8:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 6, 2009 2:00 PM
Points: 8, Visits: 23
I haven't personally made any changes to model, master, etc, but it is possible that the software installs for applications that are using the SQL Server as a backend have. Is there a way to tell if any custom procedures or other settings have been created other than contacting the vendor to ask?

Thanks again!
Post #826764
Posted Tuesday, December 1, 2009 9:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
Are you familiar with the view sys.all_objects? That'll give you a list of all tables, views, procs, etc., in the database. Should be able to find what you're looking for in there.

If you do a clean SQL Server install, either as a separate instance or on a separate computer, you can narrow the search down by doing an Except query vs that copy of master. You can also look at the types, but it's easy to miss something that way.

Would look something like:
select name
from master.sys.all_objects
except
select name
from MySecondServer.master.sys.all_objects;

That'll give you a good starting point.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #826767
Posted Tuesday, December 1, 2009 9:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 6, 2009 2:00 PM
Points: 8, Visits: 23
That looks like it will work well. I am just starting to get into SQL Server administration recently so I appreciate your willingness to help!

Post #826774
Posted Tuesday, December 1, 2009 9:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
ldstpete (12/1/2009)
That looks like it will work well. I am just starting to get into SQL Server administration recently so I appreciate your willingness to help!


You'll love this site. Lots of experts here very regularly and very willing to help.

I learn from it every time I visit.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #826778
Posted Tuesday, December 1, 2009 9:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 6, 2009 2:00 PM
Points: 8, Visits: 23
Oh right, also - what about transferring the users, do you recommend the use of sp_help_revlogin?

Thanks!
Post #826787
Posted Tuesday, December 1, 2009 9:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:27 AM
Points: 1,646, Visits: 1,947
ldstpete (12/1/2009)
Oh right, also - what about transferring the users, do you recommend the use of sp_help_revlogin?

Thanks!

Yes, sp_help_revlogin is the best way to transfer logins.

You may be interested to see my blog (available in my signature) a blog on Sept 24th has some steps and points that might help you.



Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.com\LearnSQLWithBru

Twitter -- BruMedishetty
Post #826792
Posted Tuesday, December 1, 2009 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 6, 2009 2:00 PM
Points: 8, Visits: 23
Great, thanks!
Post #826796
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse