SQL Server Performance Forum – Threads Archive

SQL 2005 Database Maintenance Plans

Hi, I am runing SQL Server 2005 SP1 and am trying to create a database maintenance plan that will work for all my user databases. I have a mixture of user databases in the full or simple recovery model. I am using the Backup database task to only backup the log file for all user databases. However, the backup task also tries to backup the log on the user databases that are in the Simple recovery model and the task fails with errors like this: Back Up Database Task (TRGAZIDSQL02) Backup Database on Local server connection Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped. Databases: All user databases Type: Transaction Log Append existing Task start: 2007-05-07T13:00:01. Task end: 2007-05-07T13:00:01. Failed<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0) Database ‘ReportServer’ will not be backed up because it does not have its recovery model set to Full or BulkLogged. Is there a way to setup the maintenance plan for all user databases that won’t fail when attempting to do the log backups? Thanks for your help!

You have an option of specifying only particular databases and you can choose there. YOu have the following options in the wizard: 1. All d/b’s
2. All system d/b’s
3. All user d/b’s
4. These d/b’s Hit on These d/b’s and u can select particular ones. Didn’t you do that? Satya

There are many bugs fixed in sp2 related to maintenace plans… apply the SP2, if possible… Second thing is when you setup the tlog backup when using Backup Database Task, databases with simple recovery model and system datatabase should be excluded automatically in BACKUP DATABASE TASK window… if it is showing then it may be bug… apply the SP2 and test it… If you don’t want to apply the SP2 then check the individual database check box for each db for tlog backup.
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Database ‘ReportServer’ will not be backed up because it does not have its recovery model set to Full or BulkLogged.
ReportServer is a system type database for reporting services, you can exclude this from the Tlog backup schedule. All you need is to have complete database backup.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

I have applied SP2 and recreated the maintenance plan for the log backups. It is still failing when attempting to backup the log for the databases that are in Simple recovery mode.
The point here is that I want to be able to choose "All User Databases" so that as new databases are added, I do not need to change the Maintenance Plan by adding the new databases to the list based on their recovery model.
Is there a way to use the "All User Databases" selection for the log backups when my databases are in a mixture of recovery models? Thanks again for your help.

By default you cannot perform log backup for a database that is in SIMPLE Recovery model, you have to exclude that database from the tlog backup schedule. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

]]>