We are inspecting a possibility of using automated scripts utilizing the SQLAzureMWBatchBackup and SQLAzureMWBatchRestore tools to do data migration between our development, testing and production databases in Azure.

Is it possible to filter database objects (e.g. select only certain user tables) when doing the export with the SQLAzureMWBatchBackup tool? By looking at the configuration file it would seem to me that this is not possible.

Thanks in advance.

Hi,

You are correct in that SQLAzureMWBatchBackup does not allow you to select specific tables for backup.  Currently that can only be done through SQLAzureMW.  But that said, I could probably create an Object Selection xml file that would allow you to specify types, tables, views, stored procedures … whatever you wanted.  Basically, I would evaluate the database objects, look for the database object in the Object Selection xml file and if I found it, then I would export it.  If I didn’t find it in the xml file, then I would ignore it.  Would something like that work?  Since this would be something new, I would welcome other / better ideas on how to do this.

Thoughts?

George

Hello George,

Thank you for the info.

The proposed Object Selection xml file would work very well for us and we would greatly appreciate this kind of functionality in the tool.

Cool,  I will see if I can't get that into the next version.  Hopefully this week timeframe or maybe over the weekend.

That would be truly awesome. Thank you for providing such a good service.

I'll still try to outline the planned operation (one of the many scenarios) of our script below:

1. We initially have (or create) an empty destination database.

2. Initial set of database tables (static "system data") required by our service is created and populated in the destination database (this is done automatically by our service)

3. A transactionally consistent copy of a source database (with live production data) is created.

4. We use the SQLAzureMWBatchBackup tool to export selected database tables (we call this user data) from the copied database.

5. We use the SQLAzureMWBatchUpload tool to import the exported stuff to the destination database.

6. At this point, it is possible that the data in the source database has been modified since our export, we transfer this modified data from source database to destination database (a technical solution to do this is still to be invented *))

7. We swap source and destination databases taking the destination database effectively in production.

8. Again, it is possible that the source database was still modified between steps 6 and 7. We redo the transfer described in step 6.

*) Initial ideas to achieve step 6 was to detect modified rows inspecting timestamp values maintained in the tables and use plain SQL to extract the modified rows as update statements from the source database. Any suggestions would be welcome here.

Any comments?

One of the things that I would think about is using SQL Azure Daya Sync http://www.microsoft.com/en-us/download/details.aspx?id=27693.  You can use this to sync your SQL Database to an on-premise database.  Then any backups that you want to do, you can do directly off your on-premise database.

Thanks again for your comments.

We have considered using the Data Sync, but we felt that it probably does not fit our purpose very well. Maybe I should give it another look.

In any case, for steps 4 and 5 described above the SQLAzureMWBatch tools work very well for us the only limitation currently being the inability to select exported database objects (likely to be only tables in our case, at least initially) as already discussed.

The capability to select which database objects you want to backup has been added to SQLAzureMWBatchBackup.  To specify which objects to backup, you will need to edit the ObjectSelector.xml file.  Here is the contents of that file:

<?xml version="1.0"?>

<!--

   Ok, the way this works is for each object type you have a script parameter where:

   true = yes, script this object type

   false = no, do not script this object type

   Now, if you want to script only specific objects, then you can add the node

   SQLObject and specify which one(s) you want to script or not to script.

   The SQLObject node contains two property values:

   1) script = true means script objects where name is equal to text value

             = false means do not script any objects where name is equal to text value

   2) regex = true means that the text value is a regex expression

            = false means that the text value is not a regex expression and we should look for an exact match

   *** NOTE ***

   As I go through the SQLObjects list, as soon as I find a match on a name, I return.

   This means if you have something like this:

        <SQLObjects>

            <SQLObject script="true" regex="true" name=".*" />

            <SQLObject script="false" regex="false" schema=".*" name="myTable" />

        </SQLObjects>

    In the above example myTable will never be filter out because it matches the first rule.  But if you reversed this:

        <SQLObjects>

            <SQLObject script="false" regex="false" schema=".*" name="myTable" />

            <SQLObject script="true" regex="true" name=".*" />

        </SQLObjects>

    Then you would be saying script all tables except myTabel.  Note that schema is optional.  If you do not

    include it in the SQLObject node, then it is ignored and only the name will be checked.

-->

<ObjectSelector xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

    <Roles script="true"></Roles>

    <Views script="true"></Views>

    <UserDefinedFunctions script="true"></UserDefinedFunctions>

    <UserDefinedDataTypes script="true"></UserDefinedDataTypes>

    <UserDefinedTableTypes script="true"></UserDefinedTableTypes>

    <StoredProcedures script="true"></StoredProcedures>

    <Triggers script="true"></Triggers>

    <Schemas script="true"></Schemas>

    <SchemaCollections script="false"></SchemaCollections>

    <Tables script="true">

        <SQLObjects>

            <SQLObject script="true" regex="true" schema=".*" name=".*" />

        </SQLObjects>

    </Tables>

</ObjectSelector>

Thanks vipreese for your testing and feedback.

Regards,

George

Many, many thanks George.

The object selector works great!