CODEWITHSUNDEEP
azure-sql-databaseazure-sql-serverbacpac
77Vetter
77Vetter

Reputation: 269

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?

Upvotes: 1

Views: 4676

Answers (2)

pim
pim

Reputation: 12587

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.

Upvotes: 1

Alberto Morillo
Alberto Morillo

Reputation: 15688

You just need to copy the production database using the portal or PowerShell 

New-AzureRmSqlDatabaseCopy -ResourceGroupName "myResourceGroup" `
    -ServerName $sourceserver `
    -DatabaseName "MySampleDatabase" `
    -CopyResourceGroupName "myResourceGroup" `
    -CopyServerName $targetserver `
    -CopyDatabaseName "CopyOfMySampleDatabase"

You can also automate refreshing the development database by recreating it using Azure Automation and the following T-SQL statement.

CREATE DATABASE db_copy   
    AS COPY OF ozabzw7545.db_original ( SERVICE_OBJECTIVE = 'P2' );

Upvotes: 1

Related Questions