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?
Best Answer
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.