CODEWITHSUNDEEP
sqlsql-serverazure
Abhijeet
Abhijeet

Reputation: 13856

Copy SQL Azure Database to different Server

SQL Azure database supports copying whole database asynchronously with a single command as below:

I have been using to copy database within same server using

CREATE DATABASE [targetdb] AS COPY OF [sourcedb]

But when I try to copy database to a different SQL Azure server:

CREATE DATABASE [targetdb] AS COPY OF [source_sql_azure_server].[sourcedb]

But I get below error:

Cannot open server "source_sql_azure_server" requested by the login. The login failed.

How do I copy?

Upvotes: 10

Views: 19514

Answers (4)

zillabunny
zillabunny

Reputation: 335

This should solve your 5 year old problem! :) 

   $dblist = @("db1","db2","db3")
   $sourceserver = "azureDBserversource"
   $targetserver = "azureDBserverTarget"
   $sourceRG = "mySourceRG"
   $targetRG = "myTargetRG"

    foreach ($db in $dblist) {
            New-AzSqlDatabaseCopy -ResourceGroupName $sourceRG `
                -ServerName $sourceserver `
                -DatabaseName $db `
                -CopyResourceGroupName  $targetRG `
                -CopyServerName $targetserver `
                -CopyDatabaseName $db
            }

Upvotes: 0

Faces Of IT
Faces Of IT

Reputation: 81

I guess there is also another way to achieve another copy in a different region. You can simply do an Asynchronous Replication to a different server and region and make that the primary.

Upvotes: 0

Adam Goodwin
Adam Goodwin

Reputation: 4071

Although this question is very old, it's the one I came across when trying to find out if I could easily copy an Azure SQL database from one server to another.

It turns out that it's now as simple as navigating to the source database from http://portal.azure.com then clicking copy and choosing the new destination server.

The whole process is explained in more detail in this article: https://azure.microsoft.com/en-us/documentation/articles/sql-database-copy/

Upvotes: 19

Raf
Raf

Reputation: 10097

To execute DB copy between 2 different servers you must be connected to the master database of the destination SQL Azure server and have correct permissions.
The exact same login/password must exist on the source server and destination server and the login must have db_owner permissions on the source server and dbmanager on the destination server.

Read more about it here: http://msdn.microsoft.com/en-us/library/ff951624.aspx

Upvotes: 4

Related Questions