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;

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.

The post Create a copy of an Azure SQL DB appeared first on sqlkitty.