Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

rollback delete command Expand / Collapse
Author
Message
Posted Friday, July 18, 2008 3:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 17, 2009 3:41 AM
Points: 33, Visits: 97
if by chance i run a delete command ,n sme rows from some table get deleted ,then what is the command to rollback the command or immediate command. so that i can retrieve the deleted rows
Post #536573
Posted Friday, July 18, 2008 3:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 7:42 AM
Points: 485, Visits: 11,017
Good Day,

You can use the "BEGIN TRAN" and "COMMIT TRAN" OR if you need to rollback then just use "ROLLBACK"

BEGIN TRANSACTION @TranName;
GO
USE AdventureWorks;
GO
DELETE FROM AdventureWorks.HumanResources.JobCandidate
WHERE JobCandidateID = 13;
GO

COMMIT TRANSACTION MyTransaction;
GO


The Rollback syntax

ROLLBACK { TRAN | TRANSACTION }
[ transaction_name | @tran_name_variable
| savepoint_name | @savepoint_variable ]
[ ; ]


Hope this helps you

regards
Stephen
Post #536576
Posted Friday, July 18, 2008 3:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
You can't recover deleted rows unless and untill you are using checkpoint. To rollback checkpoint is must.
Resoultion:
1. You can restore the backup with stop at otion and extract the data. (Best Option)
2. You can use 3rd party tool to read the log and find out more. Not sure howfar it will help?


---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #536578
Posted Friday, July 18, 2008 4:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 9:36 AM
Points: 42,765, Visits: 35,863
shiwani2002sg (7/18/2008)
if by chance i run a delete command ,n sme rows from some table get deleted ,then what is the command to rollback the command or immediate command. so that i can retrieve the deleted rows


Unless you started an explicit transaction before you ran the delete, there is no command that will retrieve the rows. You will have to locate a backup of the database.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #536618
Posted Friday, July 18, 2008 5:05 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 9:36 AM
Points: 42,765, Visits: 35,863
free_mascot (7/18/2008)
You can't recover deleted rows unless and untill you are using checkpoint.


Checkpoints happen automatically and just write dirty data pages to disk. It's BEGIN TRANSACTION that allows data modifications to be rolled back.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #536629
Posted Friday, July 18, 2008 5:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
GilaMonster (7/18/2008)
shiwani2002sg (7/18/2008)
if by chance i run a delete command ,n sme rows from some table get deleted ,then what is the command to rollback the command or immediate command. so that i can retrieve the deleted rows


Unless you started an explicit transaction before you ran the delete, there is no command that will retrieve the rows. You will have to locate a backup of the database.


If you need to revert to the backup, you may even want to explore some third party tools that allow you to do table and row level restore using the SQL Server backup. If the backup is older than you need, and you are desperate, you can also dig out a lot of information about the deleted rows from the transaction log.

Regards,
Andras





Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Post #536633
Posted Thursday, September 9, 2010 9:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:49 AM
Points: 163, Visits: 831
GilaMonster (7/18/2008)
free_mascot (7/18/2008)
You can't recover deleted rows unless and untill you are using checkpoint.


Checkpoints happen automatically and just write dirty data pages to disk. It's BEGIN TRANSACTION that allows data modifications to be rolled back.


Hello Gail, you get about, don't you?

Let me ask you this; if he issues a Delete statement at 5pm, demolishing 50 rows in .05 seconds, could he not take a TLog backup at 5.05pm, and then do a point-in-time restore to 4.59Pm?
Post #983204
Posted Thursday, September 9, 2010 9:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 9:36 AM
Points: 42,765, Visits: 35,863
Providing he had a backup, had the DB in full recovery and had an unbroken log chain, yes. However that's not a rollback (which is what the question asked)

p.s. 2 year old thread.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #983212
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse