Sql-server – Possible to restore a damaged database’s PRIMARY filegroup only

corruptiondbcc-checkdbfilegroupsrestoresql server

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?

Best Answer

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 :)

Related Question