• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 468
  • Last Modified:

Unable to open database file (mdf)

Hi Experts!

My mdf file was deleted.  I was able to restore the said file but unfortunately I can no longer open it.

I have also noticed that the recovered mdf file was smaller than the file before it was deleted.

What can I do to open the database file?  
0
matley
Asked:
matley
  • 7
  • 6
  • 2
  • +2
4 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
Do you have any backups available ?
Otherwise try to recover the deleted file using some softwares like  recover4all, norton uneraze  etc
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<I was able to restore the said file but unfortunately I can no longer open it>>How do you try to open it?
0
 
imran_fastCommented:
I think you should try detach the database  and then try  attaching it using this mdf file.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
matleyAuthor Commented:
Racimo,

I am trying to open it through Access adp file.  I used to open it this way.

imran_fast,

I have detached the database through this code:

Sub DetachDB()
Dim strExec As String
strExec = "sp_detach_db 'db1sql1', 'true'"
CurrentProject.Connection.Execute strExec
End Sub

I actually got an error message after execution but it was removed.

I have tried to attached the database through this code:

Sub AttachDB()
Dim strExec As String
strExec = "sp_attach_db @dbname = 'db1sql1', @filename1 = 'C:\Data\Data\db1SQL1.mdf', @filename2 = 'C:\My Documents\Data\db1SQL1.ldf'"
CurrentProject.Connection.Execute strExec
End Sub

On first attempt, I got this error:
"The LSN (96:57:1) passed to log scan in database 'db1sql1' is invalid."

On succeeding attempts, I got this error message:
"Connection failure"

I have verify the database connection to server - it was not attached.
0
 
Anthony PerkinsCommented:
>>I am trying to open it through Access adp file.  I used to open it this way.<<
In a word: Don't.

Since you appear not to have any backups you will have to re-enter the data.  Sorry to be the harbinger of bad news.
0
 
matleyAuthor Commented:
acperkins,
>>In a word: Don't.<<
Don't open the mdf file in Access adp file?

How about the database objects?
The Table, view, stored procedure and trigger, is there a way I can recover them?
0
 
imran_fastCommented:
Can't you reattach the file using enterprise manager or sql query analyzer.
0
 
matleyAuthor Commented:
imran_fast,

I can't. I'm using MSDE.
0
 
Anthony PerkinsCommented:
>>Don't open the mdf file in Access adp file?<<
Correct.  You can certainly open a SQL Server database in an MS Access ADP database just not the mdf file.   I mean you could open the SQL Server database if it was not already corrupt.  Since that is the case and you appear not to have any backups you have two choices:
1. Purchase some third party tool such as:
SQL Log Rescueâ„¢
Log analysis and undo for SQL Server
http://www.red-gate.com/products/sql_log_rescue/index.htm
In the hopes that you may be able to recover something.

2. Re-enter the data.
0
 
matleyAuthor Commented:
acperkins,
Can I recover my database objects using SQL Log Rescue?  Since I can reproduce the data, I only need to recover my database objects.
0
 
Anthony PerkinsCommented:
>>Can I recover my database objects using SQL Log Rescue?<<
I have no idea.  Why don't you try it out.  At this point you have nothing to lose.
0
 
matleyAuthor Commented:
acperkins,

I got nothing from SQL Log Rescue.  It even doesn't execute at all (demo version).

Anyway, I was able to attach and use the database using osql utility.
I have also tried to open the database through VB Data View but returned this error:

Connection failed:
SQLState: '42000'
SQL Server Error: 4060
Cannot open database requested in login 'db1sql1. Login fails.

What could be the next steps I can do?
0
 
Anthony PerkinsCommented:
>>I got nothing from SQL Log Rescue.<<
Than I am afraid, you are out of luck and will have to re-create all the schemas and re-enter all the data.

Sorry to be the harbinger of bad news.  If it is any consolation we have all done it once and learned a great lesson from it:  Backup and backup often.
0
 
Anthony PerkinsCommented:
Please re-read the EE Guidelines regarding grading Standards at:

What's the right grade to give?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi73

And in particular this section:

<quote>
C: Because Experts' reliability are often judged by their grading records, many Experts would like the opportunity to clarify if you have questions about their solutions. If you have given the Expert(s) ample time to respond to your clarification posts and you have responded to each of their posts providing requested information; or if the answers, after clarification, lack finality or do not completely address the issue presented, then a "C" grade is an option. You also have the option here of just asking Community Support to delete the question.
Remember, the Expert helping you today is probably going to be helping you next time you post a question. Give them a fair chance to earn an 'Excellent!' grade and they'll provide you with some amazing support. It's also true that a "C" is the lowest grade you can give, and the Experts know that -- so use it judiciously.

Only the Moderators and Page Editors have the choice to give a D grade. Beyond that, in a practical sense, the grading guidelines have "softened" a bit over the last year or two; one might expect that the majority of grades would be Bs (a standard "bell" curve), but the fact is that the culture of the site has caused there to be an inordinately high percentage of As. The Moderators have been instructed to ensure that the As they award are actually "Excellent" answers. Similarly, the C grade is the lowest that can be given by a member, a fact which should be kept in mind when grading as well.

The use of a C in a vindictive manner is likely to be changed by a Moderator. You may not like the answer you get, and in some cases, and you may not like the way it is delivered, but if it is deemed to be accurate, no less than a B is an acceptable grade.

</quote
0
 
matleyAuthor Commented:
I am very sorry. I thought not getting a solution would mean a grade of C.

I would like to ask the moderator to change the grade from C to B.

I appreciate everyone's reply.  Thanks to all.
0
 
Anthony PerkinsCommented:
>> I thought not getting a solution would mean a grade of C. <<
Nope.  See here from the EE Help:

The correct answer to some questions is "you can't do that"
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi54


>>I would like to ask the moderator to change the grade from C to B.<<
This is how it works here:

Can I get a grade changed?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi18
0
 
Anthony PerkinsCommented:
>>I would like to ask the moderator to change the grade from C to B.<<
Do you need more help with that?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 6
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now