My experience with the RedGate SQL Azure Backup tool

We have a few databases that are in SQL Azure. We need to back them up from time to time. Since there isn't an easy way to do this from SQL Azure itself, we have started using RedGate's SQL Azure Backup tool. The UI works great, even though the little slidey effect gets old after the first few executions. I was able to quickly and easily connect and backup my databases from the cloud to my local machine. Backup!!

The next time I went to backup, it errored, because the database already existed. I figured I could easily fix this with a quick rename of the existing database. But that was not the case. Since the backup was actually creating files based on the internal names and that DB already existed. I had to drop the database. I could have done something else, like backup the local, and restore it to keep a historical copy. But I simply dropped the last backed up db. Then the UI was able to backup without any issues.

Next? Well, why not try the command line. If you are like me, it will take you several (read many) iterations to get a command line anything to work properly. After quite a few iterations of stupid mistakes on my part, I finally got a simple command line script that would work and backup my db. Yeah!!

So, now I have a process. I drop the database. Then I run the command line and I have my database backed up. Yeah. Except for the parts where I have to do all those steps, over and over... that part sucks.

So I start thinking. I see these as 2 steps. 1 step to drop the db could be put into a SQL Agent job. The second step of running the command line, I could put into a batch file and execute via SQL via another step in the SQL Agent job. Yeah.

Boo.

The drop step works just fine. But the second step does not work. I have a batch file that I can execute and watch it process the backup successfully. But when this is wrapped in a SQL Agent job, it does nothing. So I grab the xp_cmdshell (I know, I know) statement and execute this in SSMS, it seems to take some time, then returns nothing. And apparently nothing has transpired either. No database was created. Nothing. No error. Hum....

I tried Powershell to no avail also.

So at this point, I have a job that kicks off both steps. Both succeed, but only the database is dropped. I will then manually kick off the batch file and backup the database via the RedGate tool command line.

It works. Its 2 steps. Its not automateable yet. But I am closer. Always getting closer.

Soon, I suspect, I will figure out what I did horribly wrong and remediate it, and I will have an automateable solution that will backup my Azure database to my local machine. At least I have that backup. And this will make me happier when we release code to the Azure database.

Backups, snapshots and a budding release process to the Cloud. We're getting closer.

Now I want to be more efficient.