Upgrade all databases using one upgraded database ik one local SQL instance
Situation : MICROSOFT SQL SERVER 2019 server , for testing I use express version.
An app is linked to one local SQL instance , let name this instance ‘ Appname’ .
In this instance there is a database , let’s call it ‘root database’. And the instance contain more databases ( can be 100 , even more ) .
So each of these databases contain data , each of them gets data from the App user works with.
The data does not change if an upgrade is needed.
the ‘root database’needs to be upgraded once a while ( data stays the same but the schemes , etc .. , needs to be upgraded)
How can I use Microsoft studio manager to upgrade all other databases using upgraded ‘root database’? So they all have same changes ( schemes , .. ) ?
How to use CMD to get the same results and maybe this method will upgrade all databases faster ?
I would follow this path:
- Generate dacpac from the root database if you don't have already a dacpac as update.
- Generate a scirpt of https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage?view=sql-server-ver15 commands using that dacpac as source, having one command for each target database.
- Execute those commands.
In this way you can create a dacpac from your root database:
sqlpackage.exe /action:extract /targetservername:server_name /targetdatabasename:root_database_name /targetfile:"
then you have to apply it to each database you need. To apply a dacpac to a database you have to use this command
\SqlPackage.exe /Action:Publish /SourceFile:" \root_database_name.dacpac" /TargetDatabaseName:db_name /TargetServerName:server_name
You should have a a way to select all database you have to applay that dacpac
select name from sys.databases where name like 'mydb%' -- or another filter you have
so if you puth all together, you can do something like this:
\SqlPackage.exe /Action:Publish /SourceFile:" \root_database_name.dacpac" /TargetDatabaseName:' + name +' /TargetServerName:server_name' from sys.databases where name like 'mydb%'
In the query output you will have all the commands needed.
This is an easy option. Probably there are better... Hope to be of help.