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…

• DB Deployment with VS 2010
• Exclude App_Data during your deployment

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:

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:

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

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