| Author |
 Topic  |
|
|
venki.dmm
Starting Member
10 Posts |
 Posted - 09/07/2009 : 09:05:22
|
i have deleted data from table
delete from carriers.
how to rollback .please help us. |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 09/07/2009 : 09:10:45
|
you can't rollback if you don't have a begin tran.
Restore from your backup
KH
Time is always against us
|
 |
|
|
venki.dmm
Starting Member
10 Posts |
Posted - 09/07/2009 : 09:20:57
|
| i dont have backup |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 09/07/2009 : 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
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 09/08/2009 : 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
United Kingdom
3560 Posts |
Posted - 09/08/2009 : 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
Slovenia
11741 Posts |
Posted - 09/08/2009 : 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! |
Edited by - spirit1 on 09/08/2009 06:07:35 |
|
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 09/08/2009 : 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
Slovenia
11741 Posts |
Posted - 09/08/2009 : 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
United Kingdom
527 Posts |
Posted - 09/08/2009 : 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 Posts |
Posted - 09/09/2009 : 00:35:08
|
rollback transcation t
select * from tablename
dis may helps u |
|
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 09/09/2009 : 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 |
|
|
| |
Topic |
|
rollback
