Backup Windows Server on EC2 with PowerShell and AWS CLI

When we first started working with Windows Server or Amazon EC2, we ran into the problem of how to back up SQL Server databases. My initial thought was to somehow mount S3 as a volume and have SQL Server write backup jobs to it. That turns out to be kind of hard.

After a little head-scratching, I realized that EBS volume snapshots are stored in S3. Therefore, all we need to do is mount an EBS volume and have SQL Server write backup jobs to that and then snapshot that volume. Oh, and unmount it from the running Windows server before snapshot to make sure that we are taking clean snapshots.

It turns out to not be too hard to make this happen on Windows Server 2012 Core using a PowerShell script that drives the AWS CLI and does the hoodoo with the Windows volume manager to mount and dismount the EBS backup volume.

This solution requires PowerShell Storage Cmdlets which ship with Windows Sever 2012/ and Amazon Web Services python-based command-line interface. Works great on Windows Server Core.

The biggest problem that I ran into was that the script would run just fine when invoked manually or when I would trigger the job from schtasks while logged in, but it would hang and fail when run from scheduled tasks in the middle of the night. Long story short: when schtasks runs the job with nobody logged in it gives the .DEFAULT environment instead of the environment of the user context of the scheduled task. That meant that AWSCLI didn’t receive the correct %USERPROFILE% environmental variable and was not able to locate its config file with the user id and key.

The simplest solution was to wrap the invocation of the powershell script in a cmd batch script:

set USERPROFILE=C:\Users\Administrator\

powershell.exe -file “C:\Program Files\Invoke-BackupJob.ps1” -path E:\ -diskNumber 2 -ec2VolumeID vol-<id-number-here> -description “Important Backup” > “C:\Program Files\Utility\Log.txt”

Use the PowerShell Get-Disk cmdlet to figure out the disk number of the EBS volume in Windows and the EC2 console or AWSCLI to figure out the EBS volume ID in EC2.

[gist https://gist.github.com/breiter/7887782]