2

I'm running an application on windows server that connects to a SQL Server database.

Today, when I opened SQL Server Management Studio, I was surprised the database is not in the list of the databases!

I don't know what's the reason. I searched in the server files but I can't find the database and also in the recycle bin.

I put my database in C:\db\myWeb.mdf and suddenly it's been removed!

I used stealer phoenix to find the database files deleted on disk but unfortunately I didn't find the db then I tried to add new database with the same name of the deleted db but SQL Server Management Studio said that the database MyWeb already exists! That means it's somewhere on the server but I couldn't find it...

Can anyone tell me how to recover the database?

8
  • If you query sys.databases does the database show up in the results?
    – user507
    Jun 9, 2014 at 11:07
  • @Shawn yes, it is. it's states is RECOVERY_PENDING ! Jun 9, 2014 at 11:41
  • Have you tried restarting SQL Server service?
    – SQLChao
    Jun 9, 2014 at 12:50
  • @JChao how can i do it ? Jun 9, 2014 at 13:01
  • Start > Run > services.msc Find SQL Server for your instance, right click, and restart.
    – SQLChao
    Jun 9, 2014 at 13:07

2 Answers 2

2

Before restoring from the latest full database backup, you can try Paul Randal's advise - set the database to the EMERGENCY mode and use a login that is a member of the sysadmin SQL Server role to access it. Keep in mind that you will be able only to read the data, as the database will be in the read-only mode.

Search Engine Q&A #4: Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database

1

Based on your comment that states the database is in "RECOVERY_PENDING" state I would read up on what that means via BOL:

SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed.

Based on that I would run this query against sys.master_files to see what the physical locations are supposed to be for the data and log file:

SELECT type_desc, name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('myWeb')

You could also review the ERRORLOG for your instance and likely find an error message regarding not being able to find the specific database file.

2
  • this is the result of the command :- ROWS ROWS MyWeb D:\db\MyWeb.mdf LOG MyWeb_log D:\db\MyWeb_log.ldf .. and review the errorlog but didn't find the error msg . Jun 9, 2014 at 19:10
  • 2
    If the files do not exist in those locations and someone deleted them, then your only option is to restore from your latest database backup.
    – user507
    Jun 9, 2014 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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