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:

<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:

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

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:

<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:

transferred DB

Hope this helps!!

-Vishal

7 comments:

Kiran Kumar said...

hi i am using xp operating system and i installed vs2010 when i am using ms deploy tool for database deploy i got this error how to resolve this

error:-Cannot connect to pubs database

Cannot initialize SSPI packages

Vishal R Joshi said...

Hi Kiran,
Can you suggest the steps you are following using VS10 for deployment?
-Vishal

Thomas Coats said...

This doesn't copy database diagrams, is there an option to enable copying database diagrams?

Vishal R Joshi said...

Hi Thomas,
MsDeploy uses SQL Management Objects (SMO) behind the scenes... Unfortunately it seems like SMO does not support database diagrams at this time... I have informed the SMO team to consider this for the future releases... As soon as the supported release is available MsDeploy will be able to directly consume it...
-Vishal

Unknown said...

Thanks Vishal, this is great, no more have to ask and wait for DBA to give a temporary test environment, does it take snapshot of entire source (tables, indexes, SProcs, Replication publishers etc.,) is there a way to get the data as well and if so what is the performance hit source takes, say a huge source, does running this locks down the source server for long period of time.

Vishal R Joshi said...

Hi Kiran,
Re: Scripting Data
You can set ScriptData=True as shown above and your data should get scripted too...

Re: Locking of DB
I do not think that the DB should get locked as Web Deploy is just trying to read the DB... If you are using SQL Express then you might face problems as many times SQL MDF files do get locked...

Btw, I recommend you touch base with your DBA, my hope is that this increases your convinience and makes both you and your DBA happy...
Thanks
Vishal

Anonymous said...

I worked on my local pc with different databases, but not with the one on GoDaddy hosting.
There's a link guided me with the command line:
"msdeploy.exe -verb:sync -source:dbDacFx="your GoDaddy connection string" -dest:dbDacFx="your local connection string""
But it's been giving me the error:
"ERROR_DACFX_NEEDED_FOR_SQL_PROVIDER. The SQL provider cannot run with dacpac option because of a missing dependency. Please make sure that DacFx is installed".
So could anyone suggest me a way for transferring database between local and godaddy? Thanks