SQL Server Backup and Restores with PowerShell Part 2: Backing up a database

In my previous post we talked about setting up your local environment to use SQL Server and PowerShell together, so now let’s put this into practice. The trick is finding something worth utilizing PowerShell for within SQL Server. And while the last thing you want to do is make something that can be done in T-SQL alone more complex than it has to be, sometimes there’s a good reason. So before we get to the code, allow me to share a quick story how this project came to be.

“Tape? What tape?”

At the company where I work, the decision was made to eliminate tape backup drives. Which is fine, and is a trend most IT shops are embracing now that bandwidth and co-location has made off-site replication or storage more attractive. However, in doing so, that compromised our current SQL backup and retention strategy: each night, backups were written from disk to tape, and the tapes changed daily. This meant our last backup was written to a tape and shipped offsite before the next backup. Tapes were also maintained for 30 days before they were rotated back to us for re-use, so we had a comfortable cushion of 30 days worth of backups of the unimaginable happened. Of course, this scenario doesn’t account for a total server loss, or the time it might take for a tape to be retrieved, but it was what the business wanted, so we ran with it.

But with no tape backups, our process, if left unchanged, would just be overwriting each night’s backups with the newest one. This might have been fine if we needed to go back up to 24 hours, and served our purposes for non-production database refreshes, but we were exposed if something happened to the data and it wasn’t caught before we overwrote our backups. Not a good thing.

We worked with the system and storage engineers on how and where we’d be storing our new backups. The DBAs were given a long-term storage device that had oodles of storage, and the vendor also had the capability to replicate the data stored on it to a remote data center with another of the same type of device.

The next problem became, how can we back-up multiple day’s worth of data to different file names each day? And what about retention? Previously we relied on backup devices inside SQL server to do the trick, but I went… a slightly different route.

Powershell keeps me sane

Since I didn’t want to try to automate the deletion and recreation of backup devices each night, I wrote a PowerShell script to handle my SQL backups. The requirements were as follows:

  1. The script should allow for each type of backup: Full, Differential, and Transaction Log backups.
  2. The backups files should be written to a given UNC path, have the name of the database, type of backup, and time stamp all in the file name.
  3. The script should also automatically create and maintain a directory structure for the files along the lines of “SERVERNAME\DATABASE NAME\BACKUPTYPE.” This would make it easy to zero-in on whatever backup set I needed. It would also make it easy to clean up older backups.

And once I got started, there were a couple little “nice to haves” that became features too:

  1. Unless specified, the script will attempt to backup each database.
  2. Compression is enabled by default.
  3. There’s an option split the full backups into multiple files instead of one per database (a trick to speeding them up in SQL Server 2008).
  4. An ability to do a COPY_ONLY backup (for non-prod restores or separate backups).
  5. Use TDE and/or SSIS on your server? Then you can also backup your database certificates and private keys!

The actual code

For examples on how to execute it, and some explanation on what it does, keep reading below. You should also bear in mind that while this code works, it’s very much a work in progress, and I plan on maintaining it either here or GitHub. There’s also some undocumented features that I am experimenting with.

And, it bears repeating but just in case this is the first time you’ve ever copied and pasted code from the internet…

DISCLAIMER: Okay look, you probably already know this but this code you’re about to use was written by me. It works for me as described, but computers are hard and sometimes things don’t work right. Therefore, I’d like to casually remind you that you probably shouldn’t load this script up in your production environment all willy-nilly. As with any new software or functionality, test it first on your local instance or in a development environment.

A simple example

Let’s say you wanted to take a full backup of all your SQL databases on one server. It’s as simple as this:

Simply run the script and provide the -servername, -backuptype, and -backuplocation parameters. It will handle the rest. To backup your system databases,  the -systemdatabases switch will force (-force, literally) the system objects to be backed-up as well, and the -seperatedirectories switch will force a new directory for each database, and inside it will create a backup type (full, log, differential) folder and store the backups within them. The example above then will just connect to your local instance, and conduct a full backup of each database and maintain the folder structure. To get detailed status messages, this script also supports -verbose.

How it works

This script utilizes a couple key PowerShell features and modules; first and foremost, it requires the SQLPS module. And it uses the Backup-SQLDatabase cmdlet. It’s mostly straightforward, but it accepts all kinds of neat parameters, including a backup set object if you wanted to split or stripe your backups across multiple files. Otherwise, it’s just a big series of Get-ChildItem cmdlets mixed with some string concatenation and some if statements and conditional logic.

Line numbers of note

  • 187: If you decide to use the seperatedirectories option, this first makes sure the path it needs to create exists, then creates it if it doesn’t
  • 260: The $fullbackup variable holds a seperate object just for the SMO backup object. This is because we can dynamically build one backup device, or multiple, and then add the entire object collection to the Backup-SQLDatabase cmdlet.
  • 280,296: In PowerShell, it’s possible to dynamically build a list of parameters to a cmdlet. That’s exactly what is being declared in line 280, and applied in line 296

Why do this?

Of course, the real question is: why take something so basic as backups and use PowerShell? Well, as you’ll see in the next part when we need to restore databases, just PowerShell is good at keeping your file structures organized when you backup, you can also leverage that same functionality to automatically manage your restores!