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?
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)
The Copy Database function could be an option for you:
From my experience, it's much quicker than the BACPAC method.
You can write a batch file that exports the database to BACPAC file and then imports into the dev database.
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.