#db deployment

[vishalrjoshi]

Overview Post for Web Deployment in VS 2010

In past few months I and my team have written several blog posts on VS 2010 Web Deployment features… In VS 2010 there was a lot of investment made in Web Deployment and over next few months we will be writing more on the subject…

Below is the list of all the posts for easy reference:

Overview of VS 2010 Web Deployment

Overview of Web Deployment with VS 2010 and IIS

Web Deployment Painkillers: VS 2010 & Web Deploy

Web Deployment Demos, Slides & Videos for reuse

Hanselminutes on 9 Video on Web Deployment

Web Deployment Made Awesome: Hanselman’s MIX 2010 talk

Web Deployment Overview on ScottGu's Blog

Team Build walkthrough on using Web Deploy

MSDN Documentation root for Web Deployment

Web Packaging (Put your web into a .zip file)

10 + 20 Reasons why should you create Web Packages

Web Packaging: Creating a Web Package using VS 2010

Web Packaging: Creating web packages using MSBuild

Creating a Web Package using IIS Manager

How does Web Deployment with VS 10 & Web Deploy Work?

Installing Web Packages using Command Line

Importing Web Package into IIS manager

Web Packaging & Installation using Team Build

Deploying to Devbox or 3rd party host IIS

Web.Config Transformation (Change Web.Config files per deployment environment)

Web.Config Transformation

Basic Web.Config Transformation for Publishing

VS 2010 Snippets for Web.Config Transformations

Web.Config Transforms (XDTs) for any XML files in your web projects

Applying XDT Magic to App.Config Files

Web Deploy Parameterization vs Web.Config Transformation

Web Deploy Parameterization in Action

Use Web.Config Transformation independent of Package/Publish

Extending Web.Config Transformation by Sayed Hashimi

Config Transformation outside of Web Applications

Database Deployment

DB Deployment with Visual Studio 2010

Replacing your old DB with new one using VS 2010

SQL Script Options for DB Deployment in VS 2010 

Exclude App_Data during your deployment

Deploy SQL Server Express (MDF) DBs to full SQL Server

Delete existing DB Objects on Server before deployment

Using SQL Management Objects (SMO) Options for DB Deployments

Extending Web Publishing Pipeline (WPP) to package SQL files generated by TS Data Projects

How to use VS Database Projects (TS Data/Data Dude) with VS 2010 Web Deployment

Publishing Databases Incrementally

Web Publishing (Push you web from one machine to another)

Web One Click Publish

Server side setup of Web Deployment Tool (MsDeploy)

How to ensure place holder folders are created during web deployment

Free Web Hosting Announcement for VS 2010 Beta1

Free Web Hosting Announcement for VS 2010 RC with DiscountASP

Free Web Hosting Announcement for VS 2010 RC with OrcsWeb

Deploying a Web Application using VS 2010

One-Click Publish - What's New Since Beta 2 

List of common Web Deploy errors to hosting provider

Publishing using Web Deploy on ScottGu’s blog

Setting folder permission on Web Publishing

Get VS 2010 Web Publishing updates from June 2012

Tips & Tricks for Web Deployment

Converting Web Site Projects to Web Application Projects

How to package or publish GAC Assemblies

How to package or publish Registry Entries

How to package or publish COM Components 

VS 2010 Beta2 Read Me Items for Web Deployment

Web Deployment Projects (WDP) Beta for VS 2010

Excluding Files and Folders from Deployment

Migrating IIS6 to IIS7 using Web Deploy & Link Extensions

Deploying to a Web Farm using Microsoft Web Farm Framework

Excluding files/folder based on Configuration

Including extra files/folder while deploying

Installing Web Applications made easy via Web PI

Web Deploy (MsDeploy) Creating a custom Provider

Web Deploy (MSdeploy) Syncing 2 or more Folders

How to see the Web Deploy commandline generated by VS 2010

Setting Folder Permissions on Web Publish

Compress CSS & JavaScript during Publish

Video on deployment with VS 2010 and Web Deploy

How to execute core MsBuild targets after Core Compile

Walkthroughs

Deployment to a Hosting Provider

Hosting an ASP.NET Web Forms Application on Windows Azure

Hosting an ASP.NET MVC Application on Windows Azure

Hosting an ASP.NET Web Application on Windows Azure

Using a Web Deploy package to deploy to IIS on the dev box and to a third party host

Hope you will enjoy the VS 2010 Web Deployment features…

Thanks,

-Vishal

[vishalrjoshi]

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