undo a sql statement

Question

undo a sql statement

scottichrosaviakosmos

SSCommitted

Points: 1889
More actions
July 26, 2010 at 4:26 am

#225384

is it possible to undo an update statement which has updated my table .
i was not useing any transaction but i directly updated it.
now i want to recover old data and i dont have back also.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

I cant think of anyway apart from back-ups.. as u said no back-ups and u dint open an explicit transaction, then i'm afraid your data is lost..

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Restore from backup is the way to 'undo' changes. If you have no backup (why not?) then there's no practical way of undoing.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 3

Is the db is full recovery mode?

If so, the old data is still on the transaction log.

Software is available that can pull that data from the log. Unfortunately, I think all such software now comes with an up-front $ cost.

If the db is in simple mode, do an immediate tran log backup, as that is the only chance you have of keeping the data, although it might already have been truncated from the log.

Scott Pletcher, SQL Server MVP 2008-2010

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

out of interest...what excactly did the update statement update?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 5

There is no undo. Log software, as Scott mentioned, builds a reversing transaction, but it cannot undo the transaction.

Restore from backup to another system, and then copy the data back. If the data from the full backup is not what you want, and you have log backups, then log software might still be needed.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

scott.pletcher (7/27/2010)
Software is available that can pull that data from the log. Unfortunately, I think all such software now comes with an up-front $ cost.

$1000+ last time I checked.

If the db is in simple mode, do an immediate tran log backup,...

Log backups aren't possible in Simple recovery as the log is auto-truncated on checkpoint.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 7

Yes, you have to change the db recovery mode and do a full backup first (or tell SQL you have done one). Then you can do a log backup.

And, as I noted, yes, that is an extreme long shot. Most likely in simple mode the table data has already been truncated from the log.

Scott Pletcher, SQL Server MVP 2008-2010

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

scott.pletcher (7/27/2010)
Yes, you have to change the db recovery mode and do a full backup first (or tell SQL you have done one). Then you can do a log backup.

I don't see why that would be useful. With the full backup starting the log chain, the log backup will only contain the log records from after the full backup.

"Or tell SQL you have done one"

?? Elaborate please?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 9

Perhaps. I thought SQL would still back up all active parts of the log. I was trying to cover all possibilities, since otherwise OP is in for a total data loss.

Scott Pletcher, SQL Server MVP 2008-2010

ross.cecil Mr or Mrs. 500 Points: 543 More actions · Answer 10

For a special application, I've used triggers to record all changes to "backup" table. This allows modifications to be undone. It's a lot of code but the requirement was that any change had to be capable of being backed out.