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:

    1. Generate dacpac from the root database if you don't have already a dacpac as update.
    2. 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.
    3. 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:"\root_database_name.dacpac"
    

    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:

    select
    '\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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2