Generate an executable SQL script from an SQL Server database project

Question

I have a SQL Server database project that can deploy the schema to a database, it creates a .dacpac and applies it to a database.

I would like to use the SQL file generated in an automated test class that will create all the tables and objects to an empty database.

I can't use the generated SQL file because it contains unrecognized commands like

:setvar DatabaseName "MyDB"
:setvar DefaultFilePrefix "MyDB"

How can I generate an executable SQL script from a SQL Server database project deployment?

Maybe this? http://stackoverflow.com/questions/26617247/serverconnection-executenonquery-in-sqlcmd-mode — xLiSPirit, Feb 16 '16 at 01:20
Possibilities for getting the generated publish SQL script to work programmaticaly: C# via Process.Start to call SQLCMD.EXE -i filename.sql: http://msdn.microsoft.com/en-us/library/system.diagnostics.process.start(v=vs.110).aspx C# via open source library to handle "GO" statements and SQLCMD-mode colon-commands (currently handles SQL files, but can be easily updated to accept a string instead): https://github.com/rusanu/DbUtilSqlCmd PowerShell via Invoke-SqlCMD: http://www.sqlservercentral.com/Forums/Topic1502697-1351-1.aspx — xLiSPirit, Feb 16 '16 at 01:23

score 1 · Accepted Answer · edited May 23 '17 at 12:23

Thanks to xLiSPirit and this SO post, I ended up making it work with the DacServices class and this piece of code :

    private void UpgradeDatabaseWithDacpac(string connectionString, string databaseName, string dacpacFileName)
    {
        DacPackage dacPackage = DacPackage.Load(dacpacFileName);
        DacServices dacServices = new DacServices(connectionString);
        dacServices.Deploy(dacPackage, databaseName, true);
    }

Generate an executable SQL script from an SQL Server database project

1 Answers1