Error 4207

Severity Level 16

Message Text

BACKUP LOG is not allowed while the select into/bulk copy option is enabled. Use BACKUP DATABASE or disable the option using sp_dboption.

Explanation

This error occurs when you perform a nonlogged operation on a database, such as bcp or SELECT INTO, followed by a BACKUP LOG to a dump device. Because there is data in your database that has not been logged and the log therefore cannot be used to properly recover the database, the BACKUP LOG operation is not allowed. For example, if a user executes a SELECT INTO followed by an UPDATE statement, the SELECT INTO operation is not logged, and the UPDATE results cannot be recovered from the log.

Note that simply enabling the select into/bulkcopy option does not, in itself, cause this condition. The option must be set and a nonlogged operation executed before this error message is reported.

Backing up the database creates a new savepoint on which subsequent transaction logs are based. Therefore, backing up the database ensures that the nonlogged changes are recoverable and backing up the transaction log to a device is once again allowed.

Action

You must perform a BACKUP DATABASE. Disabling the select into/bulkcopy option using sp_dboption will not allow you to back up the log (even though the message says this is an option). To clear the 4207 error condition, you must back up the database.

You might need to use BACKUP LOG WITH TRUNCATE_ONLY until you can perform a full database backup. If you must do this, you lose the ability to recover up-to-the-minute data changes in case of a media failure.

See Also