Update statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-18 : 08:48:11

Nadeem writes "Hi

I want to update set of transactions in the master table, after successfully updating the records if suppose I want to rollback those, how do I do that. can anyone help regarding this

Best Regards
Nadeem"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-18 : 09:07:15

begin tran
update ....
rollback tran

or in v2005 you can make use of a snapshot.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

asmanadeem
Starting Member

5 Posts

Posted - 2007-01-19 : 01:24:42

Hi nr

Thanks for your response. Suppose if I want to update at different stages, rollback will work only for the recent records and which has begin tran. Can you please suggest any other alternative wherein I can restore the data if I had updated wrongly

MohammedU
Posting Yak Master

145 Posts

Posted - 2007-01-19 : 01:45:18

Change your database recovery model to FULL.
1. Run a full backup ...
2. Schedule a tlog backups...
run your updates...
if you anything went wrong you use full+tlog restore to get it backup what you lost...

http://www.sqlservercentral.com/columnists/gFritchey/recoverytoapointintime.asp

MohammedU

asmanadeem
Starting Member

5 Posts

Posted - 2007-01-19 : 04:27:26

Dear Mohammed

Assalamu alaikum

Thanks for being so nice. I know I can take full backup and tlog backup but that's time consuming if the size of the db is huge, rather I can take that particular backup before any update. One more problem by doing that is manual intervention required, can't we do an automatation wherein if we want to restore at any given point of time.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-19 : 06:27:07

Have a look at snashots.
Oops - this is a v2000 forum.

I think you maybe want to keep the updates separate and only apply them later rather than try to roll them back.
It sounds like you need something implemented on top of the database rather than utilising database facilities.

Maybe more info about what you are trying to achieve would be good.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources