July 9, 2008 at 6:09 am
Hi,
Is it possible to backup an sql database to a readable transaction log, so i will be able to read all the sql statements?
Thanks
July 9, 2008 at 6:37 am
you will need a third part tool for this, redgate and lumigent sell them.
---------------------------------------------------------------------
July 9, 2008 at 7:24 am
Why you want to read the Tlog?
MCP, MCTS (GDBA/EDA)
July 9, 2008 at 7:43 am
I would like to see transactions that cause problems, and remove them, and restore the db without them.
July 9, 2008 at 7:47 am
the tools sold can 'reverse engineer' the code to undo a particular transaction.
---------------------------------------------------------------------
July 9, 2008 at 7:56 am
I'll check them out.
Thanks.
July 9, 2008 at 10:03 am
Restore the previous Full Backup and you will be on track.
MCP, MCTS (GDBA/EDA)
July 9, 2008 at 11:28 am
yes, but if the backup failed, i'm stuck!!
one time the IT guy took out the wrong hot swap drive, and the db got corrupted, so if i would rollback to last night back up i would loose all of todays transactions.
July 9, 2008 at 11:47 am
Test your backups after you make them. An untested backup is more dangerous than no backup at all.
Make sure you're running log backups if you need to be able to restore to a point in time. I would suggest 2 full backups and the transaction log backups since the earliest. That would give you a fair amount of freedom in how to restore in case of a disaster.
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
July 9, 2008 at 12:26 pm
To restore 20gig db takes some time. do you have an idea how to test the backup quick, without wasting time?
July 9, 2008 at 12:48 pm
Shouldn't take that long. I've got a 800 GB database that restores in just over an hour. If it's really slow, check your disk speed and performance
You can restore with verify_only. If it gives an error, your backup's no good. However it can succeed where the complete restore fails.
Ultimately, the only way to be 100% sure that a backup will restore is to restore it.
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
July 9, 2008 at 2:08 pm
I like this verify_only idea. it's another step in the right direction. better then nothing.
I think i'll do it.
Thanks
July 9, 2008 at 3:07 pm
Yes, I'll agree 100%. Not performing periodic restores of your backups is not being diligent. If you don't test the restores, you can't rely on the quality of the backup plan. You need to create a well thought out backup/recovery/disaster plan and test it.
With that said, it sounds to me like you have some occasional bad updates correct? You don't need to restore and start from scratch. Why not restore the backup to a copy of your database and then selectively update your production data from the backup thereby fixing the problematic update? Yes, you can buy third party software that can reverse engineer the transaction log and spit out a backout statement, but you can do that all on your own with restoring a copy, doing the analysis, writing your own UPDATE statement.
July 14, 2008 at 8:56 am
I just did a recent evaluation on the 3rd party log reader tools. We had a situation where data was mysteriously being deleted and wanted to read the log to find out more. RedGate is free but is only for SQL 2000. Lumigent and Apex seemed to be the leading options for SQL 2005. I personally preferred the Apex product but must say both are very good for what they do. These products are also fairly inexpensive.
Tim White
July 14, 2008 at 9:20 am
Thank you very much for sharing this information.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply