0

I have two Azure SQL Database, prod and dev.

How can i daily replicate the prod database into dev database?
I have tried to automate it by exporting and importing the BACPAC file but it takes too long time.

There is Data Sync service in Azure but I am not sure if it works in one direction.
Do you have any suggestions?

4 Answers 4

1

The Copy Database function could be an option for you:

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-transact-sql?view=azuresqldb-current#creating-a-copy

From my experience, it's much quicker than the BACPAC method.

1

You can try "Geo-replication"

Go to your prod "SQL Database", Settings > Geo-replication

Configure your dev server/database to be readable "secondary"
All changes made at prod database , will be applied to dev database right away

Beware that you won't be able to change (insert,update,delete,create,etc) data at dev database while it is in the "readable secondary" role - only read (select)

2
  • as this is dev db, it should be able to be modifiable in order to make tests. But i don't want it these changes take effect on the prod. Here is my intention is to use most recent data in my dev environment.
    – MoonHorse
    Feb 19, 2020 at 15:01
  • if you want dev to be modifiable, then you can try using geo-replication for initial copying of data prod->dev (daily), and after it completes synchronizing (copying data prod->dev), disable/turn off geo-replication. This way dev has all latest data and is same as prod, and dev becomes modifiable right after you turn off geo-replication... and these tasks should be possible to automate using t-sql or powershell Feb 19, 2020 at 15:13
0

You can write a batch file that exports the database to BACPAC file and then imports into the dev database.

You will need sqlpackage.exe utility and sqlcmd.exe utility

The script is simple:

rem drop dev database 
sqlcmd -S myserver.database.windows.net -U username -P password -Q "DROP DATABASE [dev_database]"

rem Export production azure sql database to BACPAC file
sqlpackage.exe /TargetFile:"w:\temp\dev_db.bacpac" /Action:Export /SourceServerName:"myserver.database.windows.net" /SourceDatabaseName:"prod_database" /su:username /sp:password

rem Import bacpac file to dev database 
sqlpackage.exe /SourceFile:"w:\temp\dev_db.bacpac" /Action:Import /TargetServerName:"myserver.database.windows.net" /TargetDatabaseName:"dev_database" /tu:username /tp:password

rem change edition for dev database 
sqlcmd -S myserver.database.windows.net -U username -P password -Q "ALTER DATABASE [dev_database] MODIFY (EDITION='Basic');"

-

You can schedule this script in windows scheduler in your development computer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.