Leveraging the Visual Studio 2010 Database Deployment API

I often get questions from developers wanting to modify the deployment engine’s behavior in some way.  These questions are typically motivated by the need to accommodate a specific requirement for a given environment or to support automation in some fashion.  In GDR there has not been much you could do to change the behavior of the deployment engine aside from existing deployment options and pre/post deployment scripts.  In 2010 you now have access to some really powerful APIs to leverage the database project’s build engine and database deployment engine. 



There are 3 new areas of the API now public that you can code against and influence the database deployment engine’s behavior or extend it in some fashion. 


Database Deployment Contributors

·         Plan Modifiers – Deployment Plan Modifiers allow the developer to modify the deployment plan before execution. Jamie has a good sample on MSDN which makes scripts produced by the database project re-runnable.

·         Plan Executors – Deployment Plan Executors provide the developer read only access to the deployment to process the deployment plan in some way.  Jamie also has a good sample on MSDN about how to create new artifacts to analyze a deployment plan.

Database Deployment Engine API

·         The Deployment API gives you low-level access to the deployment engine. VSDBCMD is only a shell which leverages the deployment engine and provides simplified access to the deployment engines knobs and buttons.


Here is sample code for using the deployment engine to deploy a local model on disk.  You can see how simple it is to load the model up and then walkthrough the deployment plan.  You can analyze the deployment plan and add and remove steps if you like. You can register customer loggers or even integrate it even tighter with your own deployment frameworks.


using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

using System.Diagnostics;

using System.IO;

using Microsoft.Data.Schema;

using Microsoft.Data.Schema.Sql;

using Microsoft.Data.Schema.Extensibility;

using Microsoft.Data.Schema.Build;

using Microsoft.Data.Schema.SchemaModel;

using Microsoft.Data.Schema.Sql.SchemaModel;

namespace DeployLocalModel


    class Program


        static void Main(string[] args)


            const string conxString

                = @”Server=.\SQLEXPRESS;Integrated Security=true;Pooling=false”;

            const string modelFilePath

                = @”..\..\..\LocalModel\sql\debug\LocalModel.dbschema”;

            const string targetDatabaseName = @”CarDealership”;

            ISchemaDeploymentController schemaDeploymentController = null;

            bool hasChanges = false;



            //Create a new error manager so we can capture the error messages

            ErrorManager errorManager = new ErrorManager();

            /*Create a extension manager so we can load the SQL100 DSP and

            utilize its implementation of the deployment engine*/

            ExtensionManager extensionManager =

                new ExtensionManager(typeof(Sql100DatabaseSchemaProvider).FullName);

            // Create the deployment controller

            SchemaDeploymentConstructor schemaDeploymentConstructor =



            schemaDeploymentConstructor.Setup(new FileInfo(modelFilePath), conxString);

            schemaDeploymentConstructor.Errors = errorManager;

            SchemaDeployment schemaDeployment

                = schemaDeploymentConstructor.ConstructService();

            //Set deployment controller options

            schemaDeployment.Options.TargetDatabaseName = targetDatabaseName;

            //Create a controller becase we will be working with a deployment plan

            schemaDeploymentController = schemaDeployment.CreateController();

            // Setup our event handler so we can listen to events from deployment engine


                += delegate(object sender, DeploymentContributorEventArgs deployArg)


                Console.WriteLine(“{0}”, deployArg.Message.Message);


            // Get access to the plan so we call walk through it

            DeploymentPlan deploymentPlan = schemaDeploymentController.CreatePlan();

            DeploymentStep deploymentStep = deploymentPlan.Head;

            while (deploymentStep != null)


                if (deploymentStep.Action() != “”)


                    //Send deployment step contents to output window

                    Debug.Print(“Deploy Step {0} contains:\n\r {1}”,



                    /*Test to see if we are deploying any changes,

                    if not we will short circuit later on.*/

                    if (deploymentStep is DeploymentScriptDomStep){

                        hasChanges = true;




                deploymentStep = deploymentStep.Next;



            if (hasChanges)


                //Deploy the model to the target database using the plan





                Console.WriteLine(“No deployment changes to make!”);



        catch (Exception e)






            if (schemaDeploymentController != null)




            Console.WriteLine(“Execution complete. Strike any key to exit.”);








If you want to start out with the sample already coded up you can download it from my SkyDrive.

Comments (11)

  1. khuebner says:

    So where do the following namespaces come from?  I've search both my dev pc and TFS server and cannot find them!

    using Microsoft.Data.Schema.Sql;

    using Microsoft.Data.Schema.Extensibility;

    using Microsoft.Data.Schema.Build;

    using Microsoft.Data.Schema.SchemaModel;

    using Microsoft.Data.Schema.Sql.SchemaModel;

    I really with MS wout make it EASIER for us developers instead of adding layer upon layer of complexity!!!!!!

  2. Anonymous says:

    I'm trying to use this approach to do a schema compare and generate an update script for my target DB. All seems good and I've gotten so far as to generate the script, but the script includes "ADD FILE" commands, which it really shouldn't…and that's causing the plan to throw exceptions on UpdateDatabase because the files are in use… Is there a way to suppress the ADD FILE statements from being generated?

  3. Anonymous says:

    Great script.I have a question "If I take useLegacyV2RuntimeActivationPolicy  from the app.config file

    the script won't create database.Could you please let me know how to solve this issue since I want to create a Setup class that uses this maifest to create database.



  4. Anonymous says:

    Now that the Database Projects are becoming obsolete, how do we utilize the API's for the SSDT projects?

  5. Barclay Hill says:

    SSDT also provides a public API to perform DACPAC operations.  You can read more about it here: msdn.microsoft.com/…/microsoft.sqlserver.dac.aspx

  6. Anonymous says:

    @Barclay, great article!

    Having a problem during the UpdateDatabase call when I am trying to drop a column or table.  Here is the message: "The schema update is terminating because data loss might occur.".

    I know in a database project, you can set an option to ignore this error.  I don't see an option via code on the SchemaDeployment Class.

    Can you direct me please to the right place to resolve this?

    Thanks in advance!

  7. Barclay Hill says:

    Thanks Ron. Im glad you found it useful.  Take a look at the subsequent post which describes how to load the deployment options via the manifest.  blogs.msdn.com/…/visual-studio-2010-database-deployment-api-part-2.aspx

  8. Anonymous says:

    I found this code very usefull, thank you !

    I have a problem, though : the schemaDeployment.CreateController() takes several minutes to execute, but when I compare the schema using the graphic interface, it takes only several seconds. I there any parameter to set somewhere ?

  9. Barclay Hill says:

    @ Vince is the target database the same? The delay you are noticing is probably in the connection to the target database.

  10. Anonymous says:

    Hello Barclay

    This solution assumes we have a dbschema handy

    • how do we convivially generate the dbschema with VS 2013?

    • can we bypass the use of dbschema completely, for ex by loading the schema from DB directly?