16 State 2
Dump transaction is not allowed because a non-logged operation was performed on the database. Dump your database or use dump transaction with truncate_only until you can dump your database.
This error occurs when you have performed a minimally logged operation such as bcp in or select into on the database, followed by a dump transaction to a dump device. The dump transaction is not allowed because your database contains data that cannot be recovered from the log. The log could not be used to recover the database properly. For instance, if a user does a select into followed by an update, data for the select into is not recorded in the log, so the update cannot be recovered from the log.
A minimally logged operation is one in which changes
to the data itself are not logged to the transaction log. All changes
to the allocation structures resulting from the operation are logged
to the transaction log.
Note that simply enabling the select into / bulkcopy option does not, in itself, cause this condition. The option must be set and a minimally logged operation executed before this error message is reported during a dump transaction to a dump device.
Dumping the database creates a new save point on which subsequent transaction logs are based. Therefore, dumping the database ensures that the minimally logged changes are recoverable and dumping the transaction log to a device is once again allowed.
Perform a dump database. Disabling the select into/bulkcopy option using sp_dboption will not allow you to dump the log. You must dump the database to clear the 4207 error condition. You may want to disable the select into/bulkcopy option to prevent any further nonlogged operations in your database.
You may need to use dump transaction with truncate_only until you can perform a full database dump. If you must do this, you will lose the ability to recover up-to-the-minute changes in data in case of a media failure.
All versions
