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.

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.

share|improve this question
    
Not an answer, but a suggestion would be to run a Profiler trace to capture any errors. –  Henry Lee Feb 18 '12 at 23:24
    
Actually, you can look in your maintenance logs for errors. –  RBarryYoung Jun 19 '12 at 13:20
add comment

4 Answers 4

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.

share|improve this answer
    
Yup, it's set to 'Full'. –  glasnt Jan 19 '12 at 23:25
add comment

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.

share|improve this answer
    
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 '12 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 '12 at 12:34
add comment

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.)

share|improve this answer
add comment

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:

http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx

And have written a replacement here:

http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/

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.

share|improve this answer
add comment

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.