Unanswered: Lost Index device file and database suspect
Can anyone help me to recreate the .NDF file if it was lost and not restorable? I have a stored procedure written that will then help me rebuild all indexes on all tables, but I can't seem to figure out how to create the index file again so I can run it. I have tried index files from other databases to no avail. There is no backup to go to.
Yes, database is suspect. I will try what you suggested. I have copied out the mdf and log files, dropped the database, then recreated with the exact same names and locations all three parts, but when I copy the mdf and log back in, the database goes back to 'suspect'.....
Ok, I ran it but still get errors. It does not like the fact that the database is 'suspect' and missing one of it's files. The sp_resetstatus also does not work to get it to at least a usable position to execute what you gave me.
Any other ideas on how to recover from this? Can I force the database our of the 'suspect' mode so I can add the index file again?
ALTER DATABASE Chesterfield_rm ADD FILEGROUP aliant_Index1
ALTER DATABASE Chesterfield_rm ADD FILE(NAME = N'aliant_Index1', FILENAME = N'D:\data\chesterfield\aliant_Index1.NDF' , SIZE = 1, FILEGROWTH = 1) TO FILEGROUP aliant_Index1
I know of no way to get it out of suspect mode without getting back that file. I created a database, shut down the server, moved the file to get to where you are. I then logged in as SA and attempted to recreate the file group but it doesn't like it as I suspected because it is in suspect mode.
I suspected that the server would identify a file group by number more than name and just confirmed it. I created an identical DB on another server and brought the "replacement" file over but it pukes as I suspected.
Can you try to put the database in emergency mode (you will have to consult microsoft on how to do this, it has been a long time since I had to do this. I think it involves setting allow updates on, then updating the sysdatabases table), then bcp the data out? After that, you can drop the suspect database, rebuild a new one, and hopefully live happily ever after.
The database is not production, rather a test to see if we could recover from bad scenarios.
Has anyone every had any luck restoring only a FILEGROUP to a database? I am using a very old backup that would contain the Index File, but everytime I restore it alone, either via DATABASE RESTORE or Enterprise Manager, the database moves from SUSPECT to LOADING mode and never comes out.
****I will lookup the emergency mode...but I am not sure how to BCP all the data to another db. Would I create a tab file and reload with bcp? How does that work with the transaction log?