Would it be possible rollback transactions using Transactionlog file for a particular record?
I am using SQL Server 2005.
Add a comment |
4 Answers
Natively, no. I believe there are some pricey third party tools to do this, though.
Alternatively, you can restore your DB from a backup, and then RESTORE LOGS to a point in time with the STOPAT = '6/30/2009 2:30PM' argument.
There are Apex and SQL Log Rescue available. If your logs aren't in FULL recovery mode, though, you may be up a creek if you can't restore.
3 Comments
THEn
Can you give me a sample of these tools please?
Remus Rusanu
The recommended way is to restore a copy of the database with STOPAT and then copy over the data from the copy into the real database.
Sphynx
How do you find out what recovery mode your logs are in?
From what I know your options are ApexSQL Log (pricey but has a free trial) or undocumented SQL Server commands such as DBCC Log and fn_dblog.
Any of these will only work if your database was in full recovery mode, in which case SQL Server stores a lot more details in transaction log.
Also I don’t think this works for any type of transaction. I’ve seen people using this technique for reverting insert, update, delete but I’m really sure if it can be used to revert say ALTER TABLE or something like that.
1 Comment
viggity
the free trial will only generate undo scripts for every 10th record.
As far as third-party software goes, you can use Dell's Toad software free for thirty days, and it has transaction logs with undo script generation capability.
Comments
Check out RedGate's LogRescue product. Without 3rd party products, you're probably limited to doing a point in time restore to a separate database and then manually copying the record back to your production database.
1 Comment
Samuel Jack
According to its webpage, LogRescue is limited to SQL Server 2000 only