SQL update statement issue

Question

5.00/5 (2 votes)

See more:

I have an issue that, while using sql server 2005 i have executed update query without where clause (by mistake) and all the original values of the column get lost.

How can I get old values??

Any suggestion/tips/solution is welcome and will be highly appreciated.

Posted 29-Jan-12 3:17am

Shahan Ayyub

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Amir Mahfoozi · Accepted Answer · 2012-01-29T04:11:00

Solution 2

You need a SQL Transaction Log viewer.
Good news is that most of them are capable of undoing your mentioned last update command within a simple click:
http://www.apexsql.com/sql_tools_log.aspx[^]

http://www.red-gate.com/products/dba/sql-log-rescue/[^]

Bad news is that some of them are very expensive and some of them may not compatible with your SQL server version.

Hope it helps.

Posted 29-Jan-12 4:11am

Amir Mahfoozi

Comments

thatraja 29-Jan-12 12:34pm

Nice tools, 5!

Amir Mahfoozi 29-Jan-12 13:26pm

Thanks thatraja :)

OriginalGriff · Accepted Answer · 2012-01-29T03:25:00

Solution 1

Oops.
Restore from your last backup is the first, best option - you do backup regularly, don't you?
Unfortunately, if you can't restore, then you need to restore from log files. Time to start reading (and praying...) http://www.techrepublic.com/blog/datacenter/restore-your-sql-server-database-using-transaction-logs/132[^]

Posted 29-Jan-12 3:25am

OriginalGriff

Comments

Shahan Ayyub 29-Jan-12 9:30am

I maintains backup but I did not took backup since last week and I have a dozen of updates in it within this time period. So is it possible to get the data back with your suggested solution ?

OriginalGriff 29-Jan-12 9:41am

It may be - start reading, but don't expect it to be simple!

thatraja 29-Jan-12 12:33pm

Right, 5!

mimran18 · Accepted Answer · 2012-02-05T00:49:00

Hi,
SQL server keeps log for every transation.So you can recover your modified data from the log as well without backup.
Here is the sample source code.

SQL

Select [PAGE ID],[Slot ID],[AllocUnitId],[Transaction ID] ,[RowLog Contents 0]
, [RowLog Contents 1],[RowLog Contents 3],[RowLog Contents 4] ,[Log Record]
FROM    sys.fn_dblog(NULL, NULL)
WHERE AllocUnitId IN
(Select [Allocation_unit_id] from sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2  AND partitions.partition_id = allocunits.container_id)
Where object_id=object_ID('' + 'dbo.student' + ''))
AND Operation in ('LOP_MODIFY_ROW','LOP_MODIFY_COLUMNS')
And [Context] IN   ('LCX_HEAP','LCX_CLUSTERED')

Here is the artcile, that explains step by step, how to do it.
http://raresql.com/2012/02/01/how-to-recover-modified-records-from-sql-server-part-1/