November 4, 2009 at 4:45 pm
Is it possible to recover an SQL2005 data from the log file alone?
Thank you
November 4, 2009 at 5:30 pm
Short, simple answer - no.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
November 4, 2009 at 7:35 pm
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
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 4, 2009 at 11:39 pm
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.
November 5, 2009 at 12:34 am
Hii
It is not possible dear Sorry..
November 5, 2009 at 9:02 am
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
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 5, 2009 at 9:32 am
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
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
November 5, 2009 at 10:02 am
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
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 5, 2009 at 10:09 am
You can make it go back as far as any still-existing LSN.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
November 5, 2009 at 10:17 am
Oh, ok. I see I need to play more. :blush:
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
November 9, 2009 at 1:16 pm
Todd Williams-416222 (11/4/2009)
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.
i would find out what is meant the backup media is bad and HDD crash. one time i had a case with Symantec open for a month because my netbackup catalog on an old server was corrupt and i had trouble importing 5 year old backup tapes. it's a sybase backend and they had me run a few commands to clear out a table or two and then i was able to import my tapes. a few weeks ago i got an email about a bug in Netbackup that deletes catalog data but if you take the tapes out you can still import them manually and get your backups back
you can find 10 free HD recovery tools by googling and there are professional data recovery services that can recover data. I think the US Government standard for sensitive data is 5 passes of flipping the bits. For classifed data we had to take out the hard drive, take it apart and physically destroy the platters with wire brushes. been a long time but from what I read is that even if you flip every bit on a HD, you can still recover a lot of the data if you know what you're doing.
November 23, 2009 at 9:54 am
http://www.vogon-international.com/
Not cheap - but if the data is valuable (and why would you have it otherwise)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply