IMPLICIT_TRANSACTIONS ON 的 SQL Server 2008 备份
这本身不是解决方案的问题，我试图理解 Management Studio 中的一个非常奇怪的行为。
我完全理解 BACKUP 和 RESTORE 数据库不能在事务下执行，以及 SET IMPLICIT_TRANSACTIONS ON 的含义是什么。
所以我进行了一个实验：
- 打开 Management Studio，转到 Options / Query Execution / SQL Server / ANSI 并勾选 SET IMPLICIT_TRANSACTIONS
- 重新打开 Management Studio，
- 使用默认数据库为 master 的用户登录
- 执行 BACKUP ，这工作正常，
- 将数据库更改为其他内容
执行了 BACKUP，但失败并显示一条消息
消息 3021，级别 16，状态 0，第 7 行 无法在事务内执行备份或恢复操作。 消息 3013，第 16 层，状态 1，第 7 行 BACKUP DATABASE 异常终止。
改回 master 并不能消除错误消息
我的问题是 - 为什么它允许在连接后立即执行 BACKUP 而在更改后失败数据库？
this isn't a question for a solution per se, I am trying to understand a very strange behaviour in Management Studio.
I totally understand that BACKUP and RESTORE database can't be executed under a transaction and what the implications of SET IMPLICIT_TRANSACTIONS ON are.
So I conducted an experiment:
- opened Management Studio, went to Options / Query Execution / SQL Server / ANSI and ticked SET IMPLICIT_TRANSACTIONS
- reopened Management Studio
- logged in with a user whose default database is master
- executed BACKUP and this worked fine
- changed the database to something else
executed BACKUP and it failed with a message
Msg 3021, Level 16, State 0, Line 7
Cannot perform a backup or restore operation within a transaction.
Msg 3013, Level 16, State 1, Line 7
BACKUP DATABASE is terminating abnormally.changing back to master doesn't get rid of the error message
My question is - why it allows to execute BACKUP right after connecting and fails after changing the database?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
备份不能位于事务中
将数据库从 master 更改为其他数据库时，您将发出 SET IMPLICIT_TRANSACTIONS 中描述的命令之一ON（使用SQL Profiler查看）并创建事务。
根据经验（无需实际检查！），它将是用于 Intellisense 或对象资源管理器的 SELECT 或获取其他一些元数据。我还猜测像 Intellisense 这样的东西不用于系统数据库（我关掉该死的东西并使用第 3 方工具），所以它最初不会发生
后续错误发生，因为你仍在事务中
BACKUP can not be in a transaction
When changing the database away from master, you are issuing one of the commands described in SET IMPLICIT_TRANSACTIONS ON (use SQL Profiler to see) and creating a transaction.
Based on experience (without actually checking!) it will be a SELECT for Intellisense or Object Explorer or to get some other MetaData. I'm also guessing that something like Intellisense isn't used for system databases (I switch the damn thing off and use 3rd party tools) so it doesn't happen initially
Subsequent errors happen because you're still in a transaction