SQL Azure VM Backup Autoprotect won't enable and not all DBs listed after rediscover is run

Question

We just setup a Windows 2019 server with sql server 2017 and the Azure backup for SQL in VM. This worked great upon initial testing, it found all the DBs and I was able to get them backed up with AUTOPROTECT enabled on it and they were all listed under the discovered DBs listing under the instance.

The DBAdmin then decided to upgrade the SQL server to 2019 and added a new database to it. This happened yesterday after noon. I checked it this morning and the new DB had not been discovered and backed up. I had to do a manual rediscover to get it to show up, but after doing that all the other DBs that were previously listed no longer show under the SQL instance where it shows the discovered databases (See screen shot below). They are still being backed up when I look at the listing of backed up items in the vault created for the backup.

Also in my trouble shooting I disabled AUTOPROTECt on the SQL instance and then went to re-enable it but when I turn AUTOPROTECT to ON and click OK it doesn't set it. Every time I go back in to check the status of AUTOPROTECT and the listing of DB's that were discovered it says AUTOPROTECT is off and again not all the DBs are listed on the discovered results.

Any idea why the DBs are no longer listed and why I can't turn on AUTOPROTECT? I had turned it off before and turned it back on without problem.

Answer 1

Hello @Brian Greenfield - Administrative ,

Thank you for your post! I was looking into this issue along with our Azure Backup Team, and based on your description, we were wondering how you upgraded the SQL Server version on the Azure VM itself and if you created a new instance? This will help clarify things further from our end.

However, based on what you have already described and how the discovery went very differently than expected, the Recovery Services Vault is seeing the newer version as an entirely different entity than the previous SQL Server version that was previously registered than the previous SQL Server version that was previously registered. In this case, if the name was changed, the RSV has no way of tracking or following that from Azure's perspective and thus is treating it as an entirely new entry in its list. As a result, it appears that when you upgrade the SQL Server, but the other databases retained its version. This created a new instance and thus created a database under the new version and that creates a new entry all together in the RSV as a result.

Please let us know if this helps point you in the right direction and we are happy to assist you further with any questions or concerns.

Thanks!
Carlos V.