December 5, 2008 at 11:57 am
Hello all. Is there any tool that I can use to be able to do the following:
1. Recover a full database back up
2. Apply all transaction logs before data corruption occurred.
3. Open the transaction log that took place after data corruption took place, delete the transactions that corrupted the data, save the transaction log, and apply this new saved transaction log to the database.
December 5, 2008 at 12:16 pm
juancabrer (12/5/2008)
3. Open the transaction log that took place after data corruption took place, delete the transactions that corrupted the data, save the transaction log, and apply this new saved transaction log to the database.
Definitely not. The transaction log cannot be messed with. If you try to delete transactions from the log, you'll make it unusable for a restore.
What do you mean by corrupted? Database corruptions are very unlikely to be logged at all, seeing as they are mostly IO system corruptions
You can use STOP AT in the restore log to not restore the entire thing.
Perhaps you can give us some background to your question. There's probably a better way.
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
December 6, 2008 at 9:07 am
As Gail said, use RESTORE WITH STOPAT to recover your database to the point before the corruption occured (roughly as follows)
1) Restore the full database backup. Use the STOPAT and NORECOVERY options. Note that although STOPAT is meaningless for full and differential backups, the syntax allows it so that all restores you perform can have the same options specified. NORECOVERY is necessary to allow subsequent log backups to be performed.
2) Restore all log backups up to the point of the corruption. Use the same STOPAT and NORECOVERY options on each one.
3) Immediately perform a full backup of the database once you know you've reached a point with no corruption.
By doing #3, you create a new recovery point which means that if disaster strikes again, you don't need to go through #1 and #2 again. This is what you were trying to achieve with the #3 form your original post.
Hope this helps.
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
December 6, 2008 at 10:06 am
Gila and Paul, Thanks so much for your input. Apparently this steps can only be performed using SQL Scripts, unless I'm wrong is this possible to do from the management console?
December 6, 2008 at 1:07 pm
2) Restore all log backups up to the point of the corruption. Use the same STOPAT and NORECOVERY options on each one.
3) Immediately perform a full backup of the database once you know you've reached a point with no corruption.
2.5 RESTORE DATABASE ... WITH RECOVERY to bring it online, once you are sure that you've reached the point that you want to stop the restore at.
Apparently this steps can only be performed using SQL Scripts, unless I'm wrong is this possible to do from the management console?
It can be done from management studio, but in most cases I'd prefer to do it with scripts so that I can double check before running.
I've had a case where a colleague used the gui told to do a restore and forgot to put NORECOVERY on the first of the tran log restored. Result - 6 wasted hours and an even later night than we were expecting. With scripts it would be easy to see that one was missing the needed option
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply