search

dataplat / dbops

⚙ dbops - Powershell module that provides continuous database deployments on any scale
MIT License
155 stars 39 forks source link

[Bug] Switching database contexts causes logging error #122

Closed lowlydba closed 3 years ago

lowlydba commented 3 years ago

When running a migration script, if the script changes database context, but does not return to the original database context, the migrate function will error out from being unable to find the SchemaVersionTable:

[Install-DBOScript] Script block number: 1; Message: Invalid object name 'dbo.SchemaVersions'.\n [Install-DBOScript] System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'dbo.SchemaVersions'.

Not sure the best approach - three part naming for the logging table? Open a new connection for the logging portion? I might be able to work on a PR for this depending on what approach is deemed desirable...

nvarscar commented 3 years ago

Switching database contexts is not a supported scenario in the underlying framework - DbUp. It never supported crossing the boundaries of a single database and the SchemaVersions table cannot be addressed in a three part naming manner in its current implementation. As described here, the best solution seems to be to have independent deployment scenarios for each database, rather than switching DB contexts.

lowlydba commented 3 years ago

Ack! That is frustrating. Our use case involves making a change to the database itself, so generally switching out to a system database. I'd rather not have to keep a separate migration workflow for "master" database migrations that are actually changes for user databases.

Thanks for the quick response! I'll see if I can't come up with a workaround.

nvarscar commented 3 years ago

I'll close this issue for now, but please, share the workaround if you find one!