Automatically transfer sql database to another

Question

Hello,

I would like to automatically transfer every week for example my sql database in production to my sql database in qa.

How can I do this on Azure?

Knowing that my two databases are in azure sql servers.

Thank you in advance !

Answer 1

You can create an Azure Automation job that can drop the QA database, and creates a copy of the Production database with the following T-SQL statement:

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

You can also use PowerShell script as shown below That Powershell scipt can be part of an Azure Automation job or a Windows schedule job.

Check if COPY database already exists - if so delete it

    Get-AzureRmSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $SqlServerName
`` -DatabaseName $databaseCopyName
    Remove-AzureRmSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $SqlServerName
    -DatabaseName $databaseCopyName `
    -Force

Create COPY database

New-AzureRmSqlDatabaseCopy -ResourceGroupName rg-resourcegroupname -ServerName sql-servername
-Tags @{key="value"} -DatabaseName sqldb-databasename
-CopyResourceGroupName rg-resourcegroupname -CopyServerName sql-servername
-CopyDatabaseName sqldb-databasename-copy

Answer 2

Hey,
You can use powershell to extract the prod database backup and restore it in nother server.
https://www.sqlshack.com/how-to-perform-azure-sql-database-import-export-operations-using-powershell/

To have a scheduled power shell job ,you can leverage either Azure Automation or Azure functions.

Another way would be via data sync option to sync data across the 2 databases