I need to move all the data of a production database to its staging version. These 2 databases are Azure SQL Databases from 2 different resource groups.
I have read some guides on how to achieve this exporting and importing .bacpac files, but this approach doesn't allow to import into an existing database.
I'm looking for a way for migrating the data without creating a new database, but targeting the already existing one.
Azure SQL Database does not have a backup and restore (WITH REPLACE option) as we know it on SQL Server. We also cannot export a database and import it to an existing database.
You can import with a new name and then drop the existing database, and rename the name of the imported database to the name of the dropped database.
You can also use an initial sync of Azure SQL Data Sync to migrate all you data from one database to another. Not only your databases can be located on different resource groups, but also in different subscriptions. The initial sync runs faster on empty databases.
You can export your DB with SMMS and restore it to another server/new DB if this is one time task.
You can also take frequest backups and store them to azure storage account as .bak and restore from them. You can use an automation runbook/powershell script similar to one given here :
https://www.powershellgallery.com/packages/Backup-SQLAzureDb/0.2.0
If both the DBs are on Azure SQL you can copy using simple powershell command directlay to new server
New-AzSqlDatabaseCopy -ResourceGroupName "<resourceGroup>" -ServerName $sourceserver -DatabaseName "<databaseName>" `
-CopyResourceGroupName "myResourceGroup" -CopyServerName $targetserver -CopyDatabaseName "CopyOfMySampleDatabase"New-AzSqlDatabaseCopy -ResourceGroupName "<resourceGroup>" -ServerName $sourceserver -DatabaseName "<databaseName>" `
-CopyResourceGroupName "myResourceGroup" -CopyServerName $targetserver -CopyDatabaseName "CopyOfMySampleDatabase"Or use a copy of TSQL command :
CREATE DATABASE Database2 AS COPY OF server1.Database1;