The Database Maintenance Planner is used to schedule jobs to back up a database and its transaction log. The job to back up the database succeeds, but the job to back up the transaction log fails. When you view the job history, you see a message that resembles the following:
The job failed. The Job was invoked by Schedule 4 (Schedule 1). The last step to run was step 1 (Step 1).
To verify whether you are encountering this problem, you can examine the Database Maintenance Plan log file (if you chose to create one) which is stored in the Log folder for SQL Server (by default in C:\Program Files\Microsoft SQL Server\MSSQL\LOG for a default instance). If you see a message that resembles the following in the DB Maintenance Plan log file, the job failed because transaction log backups are disallowed on the database:
Backup can not be performed on database 'Northwind'. This sub task is ignored.
The transaction log backup job may have failed because the database is using the "Simple" recovery model in SQL Server 2000 or in SQL Server 2005.
If you are not relying on transaction log backups as part of your disaster recovery strategy, you could modify the maintenance plan to only perform full database backups.
If you are relying on transaction log backups, however, you will need to change the recovery model for that database to "Full" or "Bulk-Logged" in SQL Server 2000 or in SQL Server 2005.
In SQL Enterprise Manager, right-click the database and click
Properties. Click the
Options tab and select either
Bulk-Logged or
Full for the recovery model.
For a description of the three recovery models and their differences, see "Selecting a Recovery Model" in SQL Server Books Online.
In SQL Server 2000 or in SQL Server 2005, the "Simple" recovery model is equivalent to "truncate log on checkpoint" in earlier versions of SQL Server. If the transaction log is truncated every time a checkpoint is performed on the server, this prevents you from using the log for database recovery. You can only use full database backups to restore your data. Backups of the transaction log are disabled when the "Simple" recovery model is used.
In SQL Server 7.0, a job created by the Database Maintenance Plan Wizard to perform a transaction log backup would not fail even if the database was set to "truncate log on checkpoint". This was due to a bug in SQL Server 7.0; for additional information, see the following article in the Microsoft Knowledge Base:
242500Â
(http://kbalertz.com/Feedback.aspx?kbNumber=242500/EN-US/
)
BUG: Sqlmaint Does Not Report Error on BACKUP LOG When Truncate Log on Checkpoint is Set
WARNING: Even though you change the recovery model of the
msdb database to "Full", it will automatically be reset to "Simple" the next time that SQL Server Agent is restarted. This is by design to prevent the
msdb transaction from growing to an unmanageable size. You will need to perform only full database backups on the
msdb database.
The
master database should also be left in the "Simple" recovery model; for more information, see the following article:
285288Â
(http://kbalertz.com/Feedback.aspx?kbNumber=285288/EN-US/
)
INF: Transaction Log Backups of Master Database Are Not Allowed