Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2005
    Posts
    4

    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.

  2. #2
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    ALTER DATABASE [MSWCG] ADD FILEGROUP [ATSINDEX]
    GO
    ALTER DATABASE [MSWCG] ADD FILE(NAME = N'IDX_MSWCG', FILENAME = N'D:\mssql2k\data\HSC\MSWCG\IDX_MSWCG_Data.NDF' , SIZE = 1, FILEGROWTH = 1) TO FILEGROUP [ATSINDEX]
    GO

    Change the location, names of files, file groups, etc to the old one. I'm not sure this will work. Is the database "suspect"?

  3. #3
    Join Date
    Mar 2005
    Posts
    4
    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'.....

    Thanks for the response.

  4. #4
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    I've got something I want to try. Give me a few minutes.

  5. #5
    Join Date
    Mar 2005
    Posts
    4
    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
    GO
    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
    GO

  6. #6
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    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.

  7. #7
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    I'm trying something else.

  8. #8
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    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.
    Last edited by DBA-ONE; 03-03-05 at 17:39.

  9. #9
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    I've run out of ideas. Since there is no backup I take it this wasn't on a production server?

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  11. #11
    Join Date
    Mar 2005
    Posts
    4
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •