I had a server running that had a disk crash. The server had 2 filegroups: PRIMARY and SECONDARY. SECONDARY was stored in a different NDF file. I lost SECONDARY + the installation of the server, I have the MDF file of the PRIMARY filegroup. I also have a backup from a few months ago. Is there a way to attach the MDF file I have without the NDF I lost? I don't mind losing the tables stored on the SECONDARY filegroup.

I'm using SQL Server 2008R2 DEV. Thanks!


This is one way of recovering the data from your PRIMARY filegroup.

Since the database is not attached, you have to first get it upp and running, you can do that by restoring the old backup to an alternate name (Just to be sure not to owerwrite any files of importance).

Then set the database files you are missing as OFFLINE:

ALTER DATABASE <DatabaseName> 
MODIFY FILE ( NAME = <SecondaryFilegroupFileName>, OFFLINE )

Set the entire database as offline, and replace your database and transaction log files with copies from your files at the moment of the crash.

Set the database as online, and you should be able to work against all data that is not contained in the secondary filegroup to recover your data.

  • I tried this hack: I restored from the backup and got all 3 files: the MDF, the NDF and log, I replaced the MDF with the new one which I still have but was unable to attach. I got an error saying that there is a different between the primary file which might have resulted from corruption... I don't have the exact wording. I also tried attaching after replacing both the MDF and the LDF files with new versions and got the same error. – ytoledano Jul 14 '11 at 5:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.