SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 rollback
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

venki.dmm
Starting Member

10 Posts

Posted - 09/07/2009 :  09:05:22  Show Profile  Reply with Quote
i have deleted data from table

delete from carriers.

how to rollback .please help us.

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 09/07/2009 :  09:10:45  Show Profile  Reply with Quote
you can't rollback if you don't have a begin tran.

Restore from your backup


KH
Time is always against us

Go to Top of Page

venki.dmm
Starting Member

10 Posts

Posted - 09/07/2009 :  09:20:57  Show Profile  Reply with Quote
i dont have backup
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 09/07/2009 :  12:15:19  Show Profile  Visit spirit1's Homepage  Reply with Quote
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!
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 09/08/2009 :  03:24:55  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 09/08/2009 :  04:44:29  Show Profile  Reply with Quote
Learn about backups very quickly, if this data waqs important to you, you should have had a backup anyway.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 09/08/2009 :  06:06:59  Show Profile  Visit spirit1's Homepage  Reply with Quote
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!

Edited by - spirit1 on 09/08/2009 06:07:35
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 09/08/2009 :  10:54:40  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 09/08/2009 :  11:22:14  Show Profile  Visit spirit1's Homepage  Reply with Quote
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!
Go to Top of Page

NeilG
Aged Yak Warrior

United Kingdom
530 Posts

Posted - 09/08/2009 :  11:27:47  Show Profile  Reply with Quote
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.
Go to Top of Page

Cherry_2009
Starting Member

1 Posts

Posted - 09/09/2009 :  00:35:08  Show Profile  Reply with Quote

rollback transcation t
select * from tablename
dis may helps u
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 09/09/2009 :  03:19:07  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000