Identifying Databases Not Meeting Backup Service Level Agreements (SLAs)

A quick way to find out if your backups are working as you expect

One of the questions I always ask database administrator interviewees is “What is the number one job of a DBA?”.  I feel the answer says a lot about how this possible candidate thinks, and on another level if they read the job description or not.  What I hope to hear is “Ensure recoverability in the event of a disaster” or something along those lines.  If it wasn’t obvious, I feel strongly that recoverability of your database(s) is priority number one.

Because there is an ever-present need to ensure your backups are current and meeting your establish Service Level Agreement (SLA) with the business, it’s probably a good idea to have a handy query that will get this for you.  I also like to operate on the notion that “No News is Good News”, so the output of this report shows only databases failing the SLA.

Fully Script out a MSSQL Database Role

A New Hope

SQL Server Management Studio is a phenomenal tool, especially when you want to script out complex tasks.  Do whatever you want in the GUI and click the handy little script button instead of OK and you’ve got a set of raw SQL that you can modify/reuse/etc.  This process works for a good majority of the operations you will find yourself doing as a DBA, but there’s one gaping hole that MS has yet to fill: scripting out Database Roles.  If you manage any level of database security, chances are you are using Database Roles.  If you were lucky enough to create them yourself, it’s not difficult to keep a script library to see what role allows for what permission(s).  However, the moment you inherit a database that contains custom Database Roles, you enter a world of hurt.  Traditionally, you can either painstakingly struggle with the GUI trying to associate various records and checkboxes with actual commands or you can throw in the towel and start from scratch.  Thankfully, there is a new way, as I’ve scripted out a process that will generate the role definition for you.