A Wandering Mind

This post is the fourth in an ongoing series about how to survive as the only DBA in your organization. Since October of last year, I’ve been assigned to a team that is responsible for owning and maintaining the development infrastructure. It’s a great team of seasoned professionals, but not a single other DBA. As a result, I’ve had to think very carefully about how I go about my daily work, so as to give our customers consistently good service, while still allowing those without a lot of SQL Server related knowledge to pick up my work when I’m not available.

Automation Is A (Lone) DBA’s Best Friend

I subscribe wholly to the sentiment expressed in John Sansom’s excellent post, titled “The Best Database Administrators Automate Everything”. Time is something we cannot make more of for ourselves, and as such is highly precious. If you are spending time doing repetitive work that could be either partially or fully automated, that is time wasted.

But there’s another benefit as well to automating processes: it makes them easier to hand-off. Let’s consider the example of restoring a copy of a production database to a test server. The typical steps I would complete would be:

1. Determine the location that the backup file was placed at by the production DBA group (I do not have access to production backups, so they must copy them down to my server).
2. Determine where the database files should be placed on the server using this logic:
1. If the database already exists on the server, the files should be placed at the same location as they currently reside.
2. If the database does not exist, then the files should be placed at the default data and log location as specified in the server configuration.
3. If the database exists, set the database to single user mode using the “WITH ROLLBACK IMMEDIATE” option to effectively kill any open connections to the database.
4. Issue the restore command, using native commands or the Litespeed equivalent if the database came from a server using Litespeed.
5. Execute the databases specific post restore script from the source code repository. These scripts reset permissions for developers, synchronize users, etc.

While none of these steps are particularly hard, documenting them and teaching them to others would not be a small amount of work. Consider that step two involves querying system tables (or stepping through some screens in SSMS), and steps three and four require knowledge of T-SQL commands for restoring databases.

Instead, I chose to write a Powershell script which handles as much of the logic as possible. All it requires is a server name and the name of a backup file, and it handles the rest (except step five, but I’m working on that). This way, instead of teaching all that logic, all I need to do is show someone how to find the backup file (further simplified by standardizing paths across all the servers) and run the script.

I don’t know about you, but I’d much rather write code to do a job than write a document about how to do the job manually.

Be Sociable, Share!

•