When we roll back our transaction, it nullifies the effect of every command you’ve executed since you started the last transaction. But what happens if you want to roll back only part of an ongoing transaction? SQL Server handles this with a feature called savepoints.
Savepoints are markers that act like bookmarks. You mark a certain point in the flow of the transaction, and then you can roll back to that point. You set the savepoint using the Transaction.Save() method.
Here’s a conceptual look at how you use a savepoint:
// Start the transaction.
SqlTransaction trans = Connection.BeginTransaction();
// Mark a savepoint.
trans.Save("CompletedUpdate");
// If needed, roll back to the savepoint.
trans.Rollback("CompletedUpdate");
// Commit or roll back the transaction.
trans.Commit();