Background:
To say that everything in the database space comes easy is a bit of an overstatement. That is what happens when you are asked to complete many routine tasks in Azure, what once was simple on premise is now a learning curve all over again.
Here I am going to discuss a simple restore request or database copy request. Typically this request is made when developers or the business need fresh data to run test scripts or UAT activity on, they want the last backup or current production data restored to test. Seems like a fair request, done this 100+ times on premise…
Setup and Code:
Like almost anything in Azure, whatever you do that brings the data down to your local PC and then pushes it back into Azure will cost money. This is the case when you run a simple query from management studio, or in this case if we were to create a file that we had to transfer to the test server such as a database BACPAC file for restore. Keeping this in mind we are looking for an Azure-to-Azure approach to the problem. In this case it should also be a lot faster to restore.
1. Make sure you have appropriate permissions both on the instance you are copying from and the instance you are copying to.
To create a database a login must be one of the following:
The server-level principal login
The Azure AD administrator for the local Azure SQL Server
A login that is a member of the dbmanager database role.
2. The copy command assumes the database does not exist on the target instance so either drop it or use a new name for this restore attempt.
3. On your target server (test server or server you want the new database copied to) Â issue the following command in Management studio:
CREATE DATABASE [test_server_name].[new_database_name] AS COPY OF [production_server_name].[database_name]
4. Once the command has completed ( this will take 1-2 minutes depending on your service level and server activity you can monitor the restore request by using the following dmv
sys.dm_database_copies (Azure SQL Database)
Resolution:
1. Once the copy request drops from the view you should be able to access the database on your test /target server. Note, once your initial command completes the database will show in the navigation pane on the target server but the database will not be expandable until the entire restore/copy is completed.
2. Expand the database and note, just like a restore for an on premise instance, you now have all the data, schema, security that came from the database you copied ( potentially production) so now you need to remove logins from the database, reassign roles to new logins in the case of test / dev environments.
Information on Terms:
PaaS – Platform as a Service – category of cloud computing where an application runs on hosted hardware.
Azure SQL Database – Microsoft’s implementation of a cloud SQL Server Database which runs on a PaaS instance.
Subject Links:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-azure-sql-database
https://www.mssqltips.com/sqlservertip/2235/creating-backups-and-copies-of-your-sql-azure-databases/