Tuesday, August 04, 2009

Replacing your old DB with new one using VS 2010

Earlier in the below blog posts we had talked about how to Deploy your DB using VS 2010…

Today I wanted to share how you can replace your old DB with a new one using VS 2010 DB Deployment.

Before we get into it please note that using this method you are essentially going to get rid of your old DB schema as well as data and replace it with the new one…  You should make sure that you do not turn this setting on accidentally as there will be no way to revert it back if you accidentally drop your DB content… !!

So with that understanding and with the assumption that you have already, configured your DB for deployment for use with Web Packaging OR 1-Click Publish; let us proceed:

After configuring your DB for Deployment your “Deploy SQL” Property tab should look something like below:

Deploy SQL Tab

If you look at the above UI, you will notice that there is no UI option to drop your database objects during Packaging or Publishing… This was an intentional decision as we did not want anyone to accidentally check that box and regret later… Although you can do this by manually by editing your project file…

To do that you will have to Right Click on your project and click “Unload Project” as shown below:

Unload Project

Once you unload your project you will have an option to Edit your project file as shown below:

image

Alternatively you can also open your project folder and edit the project file in notepad…

If you have your DB deployment configured in “Deploy SQL” tab (shown above) of your project properties then you should find following XML schema in the project file:

<PublishDatabaseSettings>
      <Objects>
        <ObjectGroup Name="MyDB" Order="1" Enabled="False">
          <Destination Path="" />
          <Object Type="dbFullSql">
            <PreSource Path="your connectionString value" ScriptDropsFirst=”True” ScriptSchema="True" ScriptData="False" SchemaQualify="False" CopyAllFullTextCatalogs="False" />
            <Source Path="obj\Debug\AutoScripts\MyDB_SchemaOnly.sql" />
          </Object>
        </ObjectGroup>
      </Objects>
</PublishDatabaseSettings>


If you notice carefully you should see that in the “PreSource” node above I have



additionally inserted a special attribute called ScriptDropsFirst=”True”, this attribute will tell the Web Publishing Pipeline (WPP) to insert DROP statements while scripting your DB…



In nutshell the PreSource node tells VS 2010 about your source DB and the options you would like to use while script it… ScriptDropsFirst is only one of the options but there are several others properties that you can set to control the scripting of your DB which are mentioned in SMO Scripting Options.



The option of ScriptDropsFirst will not drop your Database it will simply drop the objects inside the Database… If you would also like to drop the Database and recreate it you should use the option dropDestinationDatabase=”True”. If you are deploying to a hoster like DiscountASP or OrcsWeb you should NOT use dropDestinationDatabase option as most likely you will not have the permission to do so and even if you did the chances are that you will not have an option to recreate it and you might have to land up calling the support center…  Hopefully if you are deploying to a hosted server then you will not need to drop the DB and just dropping the schema and data inside it will be sufficient…



Anyways, once you have set these attributes  on your preSource node you can now save the project file and reload it… After that next time you Package or Publish your deployment will first drop all the tables, columns and data in your destination DB and then recreate it using the newly generated SQL script…



-Vishal

9 comments:

James said...

Hello Vishal:

I follow the steps above and I encountered a problem.
Please see the error message at URL http://user10264.netfx4lab.discountasp.net/Toalu/errors/ProjectFile.htm

Vishal R Joshi said...

Hi James, those are schema validation errors in MSBuild project file, I do not think all the schema validation is in there hence you might be getting them...
I do not think you should have any problem even if you get those squiggles... Can you confirm whether the functionality works after you save and publish... Thanks - Vishal

James said...

Hello Vishal:

I am still unable to deploy the database update.
Please see the error message at URL http://user10264.netfx4lab.discountasp.net/Toalu/errors/Publishing.htm
FYI, I was able to upload my database when I published it for the very first time.
After that, I was unable to publish any database modification.
Does the following error message mean something?
"The server experienced an issue processing the request. Contact the server administrator for more information."

=James=

Vishal R Joshi said...

James are you able to deploy without including the Database? (i.e. by going to Deploy SQL tab and unchecking your DB deployment?)...
Thanks
Vishal

James said...

Hello Vishal:

Interestingly I am able to publish the non-database files with the DB deployment checked.
Only the database fails.

=James=

Vishal R Joshi said...

Hi James, I am willing to jump on a phone call with you to get to the root of the problem... Would you be interested? If so please send me an email and we can hook up..
-Vishal

James said...

Yes, we can solve the problem over the phone.
What is the phone number to call?
You are in the Pacific Time Zone, correct?

Vishal R Joshi said...

Yes I am in PST time zone, number to reach out is 425-705-2031...
-Vishal

James said...

Hello Vishal:

I just called you, but you were unavailable.
Probably we need to schedule an appointment which you and I are available?
I will be watching the Microsoft Visual Studio 2010 Training Videos at URL https://training.partner.microsoft.com/learning/app/management/LMS_LearnerHome.aspx

=James=