The Problem

I was recently doing some initial testing around the migration of a client’s Azure DevOps Server 2019 to Azure DevOps Services. The basic process is

1. Upgrade to the current version of Azure DevOps Server, currently 2022.2
2. Validate the Team Project Collection (TPC) to be migrated, fixing any issues
3. Upload a backup of the TPC DB to Azure and run the migrations service.

As this was just an initial test, to get rough timings and find any major process customisation issues, I was using a ’throwaway’ VM, not the clients actual Azure DevOps Server.

They provided SQL backups of the Azure DevOps Server DBs, which restored OK, The Azure DevOps Server 2022.2 binaries installed happily, and I kicked off the Configuration Wizard to do the upgrade.

However, the validation step failed with a pair of errors:

TF400311: The database connection strings are not valid and cannot be
automatically corrected. To fix this problem, use the TFSConfig RemapDBs
command-line tool to correct the database connection strings.

TF400312: The following database(s) could not be reached: AzureDevOps_Test-TPC.
Refer to the log for more information.

The issue was that I did not have a copy of the AzureDevOps_Test-TPC database. Due to some confusion a backup of an older unused detached TPC had been provided in it’s place.

The correct step at this point, and the one I followed, was to get a copy of the missing backup, restore it and continue the upgrade.

However it did raise the interesting thought experiment, what to do in event of a disaster if you have no server, and and incomplete Azure DevOps Server backup set with a missing TPC?

The Analysis

So I did some experimentation on my test instance.

The upgrade wizard is getting its list of TPCs from the tfs_configuration database and enumerating every collection that is registered there, then trying to open each one. My restored config DB still had a registration for the Test-TPC collection pointing at a database that did not exist, so:

• TF400312 is the wizard failing to open AzureDevOps_Test-TPC.
• TF400311 is the wizard then giving up on correcting the connection strings because one of the databases it needs is unreachable.

My first idea was to use the Azure DevOps CLI tool TfsConfig collection to delete the references to the missing TPC. However, that was a dead end. The TfsConfig commands need a configured application tier, and as I was mid-upgrade I did not have one.

My second idea was to go into the configuration database in a SQL editor and follow AI suggestions. Setting the collection’s Status to 0 (offline) in dbo.tbl_ServiceHost, on the theory that an offline collection would be skipped. It was not. The upgrade servicing step does not care about the runtime status flag.

The Options

So the options, in rough order of “least bad” to “awful don’t even thing about doing it”:

1. Restore the collection database (the only reasonably practical one)

If you have any backup of the missing collection database, even an old one at the previous schema version, restore it under its original name on the same SQL instance.

Once this is restored, the reachability check will pass because the database now exists and carries the collection GUID the config DB expects.

The Azure DevOps upgrade will run, and once you have a working application tier you remove the collection properly via the Azure DevOps Admin Console or using TfsConfig collection /delete /collectionName:Test-TPC

2. Options to Deregister the collection

If there is genuinely no backup and you do not want the collection, it has to be removed from the configuration database entirely, not just in a single table.

IMPORTANT NOTE

I have not tried these, but in theory they may work, but they are all risky, so use them at your own risk

Configure an application-tier-only of the same version as the backup

The TfsConfig collection /delete command does exactly this in the right order inside a transaction, so the trick is getting to a point where you can run it.

Run an application-tier-only (AT) install of exactly the same version of Azure DevOps Server as used for the restored backups. The hope is that the validation when adding the new AT is less rigorous than that used for an upgrade

If the AT comes up, hopefully you can use TfsConfig collection /detach (or /delete) to remove the phantom collection cleanly, then upgrade to 2022.

Low level SQL edits

The removal of the TPC in theory can be done directly in the config DB via SQL queries and stored procedures. However this process is undocumented, unsupported, and has to be called in the correct dependency order or you will orphan rows and make things worse.

Due to the undocumented nature of any SQL edits, this is a “raise a Microsoft support case” process rather than something you should attempt, especially on a production box.

The Takeaway

If you take one thing away from this post, it is to back up all your Azure DevOps databases as a set on a regular basis, rather than getting into a position where you have to even consider attempting to perform SQL surgery on the TPC catalog tables.

For the original version of this post see Richard Fennell's personal blog at Missing Azure DevOps Server Team Project Collection database when doing an upgrade