April 14, 2004 at 1:40 pm
Does anybody know if there is a way to recover the data from a corrupted MDF file?
One disk that cotains a database was accidentally formated, after that we recovered all the files with a utility, but one file of a database seems to be corrupted since SQL says that the .MDF file is not a database file. Other databases and files on the same disk was recovered OK.
Thanks.
April 14, 2004 at 1:57 pm
Do you have recent backups of that database?
April 14, 2004 at 2:05 pm
We have a backup, but its about a month ago.
April 15, 2004 at 10:17 pm
mdaniel,
As far as I know you cannot "repair" a .mdf file. You will have to restore your database from backup.
In future make sure you have regular backups of your database.
Cheers,
Angela
April 16, 2004 at 9:21 am
Thanks, I knew that but just wanted to confirm.
All of us thought that our DBA was making backups. But he wasn't!!!
May be we need a new DBA.....
April 16, 2004 at 12:40 pm
If you have the .mdf file available and originally you had only one transaction log file, you can try the following:
1. Run sp_attach_single_file_db in QA like in the following example:
EXEC sp_attach_single_file_db @dbname = 'pubs',
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
2. If step 1 didn't help, you can try:
- rename the original .mdf file that you have adding for example the suffix _old before the extension
- create a new database with exactly the same name and same logical and physical location for the .mdf and .ldf files as the original database that you are trying to recover. If you don't know the names anymore, you can find out what they were running RESTORE FILELISTONLY from an old backup
- stop SQL Server
- replace the new created .mdf file with the original one that you've renamed before.
For example if the new .mdf file name for the new database is pubs.mdf and the original is pubs_old.mdf, rename pubs.mdf to pubs_new.mdf and pubs_old.mdf to pubs.mdf
- restart SQL Server. If nothing else wrong, the database will be marked suspect.
- put the database in bypass emergency mode. You have to change the status in master..sysdatabases to 32768 for the database
- if you were able to put the database in emergency mode you have the options:
May 8, 2014 at 8:06 am
Please note: 10 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply