SQL Server fix deleted mdf

All we need is an easy explanation of the problem, so here it is.

I have a database which my indexes are in separate filegroups and space (SSD). The SSD which contains indexes is break down, my actual data which are on tables are secured but because of SSD break down, my database is on Recovery Pending mode.

I want to get rid of those filegroups which were on SSD and create a new filegroup on another space, then create my indexes on it, but I can’t do anything because of Recovery Pending mode.

I have done these solution:

  • emergency mode, make filegroup offline, make database online
  • partial restore from full backup (my backup is for a month ago) which not worked, database stays in Restoring mode
  • paste deleted mdf from backup into its location, but database still stays in Recovery Pending mode
  • detached database and attach its current filegroups plus deleted filegroups from backup, which not worked because deleted filegroups was from another database and was not accepted by SQL Server

I don’t know what to do any more, I have two options:

  1. Make deleted filegroups offline, create new filegroups and create my indexes with new names on them. It is not a permanent way indeed, because there is some deleted filegroups on my database which is not clean, but it help me to back online.
  2. create a script to create new database, new filegroups, new tables and insert current data to them, create stored procedures and triggers and indexes and … which is difficult to write.

If you have any suggestion I’ll be appreciate.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The normal process to recover from a storage failure is to backup the tail of the log (if possible), restore from your last full backup, restore the most recent differential backup (if you do those), and restore subsequent log backups (including the tail).

There is no (at least supported) way to avoid restoring individual filegroups and end up with a healthy database.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply