Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rollback Expand / Collapse
Author
Message
Posted Monday, January 14, 2008 5:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 8:47 PM
Points: 1, Visits: 100
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...
Post #442771
Posted Monday, January 14, 2008 5:59 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 4:39 PM
Points: 4,576, Visits: 8,342
IF @@ROWCOUNT > 10 -- assume that normal query to affect not more than 10 rows
ROLLBACK
ELSE
COMMIT
Post #442782
Posted Monday, January 14, 2008 6:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:24 PM
Points: 7,121, Visits: 15,024
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?
Post #442783
Posted Monday, January 14, 2008 7:54 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 8:44 AM
Points: 1,073, Visits: 6,334
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.

http://www.red-gate.com/products/SQL_Log_Rescue/index.htm

Post #442798
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse