Hellyn Saunders
asked on
Attempting to recover SQL DB from mdf and ldf files
Recovering SQL 2008 database from mdf and ldf files.
I have reviewed many previous questions raised here that seem quite similar to my circumstance, and that has led me to make several attempts to solve my issue, but each attempt produces an error.
Using what seems to be the most commonly recommended action...
When using Attach (right click on Databases within SQL Management studio) to attach my existing mdf and ldf files, I get an error (see attached). This suggest performing DBCC CHECKDB.
However - don't I have to have the database attached in order to run that process?
Thanks in advance for any assistance
I have reviewed many previous questions raised here that seem quite similar to my circumstance, and that has led me to make several attempts to solve my issue, but each attempt produces an error.
Using what seems to be the most commonly recommended action...
When using Attach (right click on Databases within SQL Management studio) to attach my existing mdf and ldf files, I get an error (see attached). This suggest performing DBCC CHECKDB.
However - don't I have to have the database attached in order to run that process?
Thanks in advance for any assistance
Vadim Rapp
Indeed, start with checking "additional messages in sql server error log and in system event log" - especially the latter.
You might find the process here helpful
https://www.datanumen.com/sql-recovery/articles/dbcc-checkdb.htm
https://www.datanumen.com/sql-recovery/articles/dbcc-checkdb.htm
ASKER
Thank you Vadim for taking some time to view my question.
Prior to posting here, I did view the other logs. The same error message appears in those 2 locations and then subsequent statements that the db seems to be corrupted. Hence I came here looking for more knowledgeable people than myself who might have some useful information.
Prior to posting here, I did view the other logs. The same error message appears in those 2 locations and then subsequent statements that the db seems to be corrupted. Hence I came here looking for more knowledgeable people than myself who might have some useful information.
ASKER
Thank You ArneLovius for the suggestion.
I have consulted that article, and attempted the commands suggested there. But I seem to be in the same situation.
That article says to use (amongst others) the statement
set @databasename = 'Mydatabase.mdf'
[In my case my file is AwardPro.mdf]
Execution fails because AwardPro.mdf is not a known database. If I use a logical database name, not the physical database name (.mdf file) then the commands run successfully - but of course they are not checking my suspect .mdf because that is not the file attached to the logical database name.
The crux of my problem seems still to be that I am unable to attach my .mdf file.
I have consulted that article, and attempted the commands suggested there. But I seem to be in the same situation.
That article says to use (amongst others) the statement
set @databasename = 'Mydatabase.mdf'
[In my case my file is AwardPro.mdf]
Execution fails because AwardPro.mdf is not a known database. If I use a logical database name, not the physical database name (.mdf file) then the commands run successfully - but of course they are not checking my suspect .mdf because that is not the file attached to the logical database name.
The crux of my problem seems still to be that I am unable to attach my .mdf file.
> The same error message appears in those 2 locations
The message "attach database failed" appears in system event log? please double-check. It might be in application log, but not in system.
Also: copy the file to another hard drive and try to attach from there.
If you are able to copy the file, but getting the same error when attaching it, check file permissions, and just for testing, set full permissions for everyone (make sure to have a copy of the file), and try again.
If still the same, run process monitor, monitor file activity while attaching the file and hitting the error, and post the collected log here.
The message "attach database failed" appears in system event log? please double-check. It might be in application log, but not in system.
Also: copy the file to another hard drive and try to attach from there.
If you are able to copy the file, but getting the same error when attaching it, check file permissions, and just for testing, set full permissions for everyone (make sure to have a copy of the file), and try again.
If still the same, run process monitor, monitor file activity while attaching the file and hitting the error, and post the collected log here.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Vadim, and thanks for being involved.
You are correct, I was looking in the Windows application log, not the system log. There are no entries in the system log for the time I get my error attempting to attach the mdf file.
the DBCC CHECKPRIMARYFILE returns a single row with a single value of 1 in the column IsMDF - which I am interpreting to be confirmation that my file is an mdf.
Have retried and attached Process Monitor Log here - I hope I have captured and attached what you intended me to!
Logfile.PML
You are correct, I was looking in the Windows application log, not the system log. There are no entries in the system log for the time I get my error attempting to attach the mdf file.
the DBCC CHECKPRIMARYFILE returns a single row with a single value of 1 in the column IsMDF - which I am interpreting to be confirmation that my file is an mdf.
Have retried and attached Process Monitor Log here - I hope I have captured and attached what you intended me to!
Logfile.PML
Your log does not show a single trace of any .mdf file. Please try again:
1. start ssms; right-click Databases", select "Attach"; then "Add..."; in the dialog "Locate database files" navigate to the folder where you have mdf file, and select it, but don't press "OK" yet
2. start process monitor; specify filtering like on this screenshot (ignore red "Exclude" lines representing my own computer; only the first one, "Include", is important)
Press OK , then go back to SSMS and press OK there. You should see entries in Process Monitor like this:
1. start ssms; right-click Databases", select "Attach"; then "Add..."; in the dialog "Locate database files" navigate to the folder where you have mdf file, and select it, but don't press "OK" yet
2. start process monitor; specify filtering like on this screenshot (ignore red "Exclude" lines representing my own computer; only the first one, "Include", is important)
Press OK , then go back to SSMS and press OK there. You should see entries in Process Monitor like this:
ASKER
Here is a new Process Monitor Log.
Note that all of this activity takes place between the 2 "OK"'s in SSMS.
When I first go and locate the file within the directory structure, there is an OK to select the file. I didnt think this was the OK you were referring to, but for completeness I have monitored this OK as well. When I click this OK there is activity shown in the process monitor. But when I click OK (to actually perform the attach process) I do not see any further activity in the process monitor.
Logfile.PML
Note that all of this activity takes place between the 2 "OK"'s in SSMS.
When I first go and locate the file within the directory structure, there is an OK to select the file. I didnt think this was the OK you were referring to, but for completeness I have monitored this OK as well. When I click this OK there is activity shown in the process monitor. But when I click OK (to actually perform the attach process) I do not see any further activity in the process monitor.
Logfile.PML
The core problem is: your mdf/ldf files are corrupt. Thus you cannot attach it, nor run DBCC over it.
Just restore your backup and start over.
The only thing I would try: Just restore the mdf. Cause the error message reads like the log is corrupted.
Otherwise you may look into some of the tools out there, which claim to be able to repair corrupted files, but I don't have any experience with these tools. So I cannot tell, whether they are worth any money. Cause even with these tools, data loss is often inevitable.
Just restore your backup and start over.
The only thing I would try: Just restore the mdf. Cause the error message reads like the log is corrupted.
Otherwise you may look into some of the tools out there, which claim to be able to repair corrupted files, but I don't have any experience with these tools. So I cannot tell, whether they are worth any money. Cause even with these tools, data loss is often inevitable.
OK, process monitor log does not show anything to the effect of inability to read the file. Still, to completely rule out anything related to the i/o, I would try to copy mdf file elsewhere and restore from there. Even better, try it on another machine (or virtual machine), where you can install sql server express. If you try that, you could also try different versions of sql server, it's not impossible that another version would suddenly get through.
Disable any antivirus.
Also, Ste5an made good suggestion to bypass the log. Even though the "main" portion of the message mentions mdf, later it says that this happened when "redoing of a logged operation" - although, one would think that "redoing" would result in writing to the mdf, while the error happened when reading it, and procmon does not show any writing. Maybe you could investigate further if you run procmon again, and specify the filter not "contains mdf" but "contains "sqlservr.exe"", so we see all file activities.
To bypass the log, see https://www.stellarinfo.com/blog/attach-sql-database-without-transaction-log-file/
And, you did not tell if you found anything in system event log.
Disable any antivirus.
Also, Ste5an made good suggestion to bypass the log. Even though the "main" portion of the message mentions mdf, later it says that this happened when "redoing of a logged operation" - although, one would think that "redoing" would result in writing to the mdf, while the error happened when reading it, and procmon does not show any writing. Maybe you could investigate further if you run procmon again, and specify the filter not "contains mdf" but "contains "sqlservr.exe"", so we see all file activities.
To bypass the log, see https://www.stellarinfo.com/blog/attach-sql-database-without-transaction-log-file/
And, you did not tell if you found anything in system event log.
ASKER
Thanks everyone for assisting in our time of "need".
The final solution was to use the stellar recovery software.
However this is not free (far from it) as they try to make you think in their promotion of the product.
But it did save us - so it was money well spent in the end.
The final solution was to use the stellar recovery software.
However this is not free (far from it) as they try to make you think in their promotion of the product.
But it did save us - so it was money well spent in the end.