My experience with the RedGate SQL Azure Backup tool

Success!!

I should end this blog post here. You should simply be happy that it worked. I got it to work. Woohoo!!

But, alas, I write here to help me. And you. So, on with the explanation.

Last night, I had some issues with the command line tool seemingly doing nothing as I submitted to it various commands that I knew worked elsewhere. Manually I could get it to work just fine. Thru powershell or SSM via xp_cmdshell, I could not.

After various discussions with Grant Fritchey (t|b) and others at RedGate, Grant asked the dumb question that needed to be asked. "What version are you on? I'm on version X". While I thought this was silly, as did he, that was the solution. The version that I had simply wasn't working like we both expected. Upgrade, and viola! success.

So here is what I did. On my local machine, I created 1 SQL Agent Job was created with 3 steps. This job will reach out to the cloud, grab the database I want to backup, and bring it down to my box for safe keeping.

Step 1 checks if the database exists, and drops it if it does. Here is the code:

1. IF EXISTS ( SELECT * FROM master.sys.databases WHERE name = 'MyLocalDatabase')
2. DROP DATABASE [MyLocalDatabase]

Step 2 calls the Command Line utility for SQL Azure Backup. Here is the code:

1. EXEC sys.xp_cmdshell 'C:\redgate.sqlazurebackupcommandline.exe
2. /as:MyDatabase.Database.Windows.Net
3. /ad:MyDatabase
4. /au:MyUser
5. /ap:MyPassword
6. /ls:MyLocalMachine
7. /ld:MyLocalDatabase'

Step 3 calls the Command Line utility for SQL Azure Backup. Here is the code:

1. IF EXISTS ( SELECT * FROM master.sys.databases WHERE name = 'MyLocalDatabase')
2. SELECT 'Database exists'
3. ELSE
4. BEGIN
5. SELECT 'Database does not exist'
6. RAISERROR( 'Database does not exist', 16, 1)
7. end

Nice and simple. This job will backup my Azure database to a local copy.

You may be asking yourself 'Why?'. Since Azure has redundant copies of my database already, why do I need to make a backup at all? The scenario in which this task becomes important to me is within the scope of Release Management. As we are uploading new features to our Azure hosted solution, we inevitably need to make database changes. In order to ensure continuity of service and function, I ALWAYS enforce a process of backup/snapshot/something of the existing database, prior to and after changes. This extra step will save me time if we ever need to revert back to another version of the database, even an Azure database.

My basic release process is like this.

1. Become involved in the Release as soon as possible to assist with architecture
2. Review proposed DB changes
3. Produce Release Plan of all steps necessary to perform release
4. Review Release Plan with all necessary folks
5. Ratify Release Plan as a group
6. Test Release Plan in environment that mimics target environment
7. Prior to actual release, take a Snapshot and/or Backup of target database
8. Perform Release according to Release Plan
9. After actual release, take a Snapshot and/or Backup of target database
10. Compare Pre and Post Snapshots and/or Backups
11. Resume normal life

In the normal DB world (not with my head in the Clouds) I have various tools and processes that have helped me along this path to a very successful release process. Granted the process may not fit in every situation and needs to be flexible, for the most part I follow it to a T. Once a process has been established, and a set of tasks can occur to fulfill that process, automation is my next step. Remove the human factor from the steps to ensure success. Remove me from remembering what steps i need to take to accomplish the task. Automate!!

Introduce the Cloud, and the inability to perform some of these tasks and I get freaked out a bit. Luckily, designing solutions is something I enjoy doing, and this was a fun one to work on. I now have an automated task that I can click once, and perform the backup I need, allowing me to continue with my Release Process.

My thanks to those that helped me. I hope that this may help you as well. It has helped me. I now have a repeatable task that I can kick off and not worry that Ive forgotten any piece of the puzzle. Next release to the Cloud (next friday) we'll use this little piece of the repeatable puzzle. Soon, a monkey could perform the steps for us. And yes, I may be that monkey.