March 14, 2011 at 12:27 am
Hi,
I am using sql server 2005, in that I am directly running my sql queries. Now I want to know that how can I rollback if I did anything wrong.
Let us consider,
1) Table content
insert into emp_table(101,'abc',30000)
insert into emp_table(102,'uvw',25000)
insert into emp_table(103,'xyz',40000)
2) updating:
udpate emp_table set salary=50000 (here I failed to give condition, obviously all record will be updated)
Here I want to rollback my step 2, and I need my original value.
How can I achieve this?
Thanks in advance
March 14, 2011 at 12:46 am
udpate emp_table set salary=50000 (here I failed to give condition, obviously all record will be updated)
Here I want to rollback my step 2, and I need my original value.
without a condition, how do you plan to do this?
you could:
--YOUR INSERT STATEMENT HERE
WHILE Emp_condition = true/false
begin tran salary_upd
update emp_table
set salary = 50000
end tran salary_upd
ELSE
end
But an IF statement will work just as well, but you'll need at least one condition..
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 14, 2011 at 12:50 am
visweswaran28 (3/14/2011)
Hi,I am using sql server 2005, in that I am directly running my sql queries. Now I want to know that how can I rollback if I did anything wrong.
Let us consider,
1) Table content
insert into emp_table(101,'abc',30000)
insert into emp_table(102,'uvw',25000)
insert into emp_table(103,'xyz',40000)
2) updating:
udpate emp_table set salary=50000 (here I failed to give condition, obviously all record will be updated)
Here I want to rollback my step 2, and I need my original value.
How can I achieve this?
Thanks in advance
Basing this solely on what you have provided, the simple answer is it depends.
If, looking at just the code you posted, are using implicit transactions, then inserts and updates are committed when they complete.
If you want to use explicit transactions, then you'd start each transaction with a BEGIN TRANSACTION statement and end them with a COMMIT or ROLLBACK. Which you would use would be dependent on if the INSERT, UPDATE, or DELETE statement(s) had completed successfully. You may determine this using code programatically, or by running some code manually before issuing the commit or rollback.
If you determine after the commit that the insert, update, or delete was bad; then I hope you aren't running using the SIMPLE recovery model on your database. If you are, your only choice is to restore for the latest backups prior to your insert, update, delete and go from there. If you are running using the BULK LOGGED recover model, hopefully you haven't run any minimally logged transactions since your last transaction log backup (or if you have, you ran a transaction log backup immediately after and before doing these updates) as you will need to take a transaction log backup, restore from the most current backups to a point in time just before the erronous updates. If you are using the FULL recovery model, then the same as the BULK LOGGED, you just don't have to worry about minimally logged transactions preventing you from possibly doing a point in time recovery.
Of course, the BULK LOGGED and FULL recovery models and point in time recovery are dependent on your having run schudled full and transaction log backups, with possible differential log backups in there as well. If you haven't run a full backup since creating the database, then it will still operate as if using the SIMPLE recovery model until the first full backup is taken.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply