Ever notice a database backup being skipped...

Dec 8, 2011 at 4:03 AM

Just last week I met with a teammate from a previous job and he told me that our long standing maintenance job that looped through databases had routinely been skipping a database on one of the heavily used database systems.  There hadn't been a full backup in a year.  I thought by switching to sp_MSforeachdb that I was going to be better off.  Just read an article by Aaron Bertrand that says that my assumption is incorrect.  In fact, that undocumented sproc has the same problem.  Aaron has wrote a replacement script for the sproc - and he implemented the same solution as my old co-worker.  That being make your cursor STATIC READONLY.

To read Aaron's article, go here:  http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/

The code here currently still could suffer from this issue.  I should be doing an update to code with some minor updates and a correction to all of the sprocs that use sp_MSforeachdb (backup scripts only?).

Dec 13, 2011 at 2:27 AM

Addressed in changeset 8268.  http://centraldba.codeplex.com/SourceControl/changeset/changes/8268