May 17, 2011 at 12:42 am
Hello everyone.
I thought this is the right place to shoot my query. I am writing a script which must be restartable (Can be restarted if gets failed at any point. Without any data loss or inconsistencies).
For the same I just wanna know how can I rollback any delete statement written in my query. And also which sp is internally fired by SQL Server when rollback or Commit takes place?
below is my sample query that I wanted to make restartable:
SELECT * into table1 (col, col2...)
FROM table2
DELETE FROM table2 --Need to rollback table2 data if script gets failed after this step
SELECT * INTO table2 FROM table3
__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!
May 17, 2011 at 12:56 am
Use just simple transaction
begin try
begin tran
SELECT * into table1 (col, col2...)
FROM table2
DELETE FROM table2 --Need to rollback table2 data if script gets failed after this step
commit tran
end try
begin catch
rollback tran
end catch
May 17, 2011 at 1:22 am
You can also add one more begin tran - commit tran - rollback tran after
SELECT * INTO table2 FROM table3
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 17, 2011 at 1:23 am
srikant maurya (5/17/2011)
Use just simple transaction
You're right but that's not possible in my case. The scenario is, I had created Autosys jobs which calls and execute DTS package(s) which indeed contains multiple Execute SQL task. These Execute SQL Task contains my SQL Code.
So If my job gets failed at any point of time, I want to restart the job and reverse the earlier change made. For this purpose I need to ensure that data written during failure job turn should be reversed.
Also I can't use transaction option feature provided with DTS as my data side is huge (upto 2 GB's).
__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!
May 17, 2011 at 5:12 am
Without a transaction, a rollback of a delete statement requires you to have already stored the data elsewhere. If you really need complete recovery and you're working within DTS, why not export the data from the table being deleted out to a file which you can clean up at the end of the process if you don't need it or pull back in exactly what you need in the event of the, extremely awkward, rollback scenario you describe.
There is no perfect method for performing a rollback outside of using transactions. That's explicitly what transactions are for.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 17, 2011 at 6:49 am
Grant Fritchey (5/17/2011)
Without a transaction, a rollback of a delete statement requires you to have already stored the data elsewhere. If you really need complete recovery and you're working within DTS, why not export the data from the table being deleted out to a file which you can clean up at the end of the process if you don't need it or pull back in exactly what you need in the event of the, extremely awkward, rollback scenario you describe.There is no perfect method for performing a rollback outside of using transactions. That's explicitly what transactions are for.
Might be it's a extremely awkward rollback scenario. But i got this awkward scenario from the business for which I am working for and I was wondering if I can say the same to them. Anyway, I will try to work on you file logic.
Was just thinking if there is some system defined sp's in sql server which controls rollback.
Thanks for the help.
__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!
May 17, 2011 at 7:28 am
Anjan Wahwar (5/17/2011)
Grant Fritchey (5/17/2011)
Without a transaction, a rollback of a delete statement requires you to have already stored the data elsewhere. If you really need complete recovery and you're working within DTS, why not export the data from the table being deleted out to a file which you can clean up at the end of the process if you don't need it or pull back in exactly what you need in the event of the, extremely awkward, rollback scenario you describe.There is no perfect method for performing a rollback outside of using transactions. That's explicitly what transactions are for.
Might be it's a extremely awkward rollback scenario. But i got this awkward scenario from the business for which I am working for and I was wondering if I can say the same to them. Anyway, I will try to work on you file logic.
Was just thinking if there is some system defined sp's in sql server which controls rollback.
Thanks for the help.
There are system defined sp's, it's called BEGIN TRAN and ROLLBACK TRAN. What you want is something else entirely.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply