SQL Server Rollback Methods

In SQL Server, you can use the following methods to implement database rollback:

1. Begin a transaction using the BEGIN TRANSACTION statement and rollback a transaction using the ROLLBACK TRANSACTION statement. For example:

BEGIN TRANSACTION;

-- 执行一系列的操作

IF @@ERROR <> 0
BEGIN
    ROLLBACK TRANSACTION;
    PRINT '事务回滚成功';
END
ELSE
BEGIN
    COMMIT TRANSACTION;
    PRINT '事务提交成功';
END

1. Create a savepoint using SAVEPOINT, and then rollback to this savepoint using the ROLLBACK TO statement when needed. For example:

BEGIN TRANSACTION;

-- 执行一系列的操作

SAVE TRANSACTION SavePoint1;

-- 执行另一系列的操作

IF @@ERROR <> 0
BEGIN
    ROLLBACK TRANSACTION SavePoint1;
    PRINT '回滚到保存点成功';
END
ELSE
BEGIN
    COMMIT TRANSACTION;
    PRINT '事务提交成功';
END

1. Utilize transaction log backups and restoration to revert a database to a previous state. Initially, backup the database’s transaction log using the BACKUP LOG statement, and then restore the database to the backed-up transaction log using the RESTORE LOG statement.

BACKUP LOG [数据库名] TO DISK = '路径\备份文件名.trn';

-- 回滚到备份的事务日志
RESTORE LOG [数据库名] FROM DISK = '路径\备份文件名.trn' WITH STOPBEFOREMARK = '标记名称';

These methods can assist in implementing database rollback operations. Before using them, make sure to carefully understand the usage scenarios and precautions of each method.