How to copy an Azure SQL Database to a different server or subscription using Azure Automation

This article is contributed. See the original author and article here.

In this article we will demonstrate how to copy an Azure SQL DB to a different server or subscription using Azure Automation.

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

• Use a login that has the same name and password as the database owner of the source database on the source server. The login on the target server must also be a member of the dbmanager role, or be the server administrator login.


• The below steps can be used to create the login on the source and the target server.

 

 

--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

1. Create an Automation account: From the Azure Portal go to Automation account and create a new account. If you already have an Automation account you can skip this step and use your existing Automation account instead.
   
    
   
   

    

   
   
   

    

   
   


2. Import SQLServer module: Go to the modules on your Automation account, click on modules gallery. From the search option type sqlserver and select this module. The next screen allows you to import the module.                                                                                   
   

    

   
                                                                                                                                                                   


3. Create Credential to access Azure SQL DB: Now we need to create a credential that we use to allow the automation account able to connect to the target SQL server and execute the copy operation. This credential must be the login that we created on using the above steps that have access to the source and destination servers .From the Automation account click on credential and provide a name for the credential, password and confirm the password.                                                                         
   

    

   
    
   

    

   
                                                                                                                                                              


4. Create Variables for the Azure SQL DB server name: Create a variable that we will use to connect to the target SQL server, as the copy operation should be done from the target server. The value for this variable should be the FQDN of the target server (servername.database.windows.net):                                                                                         
   

    

   
                                                                                                                                                            


5. Create the run book: Click on the runbook from the Automation account. On the create runbook screen you have to type the name for this runbook and choose PowerShell as the runbook type.                                                                                                                             
   

    

   
                                                                                                                                                              


6. When the creation is completed, click on the created runbook, click on the edit and enter the below sample code (you can use the test pane to test the runbook and click save to save it).                                                                                                                                                                                                                                                                                       

    $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
   ​

                                                                                                                                                             



7. If you want to run this runbook on a schedule, you need to create a schedule and link this runbook to the created schedule.

 

 

 

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.