2

I am trying to copy my existing AzureSQL (singleton) database from PRODUCTION subscription into a NON-PRODUCTION database in a different subscription. I need to repeat this process every night so that our production support environment (non-prod) has the latest copy from production from the night before. Since overwriting database is not possible in AzureSQL, I would like to copy "DBProd" from PROD server as "DBProd2" on to my Non-Prod server (in a different subscription), then delete the existing "DBProd" from the destination server and rename "DBProd2" to "DBProd".

I have searched through this site to find answers and the closest I found was this link below...

Cross Subscription Copying of Databases on Windows Azure SQL Database

In that link user @PaulH submitted the below answer...

"This works for me across subscriptions without having matching SQL Server accounts. I am a member of the server active directory admin group on both source and target servers, and connecting using AD authentication with MFA. – paulH Mar 25 at 11:22"

However, I could not figure out the details of how it was achieved. My preference is to use power shell script to get this done. If any of you had done this before, I would appreciate a snippet of sample code, or any pointers to achieve this.

My other option is to go the BACPAC route (export and import), but I would only want to resort to that if copying of DB across subscriptions is not possible.

Thanks in advnace!

Helios

Went through the link... Cross Subscription Copying of Databases on Windows Azure SQL Database

Sun
  • 21
  • 2

1 Answers1

0

The Move-AzureRmResource cmdlet may be all you need. Below how it works.

Let's say you create a new Azure SQL Server on a different resource group.

New-AzureSqlDatabaseServer -Location "East US" -AdministratorLogin "AdminLogin" -AdministratorLoginPassword "AdminPassword"

Copy the source database to the newly created Azure SQL Server.

Start-AzureSqlDatabaseCopy -ServerName "SourceServer" -DatabaseName "Orders" -PartnerServer "NewlyCreatedServer" -PartnerDatabase "OrdersCopy"

Move the resource group of the Newly created Azure SQL Server to another subscription.

Move-AzureRmResource -DestinationResourceGroupName [-DestinationSubscriptionId ] -ResourceId [-Force] [-ApiVersion ] [-Pre] [-DefaultProfile ] [-InformationAction ] [-InformationVariable ] [-WhatIf] [-Confirm] []

For more information, please read this DBAStackExchange thread.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30