|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, November 09, 2009 2:41 PM
Points: 2,
Visits: 15
|
|
Is it possible to recover an SQL2005 data from the log file alone?
Thank you
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 5:21 PM
Points: 1,425,
Visits: 1,029
|
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 14,804,
Visits: 10,597
|
|
The log contains only the changes since the last log backup (in full recovery when a full backup was taken at some point) or since the last checkpoint (in simple recovery or full where there's never been a backup). Hence there won't be all the data in the DB and can't recover the entire database.
Where are your backups?
Gail Shaw
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, November 09, 2009 2:41 PM
Points: 2,
Visits: 15
|
|
Thank you for your response.
This is not my database. It is someone's who contacted me for help with recovering after a HDD crash. They said their back up media was bad and nothing could be restored from it.
The log file they have is pretty large: ~1.8 GB, and I don't know how far it reaches into the past, but recovering something is probably better than recovering nothing at this point.
Can you advise me on how I might go about recovering what is in the log file?
Any assistance will be greatly appreciated.
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, November 10, 2009 6:27 AM
Points: 9,
Visits: 20
|
|
Hii
It is not possible dear Sorry..
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 14,804,
Visits: 10,597
|
|
You can buy a log reader (Apex SQL Log is around $1000) and see, but who knows what it's going to retrieve. just because the log is big doesn't mean that there's much in the log. The file may be mostly empty.
Gail Shaw
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 5:21 PM
Points: 1,425,
Visits: 1,029
|
|
A log reader tool can't work without the actual database to get the table metadata, to make sense of what the log records describe.
Gail - the log file doesn't work as you describe. The *active* portion of the log *may* only be as you've described, as long as a bunch of other things aren't holding the log active (just because a log backup/checkpoint has been done, doesn't mean the log isn't still required for something else). The inactive portion of the log is still there and available until it's overwritten when the log wraps.
Regardless though, these is no way at all to get meaningful data from only a log file.
Thanks
Paul Randal
Managing Director, www.SQLskills.com
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair for SQL Server 2005
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 14,804,
Visits: 10,597
|
|
Paul Randal (11/5/2009)
Gail - the log file doesn't work as you describe. The *active* portion of the log *may* only be as you've described, as long as a bunch of other things aren't holding the log active (just because a log backup/checkpoint has been done, doesn't mean the log isn't still required for something else). The inactive portion of the log is still there and available until it's overwritten when the log wraps.
I was just thinking about the portion of the log that hasn't been marked for reuse. Haven't played with log-reader tools yet, but iirc, fn_dblog only reads the portion of the log that hasn't been marked as reusable. Correct?
Gail Shaw
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 5:21 PM
Points: 1,425,
Visits: 1,029
|
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 14,804,
Visits: 10,597
|
|
Oh, ok. I see I need to play more. 
Gail Shaw
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
