Can't select database for transaction log backup in maintenance plan

Question

I am running into a problem while trying to save a transaction log database backup maintenance task:

1. Add the 'Back Up Database Task' to the maintenance plan.
2. Edit the task.
3. Select backup type: 'Transaction Log'.
4. Select any database running with Full Recovery Model.
5. Close the task.
6. Re-open the task. Notice that the database I selected is not checked anymore.

I can select all databases, all user databases, specific databases...none of them ever save. Every time I re-open the task that field is switched back to < Select one or more > option.

I've connected as user sa and I've connected with Windows Authentication. Same result. Also, I've double-checked that the databases I'm trying to backup are in Full recovery model.

I'm running SQL Server 2005 SP2 on Windows 2003 R2 SP2.

EDIT: This happens even if the database had a full backup run against it.

Answer 1

See this Connect item, which is almost identical to the scenario you're trying -- they're close enough that I would say that it's highly likely to be the same issue. I would attempt to reproduce the exact same scenario presented in the bug report (I assume you will be able to).

While it's unclear in which release the fix for that bug landed, by the date of Microsoft's response, I believe this should be fixed in SP3.

If applying a service pack is not an option at this time, you'll want to go with a trusted 3rd-party solution, as mentioned in the other answer.

Answer 2

Take a full backup of the database that you are trying to do a T-log backup and then it will show up in maintenance plan. The reason for this is - your database (the one which you are trying to do a T-log backup) might be in pseudo-simple mode. Best explanation is given here by Paul Randal.

Also, just another thought, why dont you use SQL Server Maintenance Solution by Ola Hallengren. It is one of the best and recommended by SQL Community.