Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

enter image description here

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

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
This looks like the answer. If installing the latest service pack fixes it, I'll credit you with the answer. –  zippy Mar 26 '13 at 2:30
    
@zippy: Thanks, let us know how it goes. –  Jon Seigel Mar 26 '13 at 2:44
    
I won't get a chance to install any service packs on this server after all. However, your answer looks correct so I'll credit you the answer. –  zippy Jun 25 '13 at 21:16

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.

share|improve this answer
    
I did run a full backup of the database first. The T-log backup task still deselects the database. I'll take a look at the Ola scripts. –  zippy Mar 25 '13 at 21:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.