Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My database suddenly stopped working and from checking the error logs it says that the master database was corrupted.

So I tried rebuilding the system database with the command:

setup /ACTION=REBUILDDATABASE   /QUIET 
      /INSTANCENAME=<instance name>  /SQLSYSADMINACCOUNTS=<accounts>

After running the command, the database server has started working again, but all my database are nowhere to be found. I really need some ideas as to I have no backup databases.

I have the idea that the database files (mdf, ldf, ndf) are still on my local drive so is it possible to relink those files to a database?


update

I tried creating a database with the same name on the instance and got the error

Create failed for Database 'db'.  (Microsoft.SqlServer.Smo)

Cannot create file 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.db\MSSQL\DATA\db.mdf' because it already exists. Change the file path or the file name, and retry the operation.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 5170)

Which proves my idea that the files are still there. So how do I rebuild/recover this to my database?

share|improve this question
2  
I suggest you stop trying to solve this by blind shots. You may do more harm. This is the right place to ask and you will get good answers (not sure how fast though, it's Christmas Eve and in some places of the Earth, already Christmas day :) –  ypercube Dec 24 '12 at 19:14
    
Merry Christmas upon further research right clicking database and using the attach function seems to fix it. –  madziikoy Dec 24 '12 at 19:17
add comment

2 Answers

up vote 7 down vote accepted

You can try attaching the missing databases using the files. You can right click on the databases folder in Management Studio and select "Attach".

Another option, and the one I would generally go for, would be to use any backups you have. You will experience some data loss though. If you are using the full recovery model for your databases, you should be able to restore to the last transaction log backup. Otherwise if you are using the simple recovery model you can only restore from the latest full backup.

share|improve this answer
    
My problem now is that the users/login weren't recovered. –  madziikoy Dec 24 '12 at 19:27
1  
Yes the users are probably orphaned. This occurs when the server logins are not mapped to the database users. When you rebuilt your master database you probably lost the logins. You'll need to rebuild those. Do you ever do backups of your system databases? If not I would encourage you to do so as you'd be able to use that to get a database you could use to script out the logins. –  Brandon leach Dec 24 '12 at 19:41
2  
sp_change_users_login seems to do the trick. Thanks :) –  madziikoy Dec 24 '12 at 20:12
add comment
  1. In SSMS, right click the Databases node for the SQL Server instance
  2. Select Attach

enter image description here

3.Click Add

4.Go to D:\Program Files\Microsoft SQL Server\MSSQL10_50.db\MSSQL\DATA 5. Select db.mdf The corresponding ldf file will be automatically added

enter image description here

In case this fails because the mdf file is bad, you can try a tool for reading damaged mdf files, such as ApexSQL Recover

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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