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:

• MsDeploy RC1 x86
• MsDeploy RC2 x64

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:

<sitemanifest>

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

</sitemanifest>

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:

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
ge=C:\Users\vijoshi\Desktop\DBPackage.zip

The output of this command should show something like below:

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:

<sitemanifest>

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

</sitemanifest>

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
t=C:\Users\vijoshi\Desktop\DBDestinationManifest.xml

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

Hope this helps!!

-Vishal

Technorati Tags: DB,Deployment,Packaging,Transfer,SQL,Scripting,SMO,MSDeploy,SQL Server