Recovery Pending Database in SQL server

Question

Post reply

Recovery Pending Database in SQL server

navimir

SSC Eights!

Points: 872
More actions
December 22, 2013 at 1:41 am

#280620

My database is in Recovery Pending mode
Error: Unable to open the physical file.Operating system error 2: "2(The system cannot find the file specified.)".
Database cannot be opened due to inaccessible files or insufficient memory or disk space.
Please help

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

navimir SSC Eights! Points: 872 More actions · Answer 1

My Primary data file is existing but secondary data file is missing

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 2

You'll need to restore this database from a backup. Or, if you have detached the files, you'll need to go through an attach process. Without all the data files, you can't start the database.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

navimir SSC Eights! Points: 872 More actions · Answer 3

but i dont have backup...

can i put it in emergency mode and then take backup and restore??

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 4

Not with a missing data file. You have to have that.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

navimir SSC Eights! Points: 872 More actions · Answer 5

Can I delete the secondary data file in emergency mode?

there will be any data loss??

the secondary data file was wrongly created with extension .mdf instead of .ndf

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

You're going to have to restore from backup if you want that database back, unless you can somehow recover or find that missing data file. With a missing file in the primary filegroup you won't even be able to put it into emergency mode

The extension of the files doesn't matter in the slightest, it's a convention, nothing more. You could call the primary data file .txt and a secondary data file .jpg if you like.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 7

I'd see if you can recover that file and get your database back online.

Then, let this be a lesson, get good backups in place. Especially before you start modifying structures.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 8

navimir (12/22/2013)
Can I delete the secondary data file in emergency mode?
there will be any data loss??
the secondary data file was wrongly created with extension .mdf instead of .ndf

No. Don't delete that file. You just got done saying you don't have a backup.

Make a copy of that file and store it somewhere safe. The rename the original file (if you need to) and restore/recover that file and there should be no dataloss.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Jeff Moden (12/22/2013)
navimir (12/22/2013)
Can I delete the secondary data file in emergency mode?
there will be any data loss??
the secondary data file was wrongly created with extension .mdf instead of .ndf
No. Don't delete that file. You just got done saying you don't have a backup.
Make a copy of that file and store it somewhere safe. The rename the original file (if you need to) and restore/recover that file and there should be no dataloss.

I suspect he meant doing an 'ALTER DATABASE ... DROP FILE' in emergency mode in order to bring the DB online, and that doesn't work.

The only way to recover this at all is to locate/recover that missing secondary data file. Since we don't know why it's missing, just that it is, hard to advise.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 10

GilaMonster (12/22/2013)
Jeff Moden (12/22/2013)
navimir (12/22/2013)
Can I delete the secondary data file in emergency mode?
there will be any data loss??
the secondary data file was wrongly created with extension .mdf instead of .ndf
No. Don't delete that file. You just got done saying you don't have a backup.
Make a copy of that file and store it somewhere safe. The rename the original file (if you need to) and restore/recover that file and there should be no dataloss.
I suspect he meant doing an 'ALTER DATABASE ... DROP FILE' in emergency mode in order to bring the DB online, and that doesn't work.
The only way to recover this at all is to locate/recover that missing secondary data file. Since we don't know why it's missing, just that it is, hard to advise.

Ah. Understood. He made it sound like he had the physical file and that it was just named incorrectly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Jeff Moden (12/22/2013)
Ah. Understood. He made it sound like he had the physical file and that it was just named incorrectly.

Hard to tell what's going on.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass