A few weeks back I was asked how long a backup took for a specific database. I knew the info was in the msdb somewhere so I pieced together a bit of code and then thought to myself “hey, this might be a good thing to share with others at MSSQLTips”. Well, OK, I actually was thinking about how no one minds when dogs lick themselves in public but if you lick a dog in public it suddenly becomes offensive. Weird, huh?

Back to the tip. See, most people do their database backups as an all-in-one job, and lack the details surrounding individual databases. I know some third party vendors can provide some basic reporting but my experience with the canned reports from some vendors is, well, poor. For example, I had one product that would generate a report and highlight a database in red saying that there was not a good backup found within the past whatever time frame which made sense since we had dropped that database over a year ago.

Yeah, the report was so good, it wanted to tell me that I had not backed up a database that didn’t even exist. Thanks guys, I appreciate that bit of info. So, I am one that likes to write my own reports and not rely on vendors to get the job done for me. Sadly I do not write a lot of reports, but I do have lots of snippets of code laying around for just such an occasion.

Anyway, the tip can be found here, and I hope you find it useful someday.