SQL Server 2008 BACKUP with IMPLICIT_TRANSACTIONS ON

Question

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?

Answer 1

BACKUP can not be in a transaction

The BACKUP statement is not allowed in an explicit or implicit 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