Full backup restore vs in-place retry after Azure DevOps Server schema upgrade failure

Answer to the Question

1. Checklist for a committed upgrade: Query TfsConfigUpgradeHistory for the most recent row and confirm its Status is Success and the UpgradeVersion matches the expected post‑upgrade value. Also verify that all objects listed in the upgrade history exist in sys.objects for the Tfs_Configuration database. If both checks pass, the transaction was committed and an in‑place retry is safe.

2. Reliability of the history entry: A Success row is a strong indicator, but it does not guarantee that every schema object was applied correctly. Missing objects or orphaned schema items can still exist because some changes occur outside the transaction. Therefore the history entry should be paired with an object‑level consistency check.

3. Conditions that make retry unsafe:

• Latest history record is Failed or missing.
• Any expected objects are absent or duplicate objects exist.
• The collection database shows a partial upgrade (e.g., missing tables/views that were created by the upgrade).
• The upgrade wizard reports a non‑transient error code (e.g., missing stored procedure, schema mismatch).

In any of these cases, the supported recovery path is a full restore of a consistent pre‑upgrade backup set for Tfs_Configuration and all collection databases.

Practical Verification Steps

1. Connect to the Tfs_Configuration database. Use SSMS or sqlcmd.

   sqlcmd -S <instance> -d Tfs_Configuration -E
   SELECT UpgradeVersion, UpgradeDate, Status
   FROM dbo.TfsConfigUpgradeHistory
   ORDER BY UpgradeDate DESC;
   

2. Check that Status equals Success and that UpgradeVersion equals the target version (e.g., 2019.2.0).
3. Run a quick object consistency query:

   SELECT name, type_desc
   FROM sys.objects
   WHERE name IN (/* list of objects from the upgrade */);
   

4. If any expected objects are missing or duplicates appear, flag the upgrade as incomplete.
5. Review the upgrade wizard’s last error message or error code. If it is a transient error (e.g., network timeout, deadlock), a retry can be attempted. If it is a schema‑related error (e.g., missing stored procedure), proceed to restore.
6. After a retry attempt, repeat steps 1‑3 to confirm a new Success record and full object consistency.

When to Restore from Backup

Follow Microsoft’s guidance for the 2019‑2022 releases: if the wizard reports a non‑transient error, or if the consistency checks above fail, restore the pre‑upgrade backup set for Tfs_Configuration and all collection databases. Perform the restore on a non‑production instance first to validate that the failure reproduces and that the same error code appears, confirming that the problem is not transient.

Missing Diagnostic Detail

To fine‑tune the recommendation, could you share the exact error code or message that was displayed by the upgrade wizard when the failure occurred? This will help confirm whether the failure was classified as transient or schema‑related.