Restoring contents of Azure SQL Database from Copied Database - Process

Question

Hi,

I've a process question regarding restoring Azure SQL databases. We've got an Azure SQL Database, currently used for exporting out data from a piece of CRM Software for BI Reporting. This contains all of the working tables and views for dashboard reporting. etc from our "Live" environment.

We also have another Azure SQL Database, which is linked to a separate test instance of the CRM Software. We're currently undergoing an upgrade process on the Software, which is happening on the test instance first.

To facilitate the test using current data, we've created a copy of the Live SQL database on Azure. What would be the best method of "restoring" the contents of our Live SQL Database to our Test SQL Database? We essentially want to just replicate everything that's on our Live SQL Database.

I've looked at the Data Sync functionality within Azure, but this only seemed to give me the option of syncing across tables and not views.etc

Thanks

Answer 1

Drop the existing Test database, then you just need to copy the Production database using the portal or PowerShell and set the name of the newly created database as the name of the database you initially dropped.

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_test_copy   
    AS COPY OF LiveProduction ( SERVICE_OBJECTIVE = 'P2' );