October 19, 2011 at 1:16 pm
Hi
This is my first post, so please bear with me if I'm not complying to the normal format. I'm also a novice at SQL, I know the basics, but that's about my lot
Brief description of how things happened: We have a big HP server that hosts a SQL server that has 4 instances asscociated with it, a sharepoint instance, sage instance, SCCM instance...I think you may now be getting a clear picture that it's a pretty imporatant server. The other day, 2 drives from a raid 5 set had problems communicating to the backplane, hardware fault (this is long story, so I'll cut to the end) and ultimately, I managed to get the server back up and running with all 5 drives present and data present (or so it seemed). Opened up two of the four instances fine, yet around 70% of the DB's accross the 2 instances were in (suspect) state. I checked various scripts on line to recover, I forget the exact fault, but basically the data was corrupt. So, restored them from DPM backup, connected, alls well.
However, the data folders at the MS.SQL folder lever were inaccessible. Double click on folder, file corrupt message appeared. I have backups of the DB's, but the instance data is not backed up :/ Now, this is a slight tangent, but relevant, if I have 2 mirrored pair for OS, 2 mirrored pair for logs, 3 raid 5 for DB's...where's the best place to put the instance? (Hope I'm making sense..) So, the 2 instances thatwere recoverable, I installed the instance data to the C drive, default path. However, the 2 I can't recover, I installed the instance in the Same drives as the DB's...I only backup DB's and logs via DPM....that question above is only for future reference...the important thing for me at the minute is: Can I repair the instance? (Via repair option via add/remove programs) once I've restored the databases? And should I, can I repair/install/move the instance data from the same directory as the data files and do you all backup the instance folder - MS.SQLetc
Also, not sure if this is helpful, if I try to start one of the instances that doesn't work, I get an error 40, error 2 message.
Really hope someone offer assistance... The DB's should be restored within the next hour...
Thanks in advanace
Mark
October 19, 2011 at 1:19 pm
I guess I'm not clear on what you mean by "the instance data". Do you mean the system databases for the instances? Or something else?
- 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
October 19, 2011 at 1:21 pm
Sorry, my terminolgy isn't so good with this product. Yes, where the bin folder, logs etc are kept. Is that not classed as the instance directory?
October 19, 2011 at 1:24 pm
SQL binaries. They often live with the OS.
Reinstall any that are damaged, it'll probably be quicker.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2011 at 1:28 pm
Hi
When you say reinstall, not run a repair? Remove whats already there and create an new instance of the same name?
October 19, 2011 at 1:29 pm
I would suggest that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2011 at 1:40 pm
Ok, any reason why you wouldn't bother with a repair?
Also, if I'm recreating the instances, where do I put the install, with the OS this time?
Thanks for help, a little more reason would be helpful, I like to understand, why, a little more.
October 19, 2011 at 2:13 pm
If the drive the files are currently on is damaged, there's no guarantee a repair will succeed. I'd prefer to be safe.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2011 at 4:06 am
I thought I'd let you know how things have ended.
Thanks for the advice, but I chose not to take it. The two instances where the instance (bin, logs etc) was installed on the C drive, were resolved very easily. To get the other 2 instances resolved, I copied the instance folder from the restored instance to the F drive, changed permissions, restored the DB's and hey presto, resolved.
IF some could answer me this, or post a thread that may cover this, how can I move the instance from F drive to C, is it possible without uninstalling and re-creating the instance? 2 reasons I wish to do this, restore is so much simpler when the instance data is on the C drive. Also, we want to setup a bare metal restore of the SQL server C drive in case of OS failure. Any help would be appreciated.
October 20, 2011 at 6:20 am
The easy way to do it is to re-install in the location where you want the binaries.
Most of what matters in SQL Server isn't in the instance data anyway. It's in the system databases, mainly master and msdb.
With regards to restoring instead of re-installing, just keep in mind that you're essentially deciding that another crash in the near future is more acceptable than the minor amount of work needed to follow Gail's advice. So long as you and your employer are happy with that decision, that's fine, but be sure it's an informed decision and that all stakeholders in the databases know about the decision and are willing to accept that risk.
- 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
October 20, 2011 at 6:24 am
Thank you GSquared!
That was much more helpful and informative, apprecaited greatly.
Many thanks
Mark
October 20, 2011 at 6:42 am
mark.castro (10/20/2011)
IF some could answer me this, or post a thread that may cover this, how can I move the instance from F drive to C
Uninstall. Reinstall.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply