Backup Sanity Check

One of my pet peeves is when an application thinks its smarter than its DBA. Our helpdesk system was occasionally complaining that the database hadn’t been backed up in the past 24 hours. And our helpdesk manager was understandably concerned. That’s when I started doing some digging.

My backup script was occasionally skipping databases that had a SIMPLE recovery model. I’m still trying to figure that one out. More importantly, I set the recovery model to FULL, as it should be. * The latest upgrade of the vendor’s software set it to SIMPLE. But still, I was scratching my brain, trying to figure out what was wrong with my script.

In the meantime, I wanted to update my backup script to add a sanity check, backing up anything that hadn’t been backed up in the past 24 hours. I headed for the master.sys.databases table to see if I could find the column that indicated when it was last backed up. That’s the logical place for it, right? Wrong.  After doing some spelunking, I drew up this little gem:

SELECT      sd.name, max(bs.backup_finish_date)
FROM        master.sys.databases sd
LEFT JOIN   msdb.dbo.backupset bs
ON          bs.database_name = sd.name
where       ISNULL(type,'D') = 'D'
GROUP BY    sd.name
HAVING      max(backup_finish_date) IS NULL
OR          datediff(hour,max(backup_finish_date), getdate()) > 24
order by    sd.name

The backupset table gave me a great place to start.  But I found a little problem–it was listing a bunch of databases with really old backup dates. That’s when I realized those databases had been dropped long ago. By joining it to the master database, it only showed databases that were currently attached. And as an added bonus, a couple of NULL checks allowed me to see anything that had never been backed up.

I shouldn’t have to use this, but it is nice to know that I can use it as a sanity check.

* In my environment, the only databases that get SIMPLE recovery is static data. Everything else gets set to FULL.

Failing the Smell Test

In addition to being a DBA, I’m a bit of a travel geek. I have a great love of airplanes, aviation, and travel.

Today, I was looking at flights and saw something that made me think that it was a total violation of constraints.

Just looking at this made me cringe. US Airways flight 0729 departs London’s Heathrow, stops in Philadelphia, and then continues onto Boston. Flights with intermediate stops are common. Heck, Southwest flights are notorious for going coast-to-coast with multiple stops along the way. But in most cases, that flight is operated by same plane. In this case, the flight doesn’t stop in Philadelphia. If you look closely, you depart in Philadelphia and get on an entirely different aircraft. It’s not just a different physical plane–it’s a whole new aircraft type. You’re going from a widebody Airbus A330 onto a regional Embraer E90. This is what I call failing the smell test. The data construct just doesn’t make sense.

We’re facing a very similar situation with my company’s ERP system right now. We make technology devices and the software that runs them. Somewhere many years ago, when we started selling high availability pairs, instead of selling two devices with a discount, someone in marketing decided we would sell them as a pair. This has been a complete nightmare in terms of data. Our ERP system doesn’t allow two serial numbers for a part. The first serial number is recorded in the ERP system and then the second one lands in a spreadsheet somewhere. The folks in customer support go crazy when they can’t find the serial number of the second item in the pair.

I’ve seen a lot of conflicts between technology and marketing people in situations like this. Unfortunately, we lose all too frequently in these situations. In these cases, we need to really think through the process. Had someone looked at our problem from the customer support perspective, we might have been able to convince marketing that this was a bad idea. It should have failed the smell test at inception.

When things work

I’ve mentioned in the past that our facility had some serious drawbacks during power outages.

During our migration to our new facility and data center a few weeks ago, we lost power to the building. Again this morning, we lost building power. Let me tell you what happened.

• Our servers didn’t even blink.
• The UPS jumped in right away.
• The backup generator kicked in right away.
• Emergency lights in the data center stayed on.
• The air conditioning stayed on.
• We were able to get into the data center with our access badges.
• Every network switch on each floor of the building ran on UPS power.

This sounds like one of those “well, duh!” moments, but I have to tell you, it was revolutionary for us.  Every one of these things was problematic in our old building.

It’s wonderful when things work the way they’re supposed to.