How to sync daily the prod database to dev database in Azure SQL Server service?

by MoonHorse   Last Updated June 29, 2020 23:06 PM - source

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?



Answers 3


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)

Aleksey Vitsko
Aleksey Vitsko
February 19, 2020 14:23 PM

The Copy Database function could be an option for you:

https://docs.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.

brad
brad
February 24, 2020 00:47 AM

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.

Ivan Gusev
Ivan Gusev
February 24, 2020 02:31 AM

Related Questions





Add Active Directory User For Azure-SQL-DB

Updated February 25, 2017 13:06 PM