Copying your SQL Database

If you ever need to move a copy of a  SQL database in Azure across servers then here is a quick easy way.

So let’s say you need to take a copy of database called [Rack] within Subscription A that is on server ABCSQL1 and name it database [NewRack] within subscription B on server called RBARSQL1 (The SQL Servers are in totally different data centers too).

Connect to the server (destination) within subscription B – as you can see no SQL databases.

copy

Using a login which has permission on both source and destination server you need to issue the following when on the destination server (RBARSQL1) under the master database:

CREATE DATABASE [NewRack]
AS COPY OF [ABCSQL1].[Rack]

Then do not forget about your SQL logins too.

copy1

If you try a fully qualified server name it will fail because it seems to append the ‘database.windows.net’ automatically!

For example:

CREATE DATABASE [NewRack]
AS COPY OF [ABCSQL1.database.windows.net].[Rack]

Error message: The remote partner server name ‘ ABCSQL1.database.windows.net.database.windows.net‘ could not be resolved.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s