4

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.

4

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.

0

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.

  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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