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:
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
Update Contact :
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email : Fajarudinsidik@gmail.com
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email: Fajarudinsidik@gmail.com
atau Kirimkan Private messanger melalui email dengan klik tombol order dibawah ini :
