SQL Azure: Creating a Copy of a Database

Below is a simple query that you can use to create a copy of a Database quickly

This can be helpful in below scenarios: -

1. If you need to make any changes and validate.

2. Reproduce any Production issues
3. Quickly check and make some changes for validation
4. If you are unable to reproduce certain issues in Dev / SIT environment that can be reproduced in Prod.
5. If there is sensitive data that you do not want to keep it confidential, but, need to make certain changes and validate if it is reproducible.
6. If there's a code change / UI change that needs a Production like DB Pointer for validating the changes in UI.
7. Some SP / Schema changes and UI changes that would help validate if the changes are valid.
8. Quickly complete the production validation as well by pointing Dev / SIT / UAT UI to Copied database.
9. You want to scramble the sensitive data before taking a backup to share with Dev Team.
10. You want to make sure that you are not causing blocker for huge transactions occurring on the DB.

Query: -

You will have to open a new query with Master Database, (Obviously) because you cannot use the USE Command in SQL Azure DB

Right Click on the Master Database, Click New Query and run the below command.

CREATE DATABASE <<DB_COPYNAME>> AS COPY OF <<ACTUAL DATABASE>>

This will just say Command completed successfully - But, that does not mean you have a copy created already.

You will have to run the below query to check Percent_Complete is 100% to validate if Database has copied successfully to proceed with other operations.

Right Click on the Master Database, Click New Query and run the below command.

SELECT PERCENT_COMPLETE, DATABASE_ID, START_DATE, MODIFY_DATE, ERROR_CODE, ERROR_STATE FROM SYS.DM_DATABASE_COPIES

Hope it Helps !!!