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

General SQL Server Forums

New to SQL Server Programming

rollback

Author

Topic

venki.dmm
Starting Member

10 Posts

Posted - 2009-09-07 : 09:05:22

i have deleted data from table

delete from carriers.

how to rollback .please help us.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-07 : 09:10:45

you can't rollback if you don't have a begin tran.

Restore from your backup

KH
[spoiler]Time is always against us[/spoiler]

venki.dmm
Starting Member

10 Posts

Posted - 2009-09-07 : 09:20:57

i dont have backup

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-09-07 : 12:15:19

the only other way is to use a 3rd party log reader like Quest Log Reader for SQL Server or Lumigent Log Explorer

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-08 : 03:24:55

Which will only work if the DB is in full recovery and there has been at least one full backup somewhere in its history. If not, the log will auto-truncate on a regular basis.

--
Gail Shaw
SQL Server MVP

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-08 : 04:44:29

Learn about backups very quickly, if this data waqs important to you, you should have had a backup anyway.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-09-08 : 06:06:59

quote:
Originally posted by GilaMonster

Which will only work if the DB is in full recovery and there has been at least one full backup somewhere in its history. If not, the log will auto-truncate on a regular basis.

--
Gail Shaw
SQL Server MVP

if the db is in simple recovery and has never been backed up then the the log will never get truncated because it is still marked as needed for backup so it's still active.
or is that valid only for full recovery model?
the "no backup taken ever" thing in simple model is throwing me off.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-08 : 10:54:40

quote:
Originally posted by spirit1

if the db is in simple recovery and has never been backed up then the the log will never get truncated because it is still marked as needed for backup so it's still active.
or is that valid only for full recovery model?

Neither.

In simple recovery the log is always truncated on checkpoint, regardless of the existence (or lack thereof) of a backup.

When a database gets switched to (or created in) full recovery, the log is still automatically truncated when a checkpoint occurs until the first full database backup is taken. Once a full database backup has been taken, the log records are retained until a log backup occurs.

So a DB in full recovery that has never had a backup taken might as well be in simple recovery for the purpose of log retention.

Am I making sense?

--
Gail Shaw
SQL Server MVP

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-09-08 : 11:22:14

ah right. the existance of a backup makes no difference.
thanx.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-09-08 : 11:27:47

I always have the rule of thumb to do all transaction in a begin tran statement

eg.

BEGIN TRAN ONE

UPDATE table SET Col1 = 'TEST'

--COMMIT TRAN ONE

--ROLLBACK TRAN ONE

By commenting out the last to statements you can then choose which one is relevant. Just helps with a little added control and safety i think.

Cherry_2009
Starting Member

1 Post

Posted - 2009-09-09 : 00:35:08

rollback transcation t
select * from tablename
dis may helps u

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-09 : 03:19:07

quote:
Originally posted by Cherry_2009

rollback transcation t
select * from tablename
dis may helps u

Only if an explicit transaction had been started before the delete or is someone had set implicit transactions on as a connection setting. Unlike Oracle, SQL defaults to auto-commit mode.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources