How do we rollback the table data in SQL Server

Question Posted / bsatish

3 Answers
15715 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

How do we rollback the table data in SQL Server..

begin transaction
save transcation t
delete from tablename where id=2
select * from tablename
------------------------------------------------------
the value id=2 will be deleted in the tablename
-------------------------------------------------------
rollback transcation t
select * from tablename
---------------------------------------------------------
if u give rollback the deleted values will be seen again

Is This Answer Correct ?

22 Yes

3 No

How do we rollback the table data in SQL Server..

Answer / umesh roy

Above code spelling are wrong on save Transaction
correct code are below which is executeable

begin transaction
save transaction t
delete from tablename where id=2
select * from tablename
------------------------------------------------------
the value id=2 will be deleted in the tablename
-------------------------------------------------------
rollback transaction t
select * from tablename

Is This Answer Correct ?

14 Yes

1 No

How do we rollback the table data in SQL Server..

Answer / arunyadav007

Actually rollbacks are automatic. MS SQL Server is fully
ACID compliant.

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

Arguments:
transaction_name
Is the name assigned to the transaction on BEGIN
TRANSACTION. transaction_name must conform to the rules for
identifiers, but only the first 32 characters of the
transaction name are used. When nesting transactions,
transaction_name must be the name from the outermost BEGIN
TRANSACTION statement.

@ tran_name_variable
Is the name of a user-defined variable containing a valid
transaction name. The variable must be declared with a
char, varchar, nchar, or nvarchar data type.

savepoint_name
Is savepoint_name from a SAVE TRANSACTION statement.
savepoint_name must conform to the rules for identifiers.
Use savepoint_name when a conditional rollback should
affect only part of the transaction.

@ savepoint_variable
Is name of a user-defined variable containing a valid
savepoint name. The variable must be declared with a char,
varchar, nchar, or nvarchar data type.

Remarks
ROLLBACK TRANSACTION erases all data modifications made
from the start of the transaction or to a savepoint. It
also frees resources held by the transaction.

ROLLBACK TRANSACTION without a savepoint_name or
transaction_name rolls back to the beginning of the
transaction. When nesting transactions, this same statement
rolls back all inner transactions to the outermost BEGIN
TRANSACTION statement. In both cases, ROLLBACK TRANSACTION
decrements the @@TRANCOUNT system function to 0. ROLLBACK
TRANSACTION savepoint_name does not decrement @@TRANCOUNT.

A ROLLBACK TRANSACTION statement specifying a
savepoint_name releases any locks acquired beyond the
savepoint, with the exception of escalations and
conversions. These locks are not released, and they are not
converted back to their previous lock mode.

ROLLBACK TRANSACTION cannot reference a savepoint_name in
distributed transactions started either explicitly with
BEGIN DISTRIBUTED TRANSACTION or escalated from a local
transaction.

A transaction cannot be rolled back after a COMMIT
TRANSACTION statement is executed.

Within a transaction, duplicate savepoint names are
allowed, but a ROLLBACK TRANSACTION using the duplicate
savepoint name rolls back only to the most recent SAVE
TRANSACTION using that savepoint name.

In stored procedures, ROLLBACK TRANSACTION statements
without a savepoint_name or transaction_name roll back all
statements to the outermost BEGIN TRANSACTION. A ROLLBACK
TRANSACTION statement in a stored procedure that causes
@@TRANCOUNT to have a different value when the stored
procedure completes than the @@TRANCOUNT value when the
stored procedure was called produces an informational
message. This message does not affect subsequent processing.

Is This Answer Correct ?

2 Yes

6 No

Post New Answer