azure-sql-databaseazure-sql-serverbacpac

Azure SQL DB Refresh From Production


Looking for the best practice on refreshing a QA/Test Azure SQL Database from a Production Azure SQL Database

The production database is on a different server and resource group. So just wondering the best method for getting the production data into the qa/testing database. What tools are available for a task like this?


Solution

  • The most common format of SQL Azure Database's is bacpac, and believe me when I tell you that it is AWESOME.

    Exporting

    The easiest way to do this is using the Azure Portal or with SSMS.

    This will however copy the entire database schema and all data. If you need something more specific, like excluding a table, look no further than sqlpackage.exe.

    .\sqlpackage.exe /Action:Export /ssn:SERVER /sdn:ADB /tf:"C:\PATH\TO\FILE.bacpac" /of /p:TableData=TABLE /p:TableData=TABLE /p:TableData=TABLE
    

    Importing

    To create a database from the .bacpac you created above, all three of the aforementioned methods also support importing.

    Recommendations

    I would apply the KISS principle here and just use the portal/SSMS on both ends. Dropping the specific tables you no longer want/need.