It happened, my database is down!

[SQLBill]

Yesterday the cluster, that my database is on, lost three hard drives. My System/Network/Hardware Admin/Guru and I worked until late last night fixing the hardware and are finishing it up today. Luckily, on recovery we found the data and log files intact! Thanks to this forum and the fact that I read almost every thread (I figure that even if it doesn't pertain to me now, it might later) I believe it may be real easy to recover my database.

Here's my plan and if anyone sees anything wrong with it or has other suggestions, please feel free to let me know.

Once all the hardware is fully operational, I plan to reinstall SQL Server 2000. I then plan on detaching all databases and deleting the data and log files that were created and move the old ones into the proper location. Then I will reattach all of the databases. I've seen this suggested/discussed in this forum and it seems to be the best way to do this, vice a restore from the backup tapes.

Am I right in my thinking that as long as I have the original data and log files I can just copy the original files into their proper location and not do a restore from tape?

-SQLBill

 

[SonOfEmidec1100]

No problem, by the way you dont need to worry about the log files (.ldf) new ones will be created.

exec sp_attach_db @dbname = 'mydb', @filename1 = 'd:\mssql\data\myfilename.mdf'

Good luck

cjw

 

[MeanGreen]

SQLBILL, sorry to hear about your hardware. In you statement you said:
Once all the hardware is fully operational, I plan to reinstall SQL Server 2000. I then plan on detaching all databases and deleting the data and log files that were created and move the old ones into the proper location. Then I will reattach all of the databases. I've seen this suggested/discussed in this forum and it seems to be the best way to do this, vice a restore from the backup tapes.

Once you install SQL Server2000, apply your service pack, and make sure everything is up, then all you really have to do is attach your production databases. You may have to recover you old master database first to get your security logins back in place, so you might want to read up on this, as this is the one database that you have to handle a little differently. You also mentioned deleting the data, but I don't think you really want to do that.

Good Luck!

 

[SQLBill]

Thanks for the inputs.

MeanGreen, when I said I was going to delete the data files, I am assuming that with the re-install of SQL Server the Master, Model, MSDB data files would be recreated with the default info. Also, when I load my vendor's software to create their database a default database is created. So I meant that I was going to get rid of those files and move my real data files into the directory the default ones were built in. This looks like it might turn out to be easier than I thought.....I will read up on restoring the Master DB, thanks.

However, here's an update (it's Friday 11/22 and I'm still down). Turns out we had a catostophic, catostrophic...heck a REALLY BAD failure. The cluster lost three hard drives and one RAID controller. We don't THINK we lost any data at this point. We have actually seen the data and log files in a .chk folder (check disk ran during one reboot.) Luckily, for what I do the information comes from remote computers that have been 'told' to hold their information locally and to not send it to the database. So we aren't losing data, we just can't query it.

Because we have a huge cluster and it's fibre, DELL told us a 12+ hour rebuild (when you replace one disk the system begins rebuilding parity, etc) is normal. Today we got our RAID controller and are going to replace the bad one. Then we need to continue letting the hard drives rebuild themselves.

Luckily, SQL Server 2000 itself is on the two nodes and not on the arrays. So, I won't need to do a re-install (big sigh of relief), I'll just need to restore the data files. Believe me, at this point I'm really grateful for anything going right.

Point learned...this was a failure that could not be planned for. It's like planning for a meteor to hit the building I work in. About the only thing we could have done was have our full site mirrored in another location. That is really not viable for us.

For most of you, you've seen my posts where I state that my database is used for investigative purposes. Well, without totally revealing what I'm not allowed to...here's a very close analogy....

Consider ADT, they are a home security company. They monitor homes against intrusions. All the data from the sensors they install at a home, comes back to one central location (monitoring site). That data is stored in a database in case the police, lawyers, whatever need it to prosecute a burglar. We aren't in the home security field (I did say this was just a close analogy), but the idea is similar. So, even though the database is down, the data is still being monitored at our site and stored in the remote machines. Once I get the database back up and reconnected all the remote information will bulk insert to the database.

This is being a very interesting learning tool.

One very important thing I have learned...if you are not the System/Network Admin, get with that person and find out what they feel they are responsible for. Find out what they are backing up. I have a cluster and my Sys/Network Admin said he would take care of the cluster and making sure it was backed up; my responsibility ended at the databases. OOOPPPPSSSS, neither of us was backing up the Quorum drive which is the clustering services. We may have to rebuild that part from scratch. So, get with anyone else responsible for your equipment and make sure everyone knows how their responsibility is defined.

Once my sys/network admin is done with his part, I can get started. We are working together on this, which is a good thing. I'll post updates as I can.

-SQLBill

 

[tlbroadbent]

cjw said you shouldn't worry about the LDF files because SQL will rebuild them. This could cause numerous problems. When a catastrophic failure occurs, there are bound to be uncommitted transactions in the log files. If you omit those files when attaching the databases, SQL will not be abl to rollback transactions. This would result in inconsistent data. Just let SQL Server attach the LDF and MDF files if they are not corrupted. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[SQLBill]

Thanks Terry,

We kinda came to the same conclusion here, just attach all the files and see what happens. Worst case, we can always start over with the tape backups.

We've still not started with the SQL stuff. We lost the RAID controller again and are awaiting a new one from the company. They think they shipped us a bad one (we think it's we might have a bad power supply, but we can't get them to believe us). So, when we get the new RAID controller we'll be trying to recreate the cluster again. Once that's done I'll be able to start on the databases.

Even though I won't have anyone connected to the database, I'm going to start by putting it in single user mode then attaching the master data file. Then the model and msdb, them my databases.

My hope is that once the cluster is rebuilt and stable, rebuilding the databases will go quickly without any troubles.

-SQLBill

 

[tlbroadbent]

You won't be able to attach master, msdb and model. Once SQL Server is installed, you'll have stop SQL Server and rename the system databases. Copy your database files to the data area, including the old system databases. Then start SQL Server. If everything is in the right place, all of the databases should be opened automatically. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[SQLBill]

Terry,

You said 'once SQLServer is installed'. It's already installed, our two nodes were never lost. We only lost the clustered set of drives that made up the virtual drive. The virtual drive had the database files on it, but not the actual SQL Server 2000. Or will I have to re-install SQL Server even though it already exists?


-SQLBill

 

[tlbroadbent]

Bill,

I'm sorry for the confusion. Your first post mentioned installing SQL Server so I thought that was needed. With SQL Server installed, you should be able to copy all of your databases into the data folder(s) and start the service. This of course depends on whether the system and user databases are recoverable. If master has been corrupted you will need to restore or rebuild it. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.