Search code examples
sql-serversql-server-2008sql-server-2008-r2mdf

SQL Server Database Primary Data File got lost


SQL Server 2008 R2 stopped all of a sudden due to (maybe) Power Fluctuation. I tried all the possible ways to restart the it but every time it is failing with the error

The request failed or the service did not respond in a timely fashion.

Some of the ways I tried are

  • Making the SQL Server to log On as "Local System" instead of "NetworkService"
  • Replacing of Master.mdf and mastlog.ldf files from the "Bin/Templates" folder
  • Disabling "VIA" (which was already disabled)

But all in-vain :(

On checking further I noticed that both the data files i.e. mydb.mdf and mydb.ldf of my database are not there in the DATA folder and instead there are mydb_1.ndb and mybd_2.ldf files.

How to recover mydb.mdf file and to restart the SQL Server?

Thank you.


Solution

  • sql data files can be named anything so the mydb_1.ndb could be your data file.

    If that's true you should be able to recover the data by:

    1. Install a new sql server (sql express would work if the DB is < 10GB)
    2. move the mydb_1.ndb and mybd_2.ldf onto that server
    3. Use "Attach..." from ssms to add the database to the new server

    If you are lucky and that ndb is just a differently named mdf file you should be able to access the data.

    Then you can repair your existing server (reinstall will be easier than messing with the master database unless you've got other dbs on there) and move the database back over i.e. do the same attach... method

    Oh - and start backing it up :)