Create a copy of an Azure SQL DB

Where I work, we will be migrating data from one set of databases into another. I will be making a copy of the destination databases to allow us to roll back in case of issues.

Make a copy of the databases in the destination server

It’s as simple as this for each db you want a copy of. Just run it from the master db. This works if you want to make a copy on the same server. If you want to make a copy from another server, you would have to connect via PowerShell.

CREATE DATABASE [db1copy]
AS COPY OF [db1];

CREATE DATABASE [db2copy]
AS COPY OF [db2];

You can monitor the copy of the db. It takes a little bit to show up and then will be in a state of COPYING until it’s ONLINE.

SELECT 
    name,
    state_desc
FROM 
    sys.databases; 

After they are ONLINE, I will connect to the copies and make sure I can do a simple query.

Important Considerations

• Billing: Both the source and the new database will be billed according to their respective pricing tiers.
• Performance Impact: The copy operation might have an impact on the performance of the source database.
  

Rolling back to a copy

Since there is a possibility I may have to roll back to this copy as part of the data migration project, I will show you how you can revert to the copy.

First, we need to rename the current db. Also, run in master db.

Note: you may need to kill sessions connected to the user db before you can rename it. In SQL Server, you could do ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE, but you can’t do that in Azure SQL DB. You may have to kill the connections with a query but not kill your own connection. Something like this should work, but always test in a non prod environment before using in prod!

DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(5), c.session_id) + ';'
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE c.session_id <> @@SPID;

I didn’t have to kill sessions, but if you have a failover group, you will need to pull the db out of the FG and remove the database from the secondary before you can rename the primary db.

So back to renaming our db.

ALTER DATABASE [db1] MODIFY NAME = [db1old]; 

Then, we can rename the copy to the name of the original db.

ALTER DATABASE [db1copy] MODIFY NAME = [db1]; 

I would script out all the databases this way in advance, just in case I have to roll back to the copy. Hopefully, I won’t have to.

I will also rename the source databases to ensure that nothing hits those databases during testing after migrating the data.

Here’s my process:

Well, even if we are done on the day of migration, I am not entirely done, as I will leave the copies of the destination databases and the source databases in place for at least a couple of weeks, just in case any issues pop up. I will want to loop back around to clean that up once we are sure they aren’t needed anymore.

Dropping the databases is as easy as this.

DROP DATABASE [db1copy]; 

And if we have to roll back and try it again another time, I have all my scripts ready for that again.