Azure环境下SQL生产库数据复制至测试库的自动化方案咨询

Awesome question! Let’s walk through the best ways to set up that "one-click sync" from your production Azure SQL Database to test, along with solid backup options if the ideal setup isn’t quite what you expected.

最优接近一键式方案：Azure Automation Runbook（一键触发+自动清空） #

Azure doesn’t have a native one-click button for exactly this workflow, but you can configure a setup that feels just like it—with minimal initial work:

1. Set up an Azure Automation Runbook

   • Create a PowerShell Runbook that handles two key steps:
     • First, delete the existing test database (this effectively "clears" it before the copy)
     • Then, copy your production database to a new test database (which inherits the exact schema and latest data)
   • Here’s a sample script you can adapt:

     # Use managed identity to authenticate (no hardcoded credentials!)
     Connect-AzAccount -Identity
     
     # Define your resource details
     $resourceGroup = "your-resource-group-name"
     $sqlServer = "your-sql-server-name"
     $prodDb = "production"
     $testDb = "test"
     
     # Delete existing test DB if it exists
     try {
         Remove-AzSqlDatabase -ResourceGroupName $resourceGroup -ServerName $sqlServer -DatabaseName $testDb -Force
         Write-Output "Existing test database deleted successfully"
     }
     catch {
         Write-Output "No test database found to delete—proceeding with copy"
     }
     
     # Copy production DB to new test DB
     New-AzSqlDatabaseCopy -ResourceGroupName $resourceGroup -ServerName $sqlServer -DatabaseName $prodDb `
         -CopyResourceGroupName $resourceGroup -CopyServerName $sqlServer -CopyDatabaseName $testDb
     Write-Output "Copy job initiated—test DB will have latest production data once complete"
     

   • Grant the Runbook’s managed identity the SQL Server Contributor role on your resource group so it has permission to delete and copy databases.
   • Once configured, you just click the "Start" button on the Runbook in the Azure Portal to trigger the full sync—exactly that one-click experience you want.

2. Bonus: Add scheduled automation
   If you want to auto-sync on a regular basis (like every Sunday night), you can attach a schedule to the Runbook to skip manual triggers entirely.

次优方案：Logic Apps (No-Code Visual Workflow) #

If you prefer no-code tools over writing scripts, Azure Logic Apps is a great alternative:

• Build a workflow with these steps:
  • Trigger: Manual button (so you can launch it with one click)
  • Action: Delete the existing test database
  • Action: Export the production database to an Azure Blob Storage container
  • Action: Import the backup file from Blob Storage into a new test database
• This setup is fully visual, so you don’t need to write any code. The tradeoff is that export/import is slower than direct database copy, especially for large datasets.

Quick Notes to Keep in Mind #

• Since your prod and test schemas are already identical, the copy/export-import processes will preserve that alignment. If prod schema changes later, the copy will automatically bring those changes over to test.
• If you need to keep specific test-only data in certain tables (like test user accounts), you can tweak the Runbook script: after copying the prod data, add steps to insert or restore those test-specific records.

内容的提问来源于stack exchange，提问作者Mark Lisoway