如何在SQL Server 2008中使用带触发器的备份？ [英] How to use backup with triggers in SQL Server 2008?

问题描述

我有一个表（周期），我创建了一个触发器

I have a table (Cycle) and I created a trigger

alter trigger AnyName on Cycles
for insert,update,delete
AS
BACKUP DATABASE medrepcrm TO  DISK = N'C:\medrepcrm.bak' WITH NOFORMAT, INIT,  NAME = N'pcrm-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

但是当我在循环中插入，更新或删除时导致错误的表。

But when I insert, update, or delete in the Cycle table that leads to an error.

Msg 3021, Level 16, State 0, Procedure AnyName, Line 8
Cannot perform a backup or restore operation within a transaction.
Msg 3013, Level 16, State 1, Procedure AnyName, Line 8
BACKUP DATABASE is terminating abnormally.
The statement has been terminated.

我该如何解决此错误？

推荐答案

备份数据库在MSDN上说

在显式或隐式事务中不允许使用BACKUP语句。

The BACKUP statement is not allowed in an explicit or implicit transaction.

在交易中始终是触发器

A trigger is always in a transactions

如果您进行操作，则备份是数据库的事务一致性快照：将它包含在自己的事务中是没有意义的。

If you work it through, a backup which is transactionally consistent snapshot of a database: it doesn't make sense for it to be in it's own transaction.

同样重要的是，在RESTORE上，前滚/后滚机制将必须回滚包含备份的事务...这首先破坏了备份的目的...

Not least, on RESTORE the roll forward/roll back mechanism would have to rollback the transaction containing the backup... which defeats the purpose of the backup in the first place...

这篇关于如何在SQL Server 2008中使用带触发器的备份？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！