How to Recover the Database in SQL SERVER

No.of Views1109
Bookmarked0 times
Downloads 
Votes0
By  youngmurukan   On  11 Jan 2011 19:01:29
Tag : Sql Servers , Server Management
In this article, I will explain how to recover data in sql server. Sometimes by chance database is corrupted or suspected, on this time you have to recover database to use. On this article clearly explained step by steps to recover it.
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

Introduction

In this article, I will explain how to recover data in sql server. Sometimes by chance database is corrupted or suspected, on this time you have to recover database to use. On this article clearly explained step by steps to recover it.

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.

 
Sign Up to vote for this article
 
About Author
 
youngmurukan
Occupation-Not Provided
Company-Not Provided
Member Type-Senior
Location-Not Provided
Joined date-12 May 2009
Home Page-Not Provided
Blog Page-Not Provided
 
 
Other popularSectionarticles
Comments
There is no comments for this articles.
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top