Invoke-DbaDbUpgrade - Leaves the connection pointing at the upgraded database

[andreasjordan]

Summary

Invoke-DbaDbUpgrade runs DBCC CHECKDB, DBCC UPDATEUSAGE, sp_updatestats and sp_refreshview through $db.ExecuteNonQuery(...):

• public/Invoke-DbaDbUpgrade.ps1:236 (DBCC CHECKDB ... WITH DATA_PURITY)
• public/Invoke-DbaDbUpgrade.ps1:252 (DBCC UPDATEUSAGE)
• public/Invoke-DbaDbUpgrade.ps1:269 (sp_updatestats)
• public/Invoke-DbaDbUpgrade.ps1:294 (sp_refreshview)

Those calls run a USE [database] on the caller's connection and never switch back, so when the command returns, the connection that was passed in points at the upgraded database instead of the database it was connected to. See #10555 for the mechanism.

Steps to Reproduce

The database must actually need upgrading. With compatibility level and target recovery time already matching the instance, the command skips the database entirely at public/Invoke-DbaDbUpgrade.ps1:191-197 and nothing leaks - which is why a freshly created test database shows nothing.

$server = Connect-DbaInstance -SqlInstance $instance -NonPooledConnection
$null = New-DbaDatabase -SqlInstance $server -Name dbatoolsci_upgrade
$null = Invoke-DbaQuery -SqlInstance $instance -Database master -Query "ALTER DATABASE [dbatoolsci_upgrade] SET COMPATIBILITY_LEVEL = 100"
$null = $server.ConnectionContext.ExecuteNonQuery("USE [master]")

$server.ConnectionContext.ExecuteScalar("SELECT DB_NAME()")     # master
$null = Invoke-DbaDbUpgrade -SqlInstance $server -Database dbatoolsci_upgrade -NoCheckDb
$server.ConnectionContext.ExecuteScalar("SELECT DB_NAME()")     # dbatoolsci_upgrade

Observed output on SQL Server 2022, dbatools 2.8.4 and current development:

ok      baseline (compatibility level 100)      DB_NAME()=master
LEAKED  Invoke-DbaDbUpgrade -NoCheckDb          DB_NAME()=dbatoolsci_qscontext

Real-world trigger: attaching an older database (for example the StackOverflow2010 sample at compatibility level 100 via Mount-DbaDatabase) and then running Invoke-DbaDbUpgrade on the same $server object.

Expected behaviour

The command leaves the caller's connection on the database it was connected to.

Suggested fix

DBCC CHECKDB ('name'), DBCC UPDATEUSAGE (name) and sp_refreshview take the target as an argument and do not need the database context, but sp_updatestats does. So either

• route all four statements through the fixed Database wrapper once Database-scoped SMO calls silently change the current database of the shared connection #10555 is resolved, or
• run them on a connection dbatools owns rather than on the caller's, or
• keep the database context for sp_updatestats only and restore the previous database afterwards.

Prefer whatever comes out of #10555 so all commands do it the same way.

Test

Add a regression test asserting the connection context, with a non-pooled connection and a database that is actually upgraded (set the compatibility level down first), following tests/CLAUDE.md:

$server.ConnectionContext.ExecuteScalar("SELECT DB_NAME()") | Should -Be "master"

---

This text was created by Claude and reviewed by Andreas Jordan.