SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ROLLBACK Transaction


ROLLBACK Transaction

Author
Message
sridhar.tondapi
sridhar.tondapi
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 27
Gurus,

In a session i have run the UPDATE statement..but accidentely i pressed EXECUTE..

Steps:

1. update statement without WHERE clause.
2. Click on 'EXECUTE'.

So is there any way that i retrieve the old data for the table...

Please help,

NOTE: I have not done any DECLARE for transaction.



Regards,
~Sri
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5401 Visits: 4639
It looks to me your transaction got commited.

How about point-in-time recovery?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
maechismo_8514
maechismo_8514
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1848 Visits: 2228
Do you have any backups taken? If so, you can restore it to a different database and copy the table to the database which lost the data.
sridhar.tondapi
sridhar.tondapi
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 27
yes,my transaction got committed...i guess i need to perform recovery.
maechismo_8514
maechismo_8514
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1848 Visits: 2228
sridhar.tondapi (2/5/2009)
yes,my transaction got committed...i guess i need to perform recovery.


Then, Restore it to a different database name and copy the table to the target database.
homebrew01
homebrew01
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4877 Visits: 9108
And next time use a BEGIN TRAN so you can roll back if needed.

FYI, when making changes to data like this, I usually take a few precautions.

1. Back up the table first by creating a copy with a different name, such as:

select * into Table_20090205
from Table



2. Run a select to see how many records will get updated and Review the data to make sure it looks like the right records:

select * FROM Table_20090205
WHERE ColumnA Is Null




3. I would run the update in a transaction so you can undo it right then if you make a mistake:

begin tran

UPDATE T SET ColumnA = 'NewValue'
FROM Table T
WHERE ColumnA Is Null

rollback
commit



If you get the correct count, then: COMMIT
If you think it's wrong and need to undo, then : ROLLBACK

If you accidentally run the script all at once, having the rollback in there will undo it

4. When you're all finished, save the whole script somewhere in case you need to look back sometime.
Record the number of rcds changed in the script



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search