Copy Database Data on Azure into Another Database on Azure

Question

We have two sql databases, test and production, hosted on Microsoft Azure. Both databases have the same schema. Is there an automated or one-click way to copy all of the data from the production database into the test database. Whenever we make a new feature we want to be able to test it with up-to-date production data without testing it on the production database.

Any amount of initial setup is fine, but ideally the end result would be to essentially hit a button and the copy is done.

It is OK if the test database needs to be wiped beforehand.

If there is no way to do this on Azure, what would the next best option be to get this result?

so you are just going to get the data from production and use it in dev without any masking or anything? This is so wrong on so many levels. — Asdfg
– Asdfg, Commented May 17, 2018 at 22:16
@Asdfg Our app is relatively small, in-house app, and the sensitive data we are storing from the users is basically just first name, last name, and email. No purchase history, location details, birth dates, etc. I guess if there is a way to obfuscate the production in this one-click process that would even be more ideal. However, the goal of this is mainly to let management see what new features look like with live, up-to-date data. — Mark Lisoway
– Mark Lisoway, Commented May 17, 2018 at 22:54

Alberto Morillo · Accepted Answer · 2018-05-17 22:46:45Z

1

You just need to copy the 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' );

answered May 17, 2018 at 22:46

Alberto Morillo

15.8k3 gold badges29 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Satya_MSFT · Accepted Answer · 2018-05-17 23:02:19Z

0

You can run the following query by connecting to the production database: Create database [new_db] as copy of [prod_db];

You can also use database copy rest API / from portal, or even issue Point in time restore the production database. I would recommend doing this as it has least impact on the existing production database but it could take longer than the database copy.

answered May 17, 2018 at 23:02

Satya_MSFT

1,0226 silver badges10 bronze badges

Collectives™ on Stack Overflow

Copy Database Data on Azure into Another Database on Azure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related