Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1878
Members Online:
2
Guests Online:
99

Total Post History
Posts:
81448
Topics:
18714

7-Day Post History
New Posts:
1
New Topics:
0
Active Topics:
1

Our newest member
kowilmar1990z

Other

FAQ
All times are UTC [ DST ]

Google Ads

corrupt mdf with no ldf

Please wait...

corrupt mdf with no ldf

Postby kofriste on Thu Sep 06, 2012 11:48 am

Hey I got a mdf file which is corrupt and there is no ldf file or backup. There were uncommitted transactions in the file and now i cant recover it.

if i run

  1. sp_attach_single_file_db @dbname='mydatabase'
  2.         ,@physname=N'C:\database.mdf'


i get

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.


can i get this to recover??????
kofriste
Newbie
Newbie
 
Posts: 2
Joined: Thu Sep 06, 2012 11:39 am
Unrated

Re: corrupt mdf with no ldf

Postby Axel8s on Thu Sep 06, 2012 1:02 pm

The only procedure I've ever seen for this was described by Paul Randal in this post: http://www.sqlskills.com/blogs/paul/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx

But as you will read in his post, you will lose some data.

Good luck
Axel
User avatar
Axel8s
Apprentice
Apprentice
 
Posts: 7
Joined: Tue May 03, 2011 12:43 pm
Unrated

Re: corrupt mdf with no ldf

Postby kofriste on Thu Sep 06, 2012 1:17 pm

No i cant do that cos i dont have the ldf file like he does. if i try a similar thing like to do new database with the same name, stop the server but overwrite the new mdf with my mdf it still complains as my mdf does not match the new database ldf, it must have an internal pointers to show they match.

i can use demo tool like sqlserver.fixtoolboxx.com to see the data but i cant pay for full version. if they can see and get to data to be able to recover it then there must be a way for me to do it.

thanks
kofriste
Newbie
Newbie
 
Posts: 2
Joined: Thu Sep 06, 2012 11:39 am
Unrated

Re: corrupt mdf with no ldf

Postby davegeeit on Thu Nov 29, 2012 8:30 am

To recover corrupt mdf database file, i would suggest you to use recoveryfix for sql database repair software, Good thing is that this company offering a free trial version of software, with the help of this trial version you can see the preview of recoverable data.you can download this software from here:

http://www.repairsqlserver.net

Thanks
davegeeit
Newbie
Newbie
 
Posts: 1
Joined: Thu Nov 29, 2012 8:25 am
Unrated

Re: corrupt mdf with no ldf

Postby Thirster42 on Thu Nov 29, 2012 4:49 pm

nuke the database and restore from the latest backups?
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: corrupt mdf with no ldf

Postby onpnt on Thu Nov 29, 2012 7:24 pm

Can you expand on what you think it corrupted in the file? Was it corrupted and then sent to you to fix? are you sure it simply isn't missing a log file (which you can easily get back online if that is the case)

Do this on an instance you can restart at will. Local machine or something that has enough space.

Create a database with the same name (and physical file name). Cleanly shutdown the instance. Copy the physical mdf file to a new location (retain it for later use if needed). Put the so called corrupt mdf physical file into the location the new database physical file was (leave the ldf that was created alone). Start the instance and see if the DB came up in suspect. You will lose data if it really has inconsistency issues due to allow with data loss but this may get the DB attached and available to work on the corruption
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: corrupt mdf with no ldf

Postby onpnt on Thu Nov 29, 2012 7:25 pm

BTW...if you read the link that Axel provided by Paul Randal, you would have seen the ldf was missing and the same steps I just told you were in there. So technically, the solution was already presented to you :)
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: corrupt mdf with no ldf

Postby langm8029 on Wed Nov 19, 2014 8:31 pm

This will only work if the SQL Server was stopped when you copied the MDF file from the old SQL Server. If you copied the file while the SQL Server was running this file probably will not attach either and you will need to get a valid backup of the database. Download help tool for sql database - SQL Server Restore Toolbox. http://www.sqlserver.restoretools.com/ You can read up more on SQL Server here. http://www.filerepairforum.com/forum/microsoft/microsoft-aa/sql-server/498-creating-a-new-database-using-an-mdf-file?_=1416149856104
langm8029
Newbie
Newbie
 
Posts: 1
Joined: Wed Nov 19, 2014 8:31 pm
Unrated

Re: corrupt mdf with no ldf

Postby johnwalker10 on Sat Jun 20, 2015 7:03 am

In such a situation, I would like to suggest you to try SQL Database Repair Tool, this tool will help you to recover your corrupt MDF file without LDF file or backup.
To read more and download free demo version, visit: - http://www.sqlrepairtool.org
johnwalker10
Newbie
Newbie
 
Posts: 1
Joined: Sat Jun 20, 2015 7:03 am
Unrated