Correct steps to re-synchronize a staging environment (SQL Server)

We are using self-hosted SonarQube 9.5 (Enterprise) and have a production environment (SonarProd) and a staging environment (SonarTest) which we use to test upgrades. The SonarQube databases are locally hosted on SQL Server 2016.

The staging environment was created several years ago and has been working well for testing upgrades. However, after many years and many projects setup/analyzed, the staging environment is considerably behind in the data it contains, and we need to synchronize it so we can more accurately test the upgrades.

We attempted to synchronize the staging environment by backing up the production database and restoring it over the staging database, essentially replacing the database. This resulted in invalidating our staging license. We obtained and applied a new license, upgraded the staging environment to version 9.6.1 and attempted to scan some projects.

The results were unexpected and seemed to be crossed between the production instance and the staging instance. When I reviewed the results of the scan in our Azure DevOps pipeline, it showed it was successful and the analysis was published to the test instance (SonarTest), but the link it showed and referenced was pointing to the production environment (SonarProd). I did confirm by logging into both environments, that the results were published in SonarTest, however, they results were not expected with considerably less issues reported than the previous scan, even though no changes were made to the source code of the project. Additionally, the Bugs/Issues do not show up on the results (see screenshot).

This leads me to the conclusion that the steps I took to copy the production database over to staging was not the correct approach.

My question is, what is the best practice method for synchronizing the production and staging instances when working with a SQL Server environment?

It sounds like you’ve taken the rigth approach (copying production to a lower environment) – and, when restoring a database to another environment, make sure you wipe the Elasticsearch index (/data/es6 of the SonarQube install directory) and restart the SonarQube service.