Restoring data when missing .mdf file??

[jsteph]

I have a small sql2K database that consisted of 3 files:
data.mdf
log.ldf
index.mdf

The files were wiped out. I have file-level backups of data.mdf and log.ldf, but the index.mdf was on a different drive and wasn't backed up. Index.mdf contains ONLY one index for the largest table.

Now...my questions:
1. Forget the data for a moment--I have a couple of stored procedures I need. I've tried to Attach the 2 files to a dummy database but it wants that index.mdf. Is there any way to force this to work just to get the sp's?

2. Same for the data--I don't need indexes, I just need some raw data. Is this possible?
Thanks,
--Jim

 

[JayKusch]

Is the Index file an MDF or an NDF??? I am assuming it is an NDF and we have a typo above.

Thanks

J. Kusch

 

[jsteph]

No, it's .mdf. I may have broken naming conventions, but when I created a new filegroup in which to put this index, I just named the file index.mdf (actually, it and the other files are prefixed with the dbname but I didn't include that so as to keep the noise down).

So the missing file is from a filegroup I created and I really can do without it for my needs as far as restoring the sp's and some data, since only one table's index was using that filegroup.
--Jim

 

[JayKusch]

Have you tried to execute sp_attach_single_file_db yet? If not, rename your log and index file to something like *.hold, this will make sure the SP does not recognize them and try to work w/ them.

The take a look in Books OnLine (BOL) to view the syntax to run the SP. If all goes well, you will be asked if it is OK to create a new log file (LDF) and it "may" overlook the index file.

I have a uneasy feeling that it will still want that index file.

Thanks

J. Kusch

 

[jsteph]

Jay,
Thanks, I've tried both attach sp's, and even the single_file one fails on both the log file and the index.

We found an older backup of the index, so now I have all 3 files, but the index is from a different time--but the same db. So now I did the normal sp_attach_db, listing a new 'test' db name, and all 3 files. I get the error:

"Cannot associate files with different databases."

Why would I get that?

I even tried renaming the first arg to the original db name, but on a different server (copying the files there of course), and I still get that error.
Is there anything else I can try? Thanks,
--Jim

 

[JayKusch]

Once again I believe since you have your Index as and MDF you are running into this issue. The MDF represents the primary file group of a database. You cannot have 2 primary file groups for a database.

That is why you are receiving the error of "Cannot associate files with different databases."

SQL Server is seeing the your main database file, the MDF and the Index.MDF as two seperate databases!!

You will not be able to reattach these files as they stand right now.

For a quick, and probably a futile, attempt ... rename your Index.MDF to Index.NDF and try attaching the file. Let see what error you get from that. Hopefully none!

Thanks

J. Kusch

 

[jsteph]

Jay,
Thanks again, but it looks like I'm SOL. I keep getting similar errors. But I'm not sure it's the 'ndf' instead of 'mdf'--Microsoft gives me the option to make the extension whatever I want with no warning, so hopefully they wouldn't--though I wouldn't put it past ms--base the integrity of an entire database on having a certain file extension.

Anyway, it seems like it looks inside the first file, and that has info about other files that should be there.

It's just frustrating and disappointing, because I know the data is there--I know everything is there except a stupid index, but I'm locked out. The ironic thing is that I used to do daily backups via sql-server scheduler, but our brilliant network admins spent well into 6 figures on a SAN with Tivoli backup which was supposed to do native sqlserver backups daily, but it did not, so my manual spur-of-the-moment .mdf file backups were the only thing I had.
Thanks again for your help,
--Jim

 

[JamesLean]

Just to confirm, it does not matter what extension the data files have - it is only convention that the primary is mdf and subsequent ones are ndf. You can actually name them whatever you want.

I don't know how you can restore your database without all the files though, sorry!

--James