January 14, 2008 at 5:06 pm
Hi there.
just yesterday i was working on my development sql server 2000. I was running queries from query editor. There was an update statement and i forgot the where clause. It ran but updated the whole table instead of just one row. I know that Sql has auto commit. I had to restore to undo those changes. Upon reading I found out that using transaction statements its possible to rollback. Can anyone help me with...creating a template of
Begin tran....
where i could insert my ad-hoc queries and roll back if i need to...else commit
Thanks in advance...
January 14, 2008 at 5:59 pm
IF @@ROWCOUNT > 10 -- assume that normal query to affect not more than 10 rows
ROLLBACK
ELSE
COMMIT
_____________
Code for TallyGenerator
January 14, 2008 at 6:12 pm
In short - at any time between the BEGIN TRAN and the COMMIT.
So -
BEGIN TRAN
(do whatever you need to do)
(test that it did what you want)
COMMIT TRAN
or
ROLLBACK
Keep in mind that you will likely be keeping some things locked/unavailable while you're in the transaction, so don't take overly long so as to not disrupt the regular flow of data.
You should also do some reading on "Isolation levels" so as to determine what stays locked or should stay locked during your transection.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 14, 2008 at 7:54 pm
A log recovery tool such as SQL Log Rescue might be something you'd find useful in situations like this. It's not a replacement for managing transactions but it can be very handy for recovering from accidental modifications.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply