Geeks With Blogs
Scott Klein

In a previous post I discussed how to build an SSIS package to automate the backing up (COPY) of a SQL Azure database. I assumed that once the SSIS package was built that people would know how to deploy the package and create a schedule for it to run automatically. I have had several people let me know that just building the SSIS package was not enough, that the additional information of deploying the package and creating a job for it is needed.

The first step is to compile and build your SSIS project (if you haven't done it already), which will create the necessary SSIS package.

The package now needs to be deployed to SSIS, and there are several ways to do that. For the sake of this example, we'll deploy the package manually. Log in to your SSIS server.

Expand the Stored Packages node and right mouse click on the MSDB node. Select New Folder from the context menu.

In the Create New Folder dialog, enter a name for folder where you want to deploy your SSIS package to, then click OK.

Once the new folder is created, right mouse click the new folder and select Import Package from the context menu.

In the Import Package dialog, change the Package location to File System, then in the Package Path browse to the location of the SSIS package compiled and built in the first step above. The Package Name will default to the name of the SSIS package. Click OK on the Import Package dialog.

At this point you have deployed your SSIS package to SSIS and the next step is to create a schedule which will run this package automatically as defined by you. In SQL Server Management Studio, log in to SQL Server and expand the SQL Server Agent node. Underneath the SQL Server Agent node, right mouse click on the Jobs node and select New Job.

The New Job dialog has several pages. On the General page, enter a name for this new job, such as Copy Azure Database. Select the Steps page, and click the New button toward the bottom of the dialog to create a new step for this job. On the New Job Step dialog, we need to add and select some information the General page.

First, enter a name for the step, such as CopyDB. For the step Type, select SQL Server Integration Services Package. For the package source, select your SSIS server and enter the appropriate credentials, then select the package by click the elipse button and selecting the package you deployed earlier. Click OK on the New Job Step dialog.

Back on the New Job dialog, select the Schedules page and click the New button toward the bottom of the dialog to create a new schedule for this job. Enter a new for the schedule, such as CopyDB_Time, then configure the frequency and duration for this job. For example, if you want the backup to automatically happen daily at 1:00 AM, select Daily for the frequency then change the time to 1:00 AM for the time. Once you have configured the backup schedule to your liking, click OK on the New Job Schedule dialog which will take you back to the New Job dialog.

Unless you want to configure any alerts or notifications, you have successfully scheduled the automatic backup of your SQL Azure database. You can try it by right mouse clicking on the new job and selecting Start Job At Step from the context menu. Since we only created one step for this job, the job will automatically start.

Whether you backup your database using the CREATE DATABASE statement in SSMS or use the method described in this poast, both will complete fairly quick, within seconds actually. The CREATE DATABASE statement returns successful when the database copy is stil in progress. You can monitor the progress of the copy using the sys.dm_database_copies and sys.databases views.

Anyway, I hope this helps.


Posted on Tuesday, September 21, 2010 5:22 PM SQLServerPedia Syndication | Back to top

Comments on this post: Automating a SQL Azure Database Copy - Part 2

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © ScottKlein | Powered by: