December 1, 2009 at 8:37 am
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!
December 1, 2009 at 8:49 am
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
December 1, 2009 at 8:59 am
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!
December 1, 2009 at 9:04 am
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
December 1, 2009 at 9:09 am
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!
December 1, 2009 at 9:11 am
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
December 1, 2009 at 9:21 am
Oh right, also - what about transferring the users, do you recommend the use of sp_help_revlogin?
Thanks!
December 1, 2009 at 9:26 am
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.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 1, 2009 at 9:28 am
Great, thanks!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply