SQL Server Savepoints

There are some conditions when you need to rollback an implicit or explicit transaction to a given point inside the transaction. There are two ways to address such a need:

1. You can use the T-SQL to address such a problem:

SAVE TRAN savePointName
--Modify the data source
ROLLBACK TRAN savePointName

2. Use the SqlTransaction’s Save method within your code. Please note that this method is not part of the standard IDbTransaction interface and is located specifically on the SqlTransaction class:

//Mark a savepoint.
tran.Save("savePointName");
//Modify the data source
tran.Rollback("savePointName");

Please also note that when you rollback a transaction to a savepoint, resources are continued to be held until either the completion of the transaction or a rollback of the complete transaction. Therefore a commit or a complete rollback should always follow a rollback to savepoint.