Recover the Database in SQL SERVER

 

Steps to Recover

1.create the Database with same Name,MDF Name,LDF Name.
2.Stop the Sql Server and then Replace the only new MDF file by old database (Corrupted database) MDF file and delete the LDF File of newly created database.
3.Now Start the Sql Server again.
4.you can notice that database status became 'Suspect' as expected.
5.Then run the given script to know the current status of your newly created datatbase.
(Better you note it down the current status)

SELECT *

FROM sysdatabases

WHERE name = 'yourDB'

6.Normally sql server would not allow you update anything in the system database.SO run the given script to enable the update to system database

sp_CONFIGURE 'allow updates', 1

RECONFIGURE WITH OVERRIDE

7.After run the above script, update the status of your newly database as shown below. once you updated the status, database status become 'Emergency/Suspect'.

UPDATE sysdatabases

SET status = 32768

WHERE name = 'yourDB'

8.Restart SQL Server (This is must, if it is not done SQL Server will through an error)

9.Execute this DBCC command to create the LDF file.make sure the Name of LDF file which you are giveing is same as deleted LDF file of Newly Created database.

DBCC TRACEON (3604)

DBCC REBUILD_LOG(bmpos,'D:\yourDB_Log.ldf')

DBCC accepts two parameters

1. parameter is database name and
2. parameter is physical path (where the MDF file is located) of the log file. (*Make sure the path is physical, if you specify the logical file name it will throw an error.)

10.Run the given stored procedure to reset the status of your database.

sp_CONFIGURE 'allow updates',0

RECONFIGURE WITH OVERRIDE

11.Do not forget to disable theallow update to system datatbase.

sp_CONFIGURE 'allow updates',0

RECONFIGURE WITH OVERRIDE

12.At last, update the status which you have noted in the 5th step.

UPDATE sysdatabases

SET status = 1073741840

WHERE name = 'yourDB'

Note : During steps 8, 9 , 10 you may encounter any errors if database is in use.
in this case you Set the database to single user.

sp_DBOPTION 'yourDB', 'single user','true'

Once the steps 8,9,10 are completed and database is already single user mode, then run this script to set it multiple users mode.

sp_DBOPTION 'yourDB', 'single user','false'

That's all, now database ready to use with multiple users mode. hopes help and save your time.

Have a nice day... 'N happy Coding :)