In this article we will demonstrate how to copy an Azure SQL DB to a different server using Azure Automation.
It applies to copying databases across subscriptions and tenants as well.
This article will explain the following:
1- The requirements on Azure SQL DB and how to fulfil it.
2- How to create an Azure Automation account and add a runbook to execute the copy operation.
1. Requirements for Azure SQL DB
--Step# 1
--Create login and user in the master database of the source server.
CREATE LOGIN loginname WITH PASSWORD = 'xxxxxxxxx'
GO
CREATE USER [loginname] FOR LOGIN [loginname] WITH DEFAULT_SCHEMA=[dbo];
GO
ALTER ROLE dbmanager ADD MEMBER loginname;
GO
--Step# 2
--Create the user in the source database and grant dbowner permission to the database.
CREATE USER [loginname] FOR LOGIN [loginname] WITH DEFAULT_SCHEMA=[dbo];
GO
ALTER ROLE db_owner ADD MEMBER loginname;
GO
--Step# 3
--Capture the SID of the user "loginname" from master database
SELECT [sid] FROM sysusers WHERE [name] = 'loginname';
--Step# 4
--Connect to Destination server.
--Create login and user in the master database, same as of the source server.
CREATE LOGIN loginname WITH PASSWORD = 'xxxxxxxxx', SID = [SID of loginname login on source server];
GO
CREATE USER [loginname] FOR LOGIN [loginname] WITH DEFAULT_SCHEMA=[dbo];
GO
ALTER ROLE dbmanager ADD MEMBER loginname;
GO
From the above script we have created a same login on the source and target server that we will use on the Automation account to execute the copy.
2. Create Azure automation account and add runbook
$Cred = Get-AutomationPSCredential -Name "name_of_created_credintial"
$Server_Name = Get-AutomationVariable -Name "name_of_created_variable "
$Query = "CREATE DATABASE copy13 AS COPY OF [source_server_name].[source_DB_name];"
invoke-sqlcmd -ServerInstance "$Server_Name" -Credential $Cred - Query "$Query" -Encrypt
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.