vendredi 22 décembre 2006 09:00
My database corrupted because when I was running an update query, there is a power failure. After the computer booted, I cannot open the database anymore, it just not responding. Then I stop the sql server service, and tried to rename the .mdf and .ldf. After that it worked normally, but I need the data from the corrupted mdf file, I tried to attach the database but it just hanged. I even tried to attach without the .ldf file but it didn't work either, so I concluded that the problem is with the mdf file.
Is there any way to recover my data ??
Thanks in advance
Toutes les réponses
vendredi 22 décembre 2006 09:48
Can you rename the mdf,ldf files to their original names and attach them to your SQL Server? (with all SQL Services running)
If that works, try using
DBCC CHECKDB ('DatabaseName' /*,REPAIR_REBUILD*/)
WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY
To see what went wrong
A 2nd choice is to restore the mdf,ldf files from a recent backup (if backup exists)
vendredi 22 décembre 2006 10:02
We'd tried that but we got no luck. Attaching the file in it's original name didn't work, the computer just hanged, we suspected that the .mdf file corrupted
Unfortunately, we don't have any backup.
Thanks for reply
vendredi 22 décembre 2006 10:59
Can you try this trick:
Create a new 'dummy' database that has the same name as the old database, say 'TEST'
So now you have 2 files: test.mdf and test.ldf files
Stop SQL Server services and delete these files
Copy and rename your corrupted mdf,ldf files in their place
Restart the services and see what error message you get when SQL tries to read from the corrupted files that are now attached to the TEST db.
Then you can start the debugging 'process' based on error number
vendredi 22 décembre 2006 11:15ModérateurTry this undocumented stuff provided by Kevin [MS].
1. Back up the .mdf/.ndf files at first!!!
2. Change the database context to Master and allow updates to system tables:
sp_configure 'allow updates', 1
reconfigure with override
3. Set the database in Emergency (bypass recovery) mode:
select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use in # 6
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
4. Stop and restart SQL server.
5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
The syntax for DBCC REBUILD_LOG is as follows:
where <db_name> is the name of the database and <log_filename> is
the physical path to the new log file, not a logical file name. If you
specify the full path, the new log is created in the Windows NT system
directory (by default, this is the Winnt\System32 directory).
6. Set the database in single-user mode and run DBCC CHECKDB to validate
sp_dboption '<db_name>', 'single user', 'true'
update sysdatabases set status = <prior value> where name = '<db_name>'
-- verify one row is updated before committing
7. Turn off the updates to system tables by using:
sp_configure 'allow updates', 0
reconfigure with override
jeudi 28 décembre 2006 23:58Modérateur
First off, I don't think I've posted those steps. I've probably posted similar for use in DIRE circumstances (like this one) where there is no backup, and data loss is acceptable.
The procedure above is primarily used for cases where you have only the MDF file and no log.
Attaching the database should not hang the system. It could make it busy for awhile, but not totally hang.
How long did you let the system go before giving up and canceling?
Please look in both the Windows Event Log and in the SQL errorlog files and post any related errors here.
You need to get the database attached to an instance in order to do anything with it. Your best bet is to put the files back in their original locations and just let it run its course. You might try putting the database in emergency mode (using the steps above) before putting the files back in place. Then the database wouldn't run recovery when the instance started up.
You could then run DBCC CHECKDB , and presuming that there are serious problems, you can then re-run the CHECKDB with REPAIR_ALLOW_DATA_LOSS, taking into consideration that the command means what it says: data will be lost.
jeudi 24 juillet 2008 07:18
My database was in suspect mode(SQL Server 2005). I had stop SQL Service and delete the suspected backup, now i am trying to attach the mdf file but it's showing the error "error 5133".
Is there any solution for this problem.
Thanks in advance
mardi 2 avril 2013 10:16
If you cannot open your crucial database (.mdf file) then run DBCC CHECKDB command for repairing corrupted .mdf file. If the command didn’t work properly then download this SQL recovery application. This tool is free of charge; you may get it from the website of software developer without any complexity. http://mdf-reco