如何在SQL Server 2008中使用触发器备份？
我有一个表（循环），并且创建了一个触发器
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
，但是当我在循环表中插入、更新或删除时，会导致错误。
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.
我可以做什么来修复这个错误？
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.
What can I do to fix this error?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
MSDN 上的 备份数据库 说
触发器总是在事务中
如果您仔细研究，备份是事务上一致的数据库快照：它在自己的事务中是没有意义的。
尤其重要的是，在 RESTORE 上，前滚/回滚机制必须回滚包含备份的事务...这首先就违背了备份的目的...
BACKUP DATABASE on MSDN says
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.
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...
您可以开始一项作业
sp_start_job。在作业中，您执行一个存储过程，在其中执行备份语句。我已经使用服务器触发器完成了此操作，该触发器在创建新数据库时触发。You could start a job
sp_start_job. Within the job you execute a stored procedure where you execute the backup statement. I've done this already with a server trigger which is fired when a new database is created.