is there way to rollback? - 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 2005 Forums

Transact-SQL (2005)

is there way to rollback?

Author

Topic

sql117
Starting Member

19 Posts

Posted - 2009-06-26 : 05:35:20

Hi, i had done some update operation on a table. Can any one please let me know the process to get back to preivous mode(i.e data before update).

I performed queries like this.

Update table set colname = 'xxx' where condition.

Select * from table(This is my last operation on table).

Is there any way to get back to preivous mode?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 05:42:54

Not without a prior unfinished/uncomitted transaction.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=128307

Your best bet would be a restore from a backup.

E 12°55'05.63"
N 56°04'39.26"

sql117
Starting Member

19 Posts

Posted - 2009-06-26 : 05:45:20

Is there any way to take specific table backup on regular basis(daily with using schedular) ? instead of taking whole DB backup?

quote:
Originally posted by Peso

Not without a prior unfinished transaction.
Your best bet would be a restore from a backup.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 05:52:28

You can always do a

IF OBJECT_ID('Table1_Backup') IS NOT NULL
DROP TABLE Table1_Backup

SELECT *
INTO Table1_Backup
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-06-26 : 06:04:49

I would suggest the same as peso always a good idea to take a copy of the table first if you doing some extensive updating/insert/deleting, however i generally use a begin tran at the beginning of my statments when doing something like that i.e.

Begin tran

Update table1 set col1 = getdate() where colid = 1234

select * from table1 where colid = 1234

--Commit tran

--Rollback tran

Then once you've verified the correct data/ row have been updated, then depending if its correct or not highlight the relevent statement line commit or rollback and execute

sql117
Starting Member

19 Posts

Posted - 2009-06-26 : 06:25:35

Thank you Peso. Can we make this script as schedular in sql server or any other way to run the same in windows task schedular like batch file?

quote:
Originally posted by Peso

You can always do a

IF OBJECT_ID('Table1_Backup') IS NOT NULL
DROP TABLE Table1_Backup

SELECT *
INTO Table1_Backup
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 06:33:32

Yes, of course.
Paste the code in a jobstep and have the job scheduled.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources