November 26, 2002 at 2:45 pm
One of my customers has caused a problem with their database (a large portion of the data has been deleted). They haven't made any regular backups unfortunately. Is there anyway to use the transaction log to "roll back" the changes that caused the problem? Can I backup the database and restore up to a point with the transaction log? I tried a point-in-time restore, but it won't let me go back prior to the time of the backup I made. HELP!
November 26, 2002 at 5:15 pm
No you cannot backup from the Transaction Log in that manner except with a LogExplorer which has this built into the tool. Never tried it so not sure how well it works or if can do to a Transaction that occurred before it is installed. Also, if the log has been set to truncate on checkpoint or has been backed up with truncate then the transaction is lost and even LogExplorer will not help.
November 26, 2002 at 5:22 pm
Agree with Anatares. I think LogExplorer will attach to your log and work, but without regular backups, you may be stuck.
Steve Jones
November 26, 2002 at 11:25 pm
I dont know about log explorer but with your query you can restore old backup to another database and restore full tlog backup and now copy the tables accidently truncated to the orig database.
or
I think log explorer should be having a feature to restore portion of the log to any other database having same structure, you can do that and then copy data back to the orig server
hope it helps
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
November 27, 2002 at 8:06 am
Prakash is right as long as the transaction log is intact from the last time they did any backup then you could restore to another DB the last full, backup the current TL, and restore to the other server. However I would be carefull in that I beleiev backing up the TL will mark most of it as removable at that point and LogExplorer may not work.
So expanding upon Prakash's concept. Stop the SQL Server and copy the DB and TL to another server and use sp_attach_db to attach the copy to the other server. This way you can make a TL backup and can be sure none of the transaction log is lost on the first server, then go thru the steps Prakash states. It might work but again depends on that the entire TL be intact since the last full backup they made.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply