Tuesday, March 24, 2009

How to transfer your DB using MSDeploy command Line

Problem Statement: Lets say you have a database on a server which is live, and you think that there is some problem on the server that you would like to troubleshoot…  You typically want to move your database to your Dev DB box and troubleshoot it there instead of troubleshooting a live server…

MSDeploy Solution:  MSDeploy can help you achieve this in very simple steps below:

Learn more about how MsDeploy works

Step 1: Download MSDeploy and install it on source as well as target machines

At MIX 2009 we released MSDeploy RC and it is available for free download below:

You can just download the exe and install standard installation, that should work…

Step 2:  Create a source manifest for your source machine DB

Source manifest should look as below:


<dbFullSql path=" Data Source=VIJOSHI-DEMO;Initial Catalog=Delete;Integrated Security=True" ScriptSchema=True ScriptData=False />


You can save this xml at any location you like… In my case I will save it at C:\Users\vijoshi\Desktop\DBManifest.xml

The highlighted syntax are scripting options provided by SQL Management Objects… There are more scripting options like TargetServerVersion  which indicates the destination SQL Server version for which you want to generate SQL Scripts…

ScriptSchema option above will allow you to take the schema of the database (typically you will want this most of the time)

ScriptData option will allow you to take the data from the DB as well, in our problem statement above you do need the data as well but normally this is a very resource costly option and also might be restricted due to confidentiality policies around data.

Step 3: Open MSDeploy command prompt

Start –> All Programs –> IIS Extensions –> MSDeploy Command Console

check the figure below:

msdeploy command console

Step 4:  Run package command to create a .zip file which will contain your .sql scripts

Call MSDeploy command with below details:

  • verb:sync – This instructs msdeploy to create an action of syncing and as we want to sync our DB that is the verb we will choose…
  • source:manifest – This will tell msdeploy what to use as a source…  In our case we have created a manifest file in Step 2 which is our source (note that for MSDeploy the xml is the source, which inturn calls db as the source)
  • dest:Package -   This will tell msdeploy to create a package of the source.  In our case the DB will be scripted into .sql file and will be put into .zip package.

The command that you will use on command line will be as below:

C:\Program Files\IIS\Microsoft Web Deploy>msdeploy -verb:sync -source:manifest=C:\Users\vijoshi\Desktop\DBManifest.xml -dest:Packa

The output of this command should show something like below:

db packaging commandline

Step 5:  Transport your .zip package to the destination

Now you can simply copy the .zip file to your target machine and if you just need the .sql file to use SQL Management studio to install it then just open the .zip file in windows explorer and you should have the .sql file in there… 

Alternatively if you want to use MSDeploy itself to recreate the DB on the destination then it is equally easy as well…

Step 6: Run MSdeploy command to unzip the packaged DB

This time the zip file is the source and we want to put the DB into different DB, I will call it “TargetDB”…

For that I will create destination manifest similar to the source manifest I created in Step 2… My destination manifest will look as below:


<dbFullSql path=" Data Source=VIJOSHI-DEMO;Initial Catalog=TargetDB;Integrated Security=True"/>


Few things to note:

  • My connection string has to change on the destination to match the destination credentials
  • I do not need SMO options now as I am no more scripting anything rather I am just deploying the already scripted objects
  • I will save this file as C:\Users\vijoshi\Desktop\DBDestinationManifest.xml

Again on MSDeploy command prompt I can provide below MSDeploy command

C:\Program Files\IIS\Microsoft Web Deploy>msdeploy -verb:sync -source:package=C:\Users\vijoshi\desktop\DBPackage.zip -dest:manifes

After running the command you should have your DB updated on your local box as below:

transferred DB

Hope this helps!!


Monday, March 23, 2009

Web Deployment: Web.Config Transformation

We have earlier discussed about Web Deployment and Web Packaging quite a bit, today I wanted to dive into web.config transformation. If you would like to check out the other topics please read through the earlier blog posts below:

Usually web applications go through a chain of server deployments before being finally being deployed to production environment. Some of these environments can be Developer box (Debug), QA Server, Staging/Pre-Production, Production (Release). While transitioning between these environments various settings of the web application residing in web.config file change, some of these settings can be items like application settings, connection strings, debug flags, web services end points etc.

