Rolling Back a Transaction in the Transaction Log File

Microsoft’s SQL Server development team explains why you can’t use T-SQL to roll back a transaction in the transaction log and provides code for a way to accomplish the task.

ITPro Today logo in a gray background | ITPro Today


How can I use T-SQL to roll back the one transaction (for example, ID 0000:0010a183) that I found in the transaction log file?

To prevent data corruption, SQL Server doesn't support the rollback of individual transactions. For example, let's say that two transactions, T1 and T2, use a cash balance field. T1 adds USD 500, and T2 makes a change by using that new value. If you then roll back T1, T2 might be wrong. However, you can restore a log to a predefined mark or a point in time by using timestamps, as Listing 1 shows, or transaction log marks, as Listing 2, page 50, shows.

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like