1

Using SQL Server 2008 R2, I have a maintenance plan that has a basic Backup All Databases (ignore offline) task, and clean up after 7 days.

However, I note that sometimes I lack backups for (seemingly) random databases.

Even though the script says all databases, bar offline, is there some parameter set where databases won't be backed up? E.g. No changes to database since last backup, or some other conditions?

The particular database in question at this time has been updated in the last 24 hours, but hasn't been backed up in the past week.

edit: the recovery model is set to Simple, which is the same for most other databases on the server, so it's probably not that. I've tried to look for other documentation, but I can't seem to find any technical details, only high level business/sales white papers.

Improve this question
2
  • Not an answer, but a suggestion would be to run a Profiler trace to capture any errors.
    – Henry Lee
    Feb 18, 2012 at 23:24
  • Actually, you can look in your maintenance logs for errors.
    – RBarryYoung
    Jun 19, 2012 at 13:20

4 Answers 4

Reset to default
1

In your Backup task, is your Backup Type set to Full? If it is set to Differential then a backup may not be taken of every database.

Improve this answer
1
  • Yup, it's set to 'Full'.
    – glasnt
    Jan 19, 2012 at 23:25
1

Check logs first.

It looks like you don't have access to the DB at that time. I had similar situation: backup tried to start when Integrity check / reorganize index / DB shrink / whatever else still in progress. Test start/stop time for each job.

Improve this answer
2
  • The maintenance plan logs show only green records, with the task names, and no details. Job History just shows "The job succeeded". Interestingly, the DB in question backed up last night, but I don't recall changing anything other than looking up the plan details. (confused face)
    – glasnt
    Jan 19, 2012 at 23:27
  • Take a look into logs (including windows logs) for_a_specific_day and time you missed the files. If there is no error on "missed" day the next question would be: take a look into job script and find a place job should put your backup. I mean, maintenance might put *.BAK into DIR1 and then something other move it to DIR2 where you are used to see them.
    – Putnik
    Jan 23, 2012 at 12:34
1

There was a bug with maintenance plan backups (all types) that skipped databases with AutoClose enabled when the "ignore databases where the state is not online" option is set.

I registered a Connect item for the issue here, and it has been fixed in the latest CUs.

Having said that, if this is the problem, I would recommend solving it by turning off AutoClose on those databases. (Also, consider using Policy-Based Management to detect and resolve these deviations.)

Improve this answer
1

sp_msforeachdb is used behind the scenes for these databases. The problem is that this system procedure has been confirmed to skip databases under many scenarios. I have blogged about this here:

And have written a replacement here:

You could easily implement your own copy of this stored procedure, adding a parameter that checks only for databases that are online. Then create backup job, using this version of the stored procedure, that didn't explicitly use a maintenance plan.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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