VS10’s new web.config transformation model allows you to modify your web.config file in an automated fashion during deployment of your applications to various server environments. To help command line based deployments, Web.Config transformation is implemented as an MSBuild task behind the scene hence you can simply call it even outside of deployment realm.

I will try to go through below steps to explain web.config transformation in detail

  1. Creating a “Staging” Configuration on your developer box

  2. Adding a “Staging” Web.Config Transform file to your project

  3. Writing simple transforms to change developer box connection string settings into “Staging” environment settings

  4. Generating a new transformed web.config file for “Staging” environment from command line

  5. Generating a new transformed web.config file for “Staging” environment from VS UI

  6. Understanding various available web.config Transforms and Locators

  7. Using Web.config transformation toolset for config files in sub-folders within the project

Step 1: Creating a “Staging” Configuration on your developer box

Debug and Release build configurations are available by default within Visual Studio but if you would like to add more build configurations (for various server environments like “Dev”, “QA”, “Staging”, “Production” etc then you can do so by going to the Project menu Build --> Configuration Manager… Learn more about creating build configurations.

Step 2: Adding a “Staging” Web.Config Transform file to your project

One of the goals while designing web.config transformation was to make sure that the original runtime web.config file does not need to be modified to ensure that there would be no performance impacts and also to make sure that the design time syntax is not mixed with runtime syntax. To support this goal the concept of Configuration specific web.config files was introduced.

These web.config files follow a naming convention of web.configuration.config. For example the web.config files for various Visual Studio + Custom configurations will look as below:

web.config transform

Any new Web Application Project (WAP) created in VS10 will by default have Web.Debug.Config and Web.Release.config files added to the project. If you add new configurations (e.g. “Staging”) or if you upgrade pre-VS10 projects to VS10 then you will have to issue a command to VS to generate the Configuration specific Transform files as needed.

To add configuration specific transform file (e.g. Web.Staging.Config) you can right click the original web.config file and click the context menu command “Add Config Transforms” as shown below:

Add Config Transforms

On clicking the “Add Config Transform” command VS10 will detect the configurations that do not have a transform associated with them and will automatically create the missing transform files. It will not overwrite an existing transform file. If you do not want a particular configuration transform file then you can feel free to delete it off.

Note: In case of VB Web Application Projects the web.configuration.config transform files will not be visible till you enable the hidden file views as shown below:

VB.net web.config Transform

The transform files are design time files only and will not be deployed or packaged by VS10. If you are going to xCopy deploy your web application it is advised that you should explicitly leave out these files from deployment just like you do with project (.csproj/.vbproj) or user (.user) files…

Note: These transform files should not be harmful even if deployed as runtime does not use them in any fashion and additionally ASP.NET makes sure that .config files are not browsable in any way.

Step 3: Writing simple transforms to change developer box connection string settings into “Staging” environment settings

Web.Config Transformation Engine is a simple XML Transformation Engine which takes a source file (your project’s original web.config file) and a transform file (e.g. web.staging.config) and produces an output file (web.config ready for staging environment).

The Transform file (e.g. web.staging.config ) needs to have XML Document Transform namespace registered at the root node as shown below:

<?xml version="1.0"?>
<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">

Note: The transform web.config file needs to be a well formed XML.

Inside the XML-Document-Transform namespace two new attributes are defined. These attributes are important to understand as they drive the XML Transformation Engine.

Transform – This attribute inside the Web.Staging.config informs the Transformation engine the way to modify web.config file for specific configuration (i.e. staging). Some examples of what Transforms can do are:

  • Replacing a node

  • Inserting a node

  • Delete a node

  • Removing Attributes

  • Setting Attributes

Locator – This attribute inside the web.staging.config helps the Transformation engine to exactly pin-point the web.config node that the transform from web.staging.config should be applied to. Some examples of what Locators can do are:

  • Match on value of a node’s attribute

  • Exact XPath of where to find a node

  • A condition match to find a node

Based on the above basic understanding let us try to transform connection string from original web.config file to match Staging environment’s connection string

Let us examine the original web.config file and identify the items to replace... Original Web Config file’s connection string section looks as below:

<?xml version="1.0" encoding="UTF-8"?>
    <add name="personalDB"
     connectionString="Server=DevBox; Database=personal; User Id=admin; password=P@ssw0rd" providerName="System.Data.SqlClient" />
    <add name="professionalDB"
     connectionString="Server=DevBox; Database=professional; User Id=admin; password=P@ssw0rd" providerName="System.Data.SqlClient" />

NOTE: It is not advisable to keep connection string unencrypted in the web.config file, my example is just for demonstration purposes.

Let us assume that we would like to make following changes to web.config file when moving to staging environment

  • For “personalDB” we would like to change the connectionString to reflect Server=StagingBox, UserId=admin, passoword=StagingPersonalPassword”

  • For “professionalDB” we would like to change the connectionString to reflect Server=StagingBox, UserId=professional, passoword=StagingProfessionalPassword”

To make the above change happen we will have to open web.Staging.Config file and write the below piece of code

<?xml version="1.0"?>
<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
        <add name="personalDB"
          connectionString="Server=StagingBox; Database=personal; User   

          Id=admin; password=StagingPersonalPassword"
          providerName="System.Data.SqlClient" xdt:Transform="Replace"    

          xdt:Locator="Match(name)" />
        <add name="professionalDB"
         connectionString="Server=StagingBox; Database=professional; User  

         Id=professional; password=StagingProfessionalPassword"
         providerName="System.Data.SqlClient" xdt:Transform="Replace"


The above syntax in web.staging.config has Transform and Locator attributes from the xdt namespace. If we analyze the connection string node syntax we can notice that the Transform used here is “Replace” which is instructing the Transformation Engine to Replace the entire node

Further if we notice the Locator used here is “Match” which is informing Transformation engine that among all the “configuration/connectionStrings/add” nodes that are found, pick up the node whose name attribute matches with the name attribute of <add> node in web.Staging.config.

Also if you notice web.Staging.config does not contain anything else but the connectionStrings section (i.e. it does not have <system.web> and various other sections that web.config file usually has, this is because of the fact that the Transformation Engine does not require a complete web.config file in web.staging.config. It does the merging for you thus saving you duplication of all the rest of the sections in web.config file.

Simplest Approach: If you do not mind replicating the entire web.config file in web.staging.config then you can certainly do so by copying the entire web.config content into web.staging.config and change the relevant nodes inside web.staging.config. In such a situation you will just have to put xdt:Transform="Replace" attribute on the topmost node (i.e. configuration) of web.staging.config. You will not need xdt:Locator attribute at all as you are replacing your entire web.config file with web.staging.config without Matching anything.

So far we have seen one Transform (i.e. Replace) and one Locator (i.e. Match), we will see various other Transforms and Locators further in the post but first let us understand how we can produce the Transformed web.config file for the Staging environment after using original web.config and web.staging.config.

Step 4: Generating a new transformed web.config file for “Staging” environment from command line

Open Visual Studio Command prompt by going to Start --> Program Files –> Visual Studio v10.0 –> Visual Studio tools –> Visual Studio 10.0 Command Prompt

Type “MSBuild “Path to Application project file (.csproj/.vbproj) ” /t:TransformWebConfig /p:Configuration=Staging" and hit enter as shown below:

commandline web.config transformation

Once the transformation is successful the web.config for the “Staging” configuration will be stored under obj -->Staging folder under your project root (In solution explorer you can access this folder by first un-hiding the hidden files) :

transformed web.config

  • In the solution explorer click the button to show hidden files
  • Open the Obj folder

  • Navigate to your Active configuration (in our current case it is “Staging”)

  • You can find the transformed web.config there

You can now verify that the new staging web.config file generated has the changed connection string section.

Step 5: Generating a new transformed web.config file for “Staging” environment from VS UI

Right Click on your project and click Package –> Create Package

Create Package

The Create Package step already does web.config transformation as one of its intermediate steps before creating a package and hence you should be able to find the transformed web.config file in the same place as described in Step 4

Step 6: Understanding various available web.config Transforms and Locators


The inbuilt xdt:Locators are discussed below.

  • Match - In the provided syntax sample below the Replace transform will occur only when the name Northwind matches in the list of connection strings in the source web.config.Do note that Match Locator can take multiple attributeNames as parameters e.g. Match(name, providerName) ]

     <add name="Northwind" connectionString="connectionString goes    here" providerName="System.Data.SqlClient" xdt:Transform="Replace"          xdt:Locator="Match(name)" />

·         Condition - Condition Locator will create an XPath predicate which will be appended to current element’s XPath. The resultant XPath generated in the below example is “/configuration/connectionStrings/add[@name='Northwind or @providerName=’ System.Data.SqlClient’ ]”

This XPath is then used to search for the correct node in the source web.config file

      <add name="Northwind" connectionString="connectionString goes here"

        providerName="System.Data.SqlClient" xdt:Transform="Replace"

        xdt:Locator="Condition(@name=’Northwind or @providerName=’

        System.Data.SqlClient’)" />

·         XPath- This Locator will support complicated XPath expressions to identify the source web.config nodes. In the syntax example we can see that the XPath provided will allow user to replace system.web section no matter where it is located inside the web.config (i.e. all the system.web sections under any location tag will be removed.)

<location path="c:\MySite\Admin" >
    <system.web xdt:Transform="RemoveAll" xdt:Locator="XPath(//system.web)">


  • Replace - Completely replaces the first matching element along with all of its children from the destination web.config (e.g. staging environment’s web.config file). Do note that transforms do not modify your source web.config file.
    <assemblies xdt:Transform="Replace">
        <add assembly="System.Core, Version=, Culture=neutral, PublicKeyToken=B77A5C561934E089" />

·         Remove - Removes the first matching element along with all of its children
<assemblies xdt:Transform="Remove"></assemblies>

·         RemoveAll - Removes all the matching elements from the destination’s web.config (e.g. staging environment’s web.config file).

    <add xdt:Transform="RemoveAll"/>

·         Insert - Inserts the element defined in web.staging.config at the bottom of the list of all the siblings in the destination web.config (e.g. staging environment’s web.config file).

     <deny users="*" xdt:Transform="Insert"/>

·         SetAttributes - Takes the value of the specified attributes from the web.staging.config and sets the attributes of the matching element in the destination web.config. This Transform takes a comma separated list of attributes which need to be set. If no attributes are given to SetAttributes transform then it assumes that you would like to Set all the attributes present on the corresponding node in web.staging.config
<compilation batch="false"



·         RemoveAttributes - Removes the specified attributes from the destination web.config (i.e. staging environment’s web.config file). The syntax example shows how multiple attributes can be removed.


  • InsertAfter (XPath) - Inserts the element defined in the web.staging.config exactly after the element defined by the specified XPath passed to “InsertAfter()” transform. In the syntax example the element <deny users="Vishal" />will be exactly inserted after the element <allow roles="Admins" /> in the destinationXML.

     <deny users="Vishal" xdt:Transform="InsertAfter(/configuration/system.web/authorization/allow[@roles='Admins'])” />


  • InsertBefore (XPath) - Inserts the element defined in the web.staging.config exactly before the element defined by the specified XPath passed to “InsertBefore()” transform. In the syntax example the element <allow roles="Admins" />will be exactly inserted before the element <deny users="*" />in the destinationXML.

      <allow roles=" Admins" xdt:Transform="InsertBefore(/configuration/system.web/authorization/ deny[@users='*'])" />

Some advanced points to note:

  • If the Transformation Engine does not find a xdt:Transform attribute specified on a node in web.staging.config file then that node is ignored for Transformation and the Tranformation engine moves ahead traversing the rest of the web.staging.config.

  • A xdt:Transform attribute on a parent can very easily impact child elements eve if there is no Transform specified for child e.g. If xdt:Transform=”Replace” is put on <system.web> then everything underneath <system.web> node will be replaced with the content from web.staging.config

  • It is completely valid to place xdt:Locators attributes on arbitrary nodes inside web.staging.config just for filtering purposes. xdt:Locator does not need to be accompanied with xdt:Transform attribute. (great example here is <location> tag which might just be used for filtering… The example code here would be:

<location path="c:\MySite\Admin" xdt:Locator="Match(path)">>
          ... Bunch of transforms written under here will
          .... only apply if location path = C:\MySite\Admin

Step 7: Using Web.config transformation toolset for config files in sub-folders within the project

All of the above discussion directly applies to any web.config file present in sub folders of your project (e.g. if you have a separate web.config file for say “Admin” folder then VS 10 will support transforms for them too). You can add transform files within sub-folders and use the same packaging functionality mentioned in all of the above steps to create transformed web.config files for web.config files specific to the sub folders within your project.

I think this has become a rather long post; but I hope it helps!!