An easy way to check SQL Server backups

Using powershell you can easily see the last time your DB was backed up.

For the past several weeks I've been doing nothing but complaining about vendors so I thought I'd take a break and write something about SQL Server for a change.  So to that end today I'm gonna talk about a couple different ways to get info on which DBs were backed up and when. 

This is one of the really big topics in any DB platform because none of the vendors give you a native way to automatically check on DBs that aren't backed up.  Microsoft is making it easier with management policies, but you still have to set it up and there's certainly not a way to keep history by default.  So that's the question isn't it?  How do I monitor my backups across all 500 of my servers, and even keep history of when they've failed?

This is where some of the vendors have stepped in with backup solutions that allow you to keep a centralized repository of all your backup metadata so you can easily report on it.  And that really worked well for a long time, but those solutions aren't cheap.  Often times they cost in the neighborhood of $1,000/server.  And these vendor products can be harder to manage because often times a server won't report their metadata to the repo and nothing is in place to tell you that the data isn't being populated.  You can see that report in the GUI typically, but I'm a firm believer that you should never have to go to a GUI to get info about your environment.  Everything should be scriptable and you should just get an email.  So you end up coding your own queries to alert on these missing backups and since the schemas of these products are usually... let's go ahead and say, less than optimal... then the queries tend to be long and unwieldy.

You can of course also go through the msdb backup history tables and write your series of joins it'll take to figure out when a DB was last backed up and then save that into a centralized table somewhere and that'll certainly work.  Again the problem is that the queries aren't necessarily easy to come by and you have to reproduce it for each server.  So it has to be housed somewhere and those queries may change across different versions of the DB.

This is where my current favorite solution comes into play.  I'm most in favor of powershell for this sort of thing these days because not only is the query very very simple, but it's the same across all versions of SQL Server.  And especially if you're looking for simple, fast alerts that tell you the last time your DB was backed up, you'll love this too.  Here's the powershell code to query that last backup info for all the DBs on a server.

Dir | FT Name, LastBackupDate, LastLogBackupDate, LastDifferentialBackupDate

That's it.  That's all the powershell code it takes to query for your last backup times.  Now, there are tons of other properties you can query at the DB level that are just as easy.  And now all you've gotta do is save it in your centralized table and write a simple query because all of your servers have their metadata in the same place.  And what's even better is you don't have to drop anything on individual servers because you'll run this from a single server and just cursor through all the boxes on a schedule and populate it. 

What I do, is I keep a table with all of my DB servers listed in it and that runs all of my powershell scripts.  I can do whatever I want to any group of boxes by just changing the query I use to pull the list of servers.  It's a wonderfully simple way to do it and it's also very dynamic.  If you bring a new server online, just add it to the server table and it'll be added to the collection next time it runs.  And if you've got a lot of servers and you don't want to take the time to cursor through all of them serially, then create 2 jobs and have each one take a different set of servers in the table.

It's fabulous and once you start writing these things you'll discover how easy they are to get up and manage.  Notice though that I didn't post tons of code here?  That's because this is meant for those who already know enough powershell to make this happen and will know what to do with it.  I'll write something else soon talking about all of this from a beginner perspective and possibly I'll even post a bunch of code that you can just drop into your environment.

Anyway, good luck and I hope you guys find this helpful.

Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.

Copyright © 2010 IDG Communications, Inc.