Savepoints are SQL Server's save game [i.e. transaction] checkpoints (Think "An possible answer to the need for nested transactions")

BlackWasp - SQL Server Transaction Savepoints

Sometimes Transact-SQL (T-SQL) scripts require the ability to roll back some parts of a transaction whilst allowing other elements of a process to be committed to the database normally. This can be achieved with the use of transaction savepoints.

Nested Transactions

SQL Server includes the concept of nested transactions, where one transaction is started within the scope of another. You might conclude that this would allow you to create a nested transaction, perform some actions and then roll back the changes of the nested transaction, allowing the outer transaction to continue as normal and be rolled back or committed later. Unfortunately, nested transactions do not behave in this manner. You cannot use them to reverse parts of a transaction, which can lead to errors when transactions are nested inadvertently.

...

Transaction Savepoints

Within a transaction you can create one or more transaction savepoints. These mark a point in a transaction to which you may wish to rollback. When you rollback to a savepoint, all of the database updates performed after that savepoint are reversed. Updates that happened after the transaction started but before the savepoint was declared are not affected.

You can create multiple savepoints within a single transaction and roll them back individually. However, it's important to note that rolling back to a savepoint also removes any savepoints that were created later. For example, if you created savepoints named "s1", "s2" and "s3" in that order, rolling back savepoint "s2" would remove savepoint "s3". Savepoint "s1" would still be active.

You never commit a savepoint manually. The updates following a savepoint that has not been rolled back will be stored or discarded according to whether the containing transaction is committed or rolled back.

To create a savepoint, use the SAVE TRAN or SAVE TRANSACTION command. You must provide a name for the savepoint. This should be a string of up to 32 characters. If the name is longer than 32 characters the additional text is ignored. You can provide the name within a variable if desired.

...

Considerations

Savepoints can be very useful but there are considerations for their use. Firstly, savepoints have the limitation that they cannot be used in distributed transactions. Secondly, you should note that locks created during a transaction are retained when rolling back to a savepoint. They are released only when the entire transaction is committed or rolled back.

..."

I swear I feel dumber by the day. While I've know about SQL Server's next transaction behavior for forever, I hadn't known about savepoints. And gee, this feature has only been around since SQL Server 2005... sigh.

Every DBA/Dev knows about SQL Server's nested transaction behavior, right? Where while you can nest BEGIN TRANS, when you ROLLBACK, you roll them all back. But what if you really more than this? What if you need to really rollback only part?

SAVE TRANSACTION (Transact-SQL)

"A user can set a savepoint, or marker, within a transaction. The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled. If a transaction is rolled back to a savepoint, it must proceed to completion with more Transact-SQL statements if needed and a COMMIT TRANSACTION statement, or it must be canceled altogether by rolling the transaction back to its beginning. To cancel an entire transaction, use the form ROLLBACK TRANSACTION transaction_name. All the statements or procedures of the transaction are undone.

..."

If you're never wished for a partial transaction rollback feature, you should check this out...