DAC SQL Azure Import Export Service PowerShell Client Module

Posted on by

SQL Azure offers a hosted service to import/export Databases between SQL Azure and Azure Blob storage, essentially they have put up a REST API and you can fire commands at it.  There is even a Codeplex project with SQL DAC example client  implementations. 

When I recently attempted to automate exports of a number of databases we host in Azure I grabbed a copy of the client and wrapped it in PowerShell and thought job done. That’s where I ran into issue number one, the exe randomly hangs for me. 

C# is not one of my strong points, so I decided that attempting to debug the C# source probably wasn’t a good idea and instead I decided to re-implement the client in PowerShell. 

So a lot of coffee, detective work (the REST API isn’t very well documented currently) and digging around in the example source code I’ve put together a PowerShell module implementing the three main features.  Export, Import, Status.

Usage

I’ve attempted to keep the command switches as close as I could to the Codeplex project so if you’re switching from one to the other, you should be able to figure out what’s going on very quickly.  

start-DacExport -s <server> -d <database> -u <username> -p <password> -bloburl <bloburl> -blobaccesskey <key>

         
start-DacImport -s <server> -d <database> -u <username> -p <password> -bloburl <bloburl> -blobaccesskey <key> -size <inGB> -edition <web/business>


get-DacJobStatus -s <server> -u <username> -p <password> [-requestid <GUID> -withInfo]

Both start-DacExport and start-DacImport will return the GUID of the job, which you can then use with get-DacJobStatus.  get-DacJobStatus will return an xml object containing then job’s status information; this is great if you are using the function in your own script, but if you just want to print the results to screen make sure you use –withInfo and the XML will be sent to the console instead.

Installation Instructions

  • Create a new folder in called DacIESvcPS in your modules directory e.g C:\Windows\System32\WindowsPowerShell\v1.0\Modules\DacIESvcPS
  • Download the latest version of the PSM1 file from https://github.com/stevenaskwith/DacIESvcPS into the new directory
  • Launch a PowerShell console and run
    • import-module DacIESvcPS
  • To confirm module loaded correctly run
    • Get-Command -Module DacIESvcPS

You should get something like this:

image

An example output of get-DacJobStatus –withInfo would look like this:

db3prod-dacsvc.azure.com
<?xml version="1.0" encoding="ibm850"?>
<ArrayOfStatusInfo xmlns="http://schemas.datacontract.org/2004/07/Microsoft.SqlServer.Management.Dac.ServiceTypes" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <StatusInfo>
    <BlobUri>http://myExportBlob.blob.core.windows.net/sqlexports/someDatabase.bacpac</BlobUri>
    <DatabaseName>someDatabase</DatabaseName>
    <ErrorMessage />
    <LastModifiedTime>2012-03-22T10:18:57.1864719Z</LastModifiedTime>
    <QueuedTime>2012-03-22T10:16:03.7488387Z</QueuedTime>
    <RequestId>2bbbf314-3ec5-4f7c-afbd-ba219a61954b</RequestId>
    <RequestType>Import</RequestType>
    <ServerName>eccaps1fj1.database.windows.net</ServerName>
    <Status>Completed</Status>
  </StatusInfo>
</ArrayOfStatusInfo>

I would love to hear back from anyone who uses this in the field.

THIS POSTING AND CODE RELATED TO IT ARE PROVIDED “AS IS” AND INFERS NO WARRANTIES OR RIGHTS, USE AT YOUR OWN RISK

Leave a comment