Hi everyone,
My SQL server 2005 data file has corrupted and I do not have backup of the database. But as seems there is no problem in log file (ldf).
Can anyone please help in recovering the data from corrupt mdf file?
Are there any methods or steps to recover the data with log file?
Thanks in advance for suggestions...
My SQL server 2005 data file has corrupted and I do not have backup of the database. But as seems there is no problem in log file (ldf).
Can anyone please help in recovering the data from corrupt mdf file?
Are there any methods or steps to recover the data with log file?
Thanks in advance for suggestions...
In Database
Share this post:
Responses (5)
-
Accepted Answer
-
Accepted Answer
Recover a corrupt database
The first step is to attach the database as suspect
Create a database with an mdf of the same size as the old one stop the server.
Copy the corrupt mdf over the newly created one.
Restart the server - the database should be suspect.
Get the server to retry recovery
Reset the suspect flag
this can be done by executing sp_resetstatus 'mydbname'
or by
update master..sysdatabases SET status = status ^ 256 where name = 'mydbname'
Restart the server
If the database is still suspect set it to emergency mode
This will be the case for torn pages - if you get a torn page error go straight to this step.
update master..sysdatabases set status = 32768 where name =
'mydbname'
(for v7 I believe it was update master..sysdatabases set status = -32768 where name = 'mydbname')
The database should now be in emergency mode and allow you to access the data.
The data should now be transferred to another database via dts, bcp or queries (I prefer bcp native format).
You will get an error when trying to access the corrupt data but should be able to access all the rest of the data.
Use indexes to access data around the corrupt pages.
To allow the above commands to work you will have to allow updates to system tables
Sp_configure 'allow updates', 1
Reconfigure with override
Remember to set it back afterwards
If this instruction appeared uneffective, then make use of third party software SQL Server Repair Toolbox http://www.sqlserver.repairtoolbox.com/ -
Accepted Answer
Repair corrupt or damaged SQL server database, download an effective tool Kernel for SQL Database Recovery Tool. The tool easy to recover corrupt or damaged MDF files of Microsoft SQL Server. This tool also recovers all tables, stored procedure, functions, views, rules, triggers and associated Primary Key, Unique keys and etc. It supports all versions of MS SQL server and Windows OS versions. Visit here - http://www.sqldatabaserecovery.org -
Accepted Answer
Execute the following query
ALTER DATABASE test
SET emergency
GO
ALTER DATABASE test
SET single_user
GO
DBCC checkdb (test, repair_allow_data_loss)
ALTER DATABASE test
SET multi_user
GO
If this doesn't help you then you can take the help of third party MDF repair tool to recover your database.
Sorry, the discussion is currently locked. You will not be able to post a reply at the moment.