July 9, 2009 at 4:29 am
Hi Friends,
I am using SQL Server 2005 ,by mistake i run update query without where condition.
is there any way to recover data which was there before update statement executed.
Thanks in Advance
Pratik Asthana
July 9, 2009 at 4:42 am
If the DB is in full recovery and there's an existing full backup you can back the log up then restore the database (I suggest alongside the existing one with a different name), restore the log with the STOPAT option and stop before the update ran.
If you're in simple recovery, the log will be truncated when a checkpoint occurs (usually around every minute) and so there won't be anything in the log shortly. You can try buying a log reader tool (apex has one, so do a couple other companies) and seeing if there's anything there, but chances are slim.
The only free log reader that I know of is RedGate's and that only works on SQL 2000.
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, 2009 at 4:47 am
Hi,
as said if you have full backup and transaction log backup.. then.. find the below syntax
restore database mydb from disk = ‘c:\mydb_full.bak’ with norecovery
restore log mydb from disk = ‘c:\mydb_log1.bak’ with norecovery
restore log mydb from disk = ‘c:\mydb_log2.bak’ with recovery, stopat =
‘Aug 20, 2008 1:01 PM
July 9, 2009 at 5:02 am
You won,t be able to rollback the data once it is committed. You can try doing a point in time recovery & get back the lost data.
If not you can use the SQL Log Rescue tool. For more details refer to http://www.red-gate.com/products/SQL_Log_Rescue/index.htm
----------------------------------------------------------------
**"There is only one difference between dream and aim. Dream requires Soundless sleep to see…Where as Aim Requires Sleepless Efforts to Achieve":-) **
July 9, 2009 at 8:52 am
SQL DBA (7/9/2009)
If not you can use the SQL Log Rescue tool. For more details refer to http://www.red-gate.com/products/SQL_Log_Rescue/index.htm
Log rescue works on SQL 2000 , not SQL 2005.
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