Database changes versioning

Question

my problem is that I can't find the way how to update sqlproj from command line to track database changes in repository. According to this post: "Projects not directly supported in this release", however have tried next command with no success:

msbuild PathToMyProject.sqlproj /t:SqlSchemaCompare /p:SqlScmpFilePath=SomePath/ComparationConfiguration.scmp /p:target=PathToMyProject.sqlproj /p:Deploy="true"

and can't find the way how to do this. Is it ever possible?
From the other side it looks like I can compare database schema to dacpac file(compilation output of sqlproj) to get changes that are not present on database project, however for automation of database changes tracking it looks useless because each time I got some changes I need manually open related solution, do comparison, update target database project and then checking changes to repository

Answer 1

As was said by @Steven Green at another answer there are no way currently to update database project using SSDT from command-line(hope it will be available soon), however have found another way to version database schema changes without database project(have left it for future in case any command-line method to update it would be added). Solution is based on C# program that generates database schema creation sql script using SMO. So, we just store this script in repository and update on daily basis using CI server configuration that runs check and in case any changes found - commit them into repository. Leaving these steps for someone who faced the same problem:

1. It's needed reference next dependencies(mine were located at C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies):

   • Microsoft.SqlServer.ConnectionInfo.dll
   • Microsoft.SqlServer.Management.Sdk.Sfc.dll
   • Microsoft.SqlServer.Smo.dll
   • Microsoft.SqlServer.SmoExtended.dll

2. Use next code to generate file:

   using System;
   using System.Configuration;
   using System.Data.SqlClient;
   using System.IO;
   using System.Text;
   using Microsoft.SqlServer.Management.Common;
   using Microsoft.SqlServer.Management.Smo;
   
   namespace ScriptGenerator {
       class Program {
           private static string GetTransferScript(Database database){
               var transfer = new Transfer(database) {
                                  CopyAllObjects = true,
                                  CopyAllSynonyms = true,
                                  CopyData = false,
                                  PreserveDbo = true,
                                  Options = {
                                                WithDependencies = true,
                                                DriAll = true,
                                                Triggers = true,
                                                Indexes = true,
                                                SchemaQualifyForeignKeysReferences = true,
                                                ExtendedProperties = true,
                                                IncludeDatabaseRoleMemberships = true,
                                                Permissions = true
                                            }
   
                              };
               var transferScript = new StringBuilder();
               foreach (var scriptLine in transfer.ScriptTransfer()) {
                   transferScript.AppendLine(scriptLine);
               }
               return transferScript.ToString();
           }
   
           static void Main(string[] args){
               var databaseName = "yourDataseName";
               var outputSqlFile = Path.GetFullPath($"DatabaseSchemaScript.sql");
   
               if (File.Exists(outputSqlFile))
                   File.Delete(outputSqlFile);
   
               using (var connection = new SqlConnection(ConfigurationManager.AppSettings["DatabaseConnectionString"])) {
                   var server = new Server(new ServerConnection(connection));
                   if (!server.Databases.Contains(databaseName))
                       throw new Exception($"Database '{databaseName}' does not exists on server{connection.DataSource}");
   
                   var database = server.Databases[databaseName];
                   var transferScript = GetTransferScript(database);
                   using (var sw = new StreamWriter(outputSqlFile)) {
                       sw.WriteLine(transferScript);
                   }
               }
           }
       }
   }