1

I have a database (MyDatabase) with 2 filegroups, [PRIMARY] and [SECONDARY].

The [SECONDARY] filegroup has 2 datafiles, [DataFileA] and [DataFileB].

[DataFileA] is damaged (even after DBCC CHECKDB process).

I tried to solve it with:

  1. DBCC CHECKDB ([MyDatabase], REPAIR_ALLOW_DATA_LOSS) but it didn't help.
  2. Deleted the damaged datafile [DataFileA], with no success.

The [PRIMARY] filegroup is ok though.

Can I backup just the [PRIMARY] filegroup and restore it (under a new name) and create a new database from this single filegroup?

  • 3
    Do you have any backups to restore from? If not then data loss may be unavoidable – James Anderson Apr 30 '15 at 15:27
  • No, I don't have any backup (I wish I add). And yes, I know . Thanks ! – itzik Paz Apr 30 '15 at 15:30
  • The database is in ONLINE state. The problem is that I can not delete\drop [DataFileA], I am getting an error on the delete process that there are damaged pages . – itzik Paz Apr 30 '15 at 19:27
2

Since the database is ONLINE, you have two main options:

  1. Create a new database with the same schema for [PRIMARY] filegroup objects as the old database, and transfer data using any of the tools available (e.g. SSIS, bcp, linked server). Depending on the complexity of the existing schema, constraints, and relationships (e.g. foreign keys) this may be quite time-consuming.

  2. Take a partial backup of the [PRIMARY] filegroup only. Restore this to a new database and files using piecemeal restore. Finally, mark the filegroup as defunct. Be sure DBCC CHECKFILEGROUP for [PRIMARY] runs cleanly first. You should export the schema and data to a safe place as in option 1 as well. As you know, having tested backups of your valuable data is priority #1 :)

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.