backup cannot be performed on this database

Question

backup cannot be performed on this database

scott clemons

Mr or Mrs. 500

Points: 550
More actions
November 3, 2006 at 10:42 am

#115253

I setup a maintenance plan to backup two databases in SQL Server 2000. A Complete Backup and Transaction Log Backup are performed with integrity check upon completion. Nothing is selected in the Optimization or Integrity tabs.
The complete backup is a success. The transaction log backup is a failure on both databases. The errors follow:
SQL Server Agent > Jobs > View Job history = "The job failed. The last step to run was Step 1." (note: only one step in the job)
Maintenance > Database Maintenance Plan History > Message = "Backup cannot be performed on this database. The sub task is ignored"
Neither message contains an Error ID or other useful troubleshooting information.
What is going on?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

The databases you are trying to backup are probably set to SIMPLE recovery mode. In this mode, transaction log backups can not be done, as the transaction log is truncated as each transaction is checkpointed by SQL Server.

Take a few minutes to read BOL, as I feel it does a good job of explaining the different recovery modes.

debrucer1 SSCertifiable Points: 5366 More actions · Answer 2

It always surprizes me that even with the message "ignored" that the step fails... and what I usually see is that the files are not purged. We split our maintenance plans into two types: one with only simple recovery mode DBs, and a second with bulk/full recovery modes. This elimiates that "ignored" warning and the step that does the purge(s) does not fail.

Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982

scott clemons Mr or Mrs. 500 Points: 550 More actions · Answer 3

Can I change the model to FULL? If yes, are there any side effects?

Database was initially created in MSDE, so the default model is SIMPLE. Evidentally, the setting stayed the same, when I moved it to the production SQL Server.

BOL were very informative. Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Yes, you can using EM. Just be sure you have the transaction log backups running, as the Full and Differential backups do not truncate the transaction log.