I have two databases in Azure SQL Database almost identical in their structure (unless one of the two is modified). I need to synchronize the data from the production database with the staging database, being able to make changes in staging without harming production unless I need to do a production restore (that's another topic). In the case that there is no solution, I want to be able to make staging equal to production when my developers need it, is there a way to modify a database with a backup of another without having to create a new database (since you would have to modify the server name in the app)

CodePudding user response：

The process I currently employ across my environments is:

1. Start with a baseline where STAGING and PROD are exactly the same. This you can achieve by restoring the latest backup(s) from your PROD environment to your STAGING environment.

2. As changes occur in DEV and need to be released to STAGING, create the appropriate release scripts to apply to STAGING.

3. When you need to refresh the data in STAGING, restore the latest backup(s) from PROD to STAGING again.

4. Optional: Run any post-refresh STAGING specific scripts that are needed. E.g. if you obfuscate any of the data, or change of the data to signify it's the STAGING environment.

5. Run any release scripts in STAGING, that haven't yet been released to PROD.

6. Over time your release scripts that were used in STAGING, should get used to release to PROD, which is what will help keep the two environments in sync.

7. Repeat steps 2, 3, and 4 at the frequency acceptable to your goals.

I have the above in a SQL Job so it's essentially a one click process, but my database is SQL Server on-prem. I also use a schema comparison tool called SQL Examiner to automate generating the release scripts I need, but not sure it's applicable to Azure SQL Database.