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.

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.

I don't know anyone, I don't sing karaoke, I don't drink. Can I still have fun at SQLPASS? addendum

I wrote this blog post the other day about SQL PASS and how to experience it and ensure that you experience it all. Break out of your shell and soak it all in.

But I forgot to mention a few things, and I hope to clear these up in this post.

A few of my favorite things that always happen at the Summit are

SQL Server Central opening party

Welcome Reception & Quiz Bowl

Exhibitor Reception

Women in Technology Luncheon

Friends of RedGate Dinner

Keep in mind that these events are on different dates and times. And they often come with food. And people. People that you should be networking with. In a situation that allows you to network and get to know each other, ifn you'll simply open your mouth and talk to the other humans.

The SQL Server Central party is one of my favorites. Its always a relaxing event, with easy social interactions. Steve Jones and Red Gate put on a great party that allow us humans to interact easily with games, giveaways and fun. I would suggest you find your way to this event and join in. Its a hoot. Maybe you'll loose at whatever gambling game you participate in. Maybe you'll win a prize as Steve randomly circles the event giving out tons of swag. Maybe you'll make a fool of yourself and have the entire table yelling and screeching as you attempt to reach 21 and explode when you actually reach 21. In any event. you get my drift. You should attend, meet some great folks, play some games, and you might even get a descent shirt out of it.

The Welcome Reception and Quiz Bowl are a huge opening event, prior to the SQL Server Central party. It has food. It often has music. I've seen video games in the middle of the room. It may even have food. You wont really need to buy dinner. And all of a sudden there will be a few folks up on stage answering questions and amazing you with their knowledge and humor. The Quiz Bowl is always an entertaining event to participate in. You could even win a book or other swag as the quiz is going on by merely being close enough to the stage to join in.

On Wednesday night, after all the sessions of the day, you'll find yourself wondering into the Exhibitor Reception, which is located in the Exhibitor Hall. Besides all the people you can network with and wave at and join in with conversations, there's food. Again, dinner doesnt need to be purchased as there will be descent food here to keep your motor running. Take an opportunity to wonder around and visit with the vendors. Get some swag. Get some food. But don't forget to talk to the humans. They are all around. They have knowledge that can assist you in your day to day work. Or maybe you can help them. Maybe you need to make a list of things to talk about and refer to it, to assist you. But open your mouth. Talk to the others. Remember we are all there for the same purpose. To learn. To Share. To Network.

As a father with female offspring that I often do not understand, I find that attending the Women in Technology Luncheon is an eye opening event. Learning from the expert panel is always enlightening. I tend to sit there and think about my girls and how I can share this information with them. Help them enter the workforce ready and armed with what they may need to make it easier on them. This event, though it contains the value 'Women' is not solely intended for the women of the Summit. Its for us all. It helps us all. Oh, did I mention that there is food? Yeah, there is. Lunch. Good lunch. As you attend this event, put your head on straight and participate. Soak in the atmosphere and the topic.

Another event that I love to attend is the Friends of RedGate Dinner. This is such an honor to be a member of this fine club. Being able to rub shoulders in a social environment with the employees of this company is always engaging and fun. There is food, but that's actually not the top priority. Being able to interact with this crew of folks by far outweighs the food. This allows mere humans like me to be in the presence of many fine folks that have influence in the community as a whole. Open up your ears and you can learn tons from these folks. By volunteering in the community, gaining a position of influence and being able to share with folks is what allows you to become a member of this group of 'Friends'. Sidle up to these folks and learn. Learn all you can.

So, as you can tell, there are a lot of scheduled events that occur. Check out the schedule yourself. Try to hit as many of these events as possible. Especially these on my list of favorites. Maybe some of these can become your favorites.

Other things that you can do that I have loved to do.

Photo Walk

Breakfast at Lowells

Grabbing fresh fruit and other snacks at Pike Place

Wandering the streets towards Pike Place

Visiting a French Bakery with your new southern brother

Make yourself some goals. Write up some talking points and use them in a pinch to open discussions with other people. I guarantee that if you will make an effort and talk to the humans, you will find several fine folks that will become fast friends and cohorts in this journey we are all on to better ourselves thru professional development and education. You'll learn or share what you have learned. You'll make friends. You'll experience the best of SQLPASS.

I don't know anyone, I don't sing karaoke, I don't drink. Can I still have fun at SQLPASS?

The Summit for the Professional Association for SQL Server is the premiere event of the year for the group commonly referred to as PASS. Its coming up soon. In October to be more precise. If you are not planning on attending, stop what you are doing and make plans.

I attended my first PASS summit in 2004 in Florida after the hurricane had hit the Florida area. Since Disney World was close by, my family came along with me. We rented a condo and stayed there for the duration. I had a rental car, and I would get dropped off at the Summit while they went to Disney World. I joined them twice, leaving the summit once early, and again on Friday. We went as an entire family on Saturday and then flew home. Each day I would arrive at the summit after breakfast and usually after the keynote. I would attend session and after session, learning tons of valuable information, and then meet my family somehow afterwards. That was the extent of my participation in the summit.

Looking back and realizing that some amazing memories were created with my family at Disney World, I think I would do things differently now. Knowing what I now know.

I would attend the summit earlier each day, earlier than breakfast. People are there already, milling about. I could get to know them. We could network. I could learn from them. No just the sessions. I would talk to more people while at breakfast. I would ask what they did, what they struggled with, and I would share those same things with them from my day to day experiences.

I would attend the keynotes and instead of making sure I had that saftey chair between me and anyone else in my row, I would sit next to someone. In fact, if I saw a group of folks talking and chatting, i might siddle up to them, if nothing else than to listen in and hope to join the conversation when I could. Inbetween sessions I might look again for a group of folks that seem to be discussing interesting topics and join in. If I was late to a session, I wouldn't stress about it. I'd attend when I could.

But what about drinking? What about all the parties? What about SQLKaraoke? What about wearing a Kilt? None of these things are things I would do in my day to day life. I never go to a bar. I have never drank alcohol in my life, nor do I smoke. Why would I go to something like this? Should I go to a gathering like this? Should I wear a kilt?

Well, let me tell you that after the first summit, I went to my next summit and implemented some of the things I mentioned above that I said I should have done from the beginning. I met people. I talked to people. I ate breakfast with people. We discussed issues. Soon I learned that there were even more networking opportunities out there for me. After the sessions had closed, people kept doing things. Kept hanging out. Yes, lots of them drink and even tend to sing crudely in front of an audience. Some will even wear a kilt. And no, these are not things I usually do, so I would usually return to my hotel. I should avoid them? WRONG!!

At one of my early volunteer outings with other PASS volunteers I realized that even if I didn't drink, I could still have fun with people that did. I talked. I networked. I commiserated. I discussed issues. I partied like I party. When someone tried to make fun of me for not drinking, another PASS volunteer stood up and defended me. He didnt drink either. But he sure likes pie. Instead of leaving or avoiding the gathering, I met more and more people. More and more opened up to me. I met even more people. It's insane the number of people you can meet if you open yourself up to meeting them.

Remember, they are all like you. There is not a cool kids club. We are all part of the club. Its up to us to determine how much we participate. Or not. And if we don't, we are simply hurting ourselves. Because others are meeting, talking, planning, sharing and learning from each other. Why aren't you out there with them doing the same?

Sleep when you return home from PASS. Soak up every moment you can with the SQLFamily.

Get out there and meet them. Open up. Experience PASS.