Roll back transactions in the transaction log file

Question: How to Use Transact-SQL to roll back a transaction in the transaction log file (for example, Id 0000: 0010a183 )?

Answer:: To prevent data errors, SQL server does not support rollback of individual transactions. For example, assume that two transactions t1 and t2 use the cash balance domain. $500 is added for T1, and T2 performs an operation using the updated value. If T1 is rolled back, T2. However, you can use timestamps or transaction log tags to restore logs to predefined tags or time points. The following two examples illustrate how to use SQL Server 2000 syntax.

Example 1: Use a timestamp to restore a log at a time point

Use the previous full backup to restore the database and make it ready for log recovery.

Restore database pubs from disk = n'c: \ Backups \ fullbackup. Bak 'with norecovery

Now you can roll logs to the appropriate time point and make the database available. Note that stopat cannot be executed when the database is executing large-capacity logs.

Restore log pubs from disk = n'c: \ Backups \ logbackup. Bak' with recovery, stopat = '2017/24/60 17:35:00'

Example 2: statements that use database tags to restore logs to a predefined time point

Add a tag to the transaction log. Note that at least one update must be submitted for the marked transaction to mark the log.

Begin Tran mymark with Mark
Update pubs. DBO. lastlogmark set marktime = getdate ()
Commit Tran mymark

Back up transaction logs according to your common methods.

Backup log pubs to disk = 'C: \ Backups \ fullbackup. Bak' with init

Now you can restore the database to a log mark point. First, recover the database and make it ready to accept log recovery.

Restore database pubs from disk = n'c: \ Backups \ fullbackup. Bak 'with norecovery

Now, the log is restored to the time point that contains the mark and made available for use. Note that stopat cannot be executed when the database is executing large-capacity logs.

Restore log pubs from disk = n'c: \ Backups \ logbackup. Bak' with recovery,
Stopat = '2014/1/0 17:35:00'