Azure SQL Database Restore to different server

It is quite a common requirement to restore a copy of a database to the same Azure SQL server, you just issue a COPY OF command. What if you need to restore a copy to a different target Azure SQL server? Well its similar, just with a slight difference in that you need to refer back to the source server within your code.

To do this, log in to the master database of the target server where the new database is to be created using SSMS (SQL Server Management Studio).

— Execute on the master database of the target server to start copying from Source server.

CREATE DATABASE TargetDatabase2 AS COPY OF Source1.Database1;

Study my setup. I have 2 logical servers called spacesql and starworld. Spacesql will be my source server and I want a copy of testdb over on the target starworld. As a server admin I connect to ( db-manager role will be ok) starworld server and issue the following under master.

CREATE DATABASE testdb2 AS COPY OF spacesql.testdb;

Naturally bigger the database longer it will take, use the below query to keep an eye on it.

SELECT * FROM sys.dm_operation_status

Once complete you will see it within SSMS and it will be the same tier (standard, General Purpose etc.) as the source.

SELECT  d.name,   
     slo.*    
FROM sys.databases d   
JOIN sys.database_service_objectives slo    
ON d.database_id = slo.database_id;

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s