Nice.
But how to update real database ?
From Visual Studio you can use "schema compare" tool which will graphically show you differences and then is able to generate SQL update script. Of course sometime it is not enough, because you may want to perform some data transformation , or add an column in the middle in example, but for simple scenarios it is good enough.
But you can have multiple database projects, like per schema, and schema compare "scenario" is not very comfortable then.
So little automation is needed.
Database project "end product" is not an dll or exe but dacpac file, and there is command line utility which can use dacpac file to update real database.
sqlpackage.exe is offered by Microsoft, and can create SQL script, or bacpac, or report,or can publish dacpac file.
"Publish" mean that actual DBis compared with definition from dacpac, like "Schema compare" from Visual Studio, the SQL update script is prepared and started.
Example:
sqlpackage.exe /Action:Publish /SourceFile:"myDB.dacpac" /TargetDatabaseName:MyDB /TargetServerName:MyDBServer /TargetUser:MyUser /TargetPassword:MyUserPassword
When you have multiple DB projects per schema then there is problem that normal schema compare will also notice that in real DB there are objects which are not present in dacpac and therefore will try to drop them in SQL update script. Therefore we should add to parameters /p:DropObjectsNotInSource=false.
Also will be nice to have one command to update database in once.
My solution:
1. Collect all dacpacfiles in one folder.
Just create and common folder and add to all projects in post build event something like:
copy $(ProjectDir)bin\release $(SolutionDir)Setup\DB /Y
exit 0
- exit 0 is required because copy will endwith exit 1 which will cause build failed error for msbuild
2. create bat or ps which will start Sqlpackage.exe for all dacpac files
like:
for %%f in (*.dacpac) do (
sqlpackage.exe /Action:Publish /SourceFile:"%%~nf.dacpac" /p:DropObjectsNotInSource=false /TargetDatabaseName:%2 /TargetServerName:%1 /TargetUser:%3 /TargetPassword:%4
)
Thanks for you attention !
