Migrate Azure SQL Database From Prod To Test

Question

Hello everyone, I want to copy the Production instance of our Azure SQL Database to an already existing Test environment (basically overwrite after backup).

What would be the recommended way to achieve this?

Thank you very much.

---

Sohi

Answer 1

You can do this using SQL Server Management Studio.

Once you connect to your database there are several ways you can deploy to Azure.

Right-click on the database, click on Tasks, and select Deploy Database to Microsoft Azure SQL Database.

---

william xifaras

Answer 2

Hello,

Please provide basic details. are you working on PASS or IAAS model?

PAAS:

I would recommend create "bacpac" file and import into Test environment. Please follow below link.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-export

IAAS Model: You can directly take database backup and restore on Test environment.

Answer 3

Hi,

You can simply create a copy of the database directly using simple query:

CREATE DATABASE Database2 AS COPY OF Database1;

* Drop your old test if you need to replace it with the new and create the new from copy using the above command

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]

Answer 4

Thank you, this seems to be the most straightforward approach.

One question though, does the above statement create a transactionally consistent copy of the database? Would it work if there is an open transaction at the time of running this statement?

---

Sohi

Answer 5

You are most welcome :-) 

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]