VS2010 Beta1 "Delete existing objects before creating" flag for DB Deployment

Web Development Tools Microsoft

Here is an example of end to end scenario of the last blog VS2010 Beta1 Web Application Project Database package and SMO options. Here, we’re going to package a web application and its database with “Delete existing objects before creating” flag, so that we can test the deploy multiple times without the need to drop the database each time after deployment.  This feature should be useful for the daily QA testing.

1. Create a Web Application Project

2. Go to its property page, Deploy SQL property page, click Add to add an connection name.  Select it, check “Pull data from an existing databases”.  Input a connection string for the source database.  Input a connection string for the destination connection string.

3. Saving the project.

4. Looking for the supported SMO options from msdeploy IIS7 module.  Start IIS7, and choose “Export Application”.

5. Click “Add Components …” button.  Add a new provider, choose dbFullSql.

6. Click “…” in the new provider’s “Provider Settings” column, we’ll see all the available SMO options.

7. Set ScriptDropsFirst=”True” inside the project file’s PreSource XML node (e.g. appName.vbproj/csproj file), and reload it in VS.

<PublishDatabaseSettings>
  <Objects>
    <ObjectGroup Name="NewConnection1" Order="1">
      <Destination Path="Data Source=1p18-fwg35%3bInitial Catalog=SimpleDB1_test1%3bIntegrated Security=True" />
      <Object Type="dbFullSql">
        <PreSource Path="Data Source=1p18-fwg35%3bInitial Catalog=SimpleDB1%3bIntegrated Security=True" ScriptSchema="True" ScriptData="False" SchemaQualify="True" ScriptDropsFirst="True" />
        <Source Path="objDebugAutoScriptsNewConnection1_SchemaOnly.sql" />
      </Object>
    </ObjectGroup>
  </Objects>
</PublishDatabaseSettings>

8. Package the project from command line: %projectDir%>msbuild WebApplication2.vbproj /target:package

9. Read sample deploy command line batch file and set the environment: %projectDir%objdebugpackage> WebApplication2.deploy.cmd

set Path=%Path%;"C:Program FilesIISMicrosoft Web Deploy"

10. Test the package deploy locally from command line: %projectDir%objdebugpackage> WebApplication2.deploy.cmd /y

If you check the script created inside the package %projectDir%objdebugpackage%projectName%.zip file , you can see the sqlScript file inside with format like following:

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Products_Categories]') AND parent_object_id = OBJECT_ID(N'[dbo].[Products]'))
ALTER TABLE [dbo].[Products] DROP CONSTRAINT [FK_Products_Categories]
GO
IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_Products_UnitPrice]') AND parent_object_id = OBJECT_ID(N'[dbo].[Products]'))
ALTER TABLE [dbo].[Products] DROP CONSTRAINT [CK_Products_UnitPrice]
GO
IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_Products_UnitsInStock]') AND parent_object_id = OBJECT_ID(N'[dbo].[Products]'))
ALTER TABLE [dbo].[Products] DROP CONSTRAINT [CK_Products_UnitsInStock]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TotalProductsCost]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TotalProductsCost]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShowCategories]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ShowCategories]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShowProducts]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ShowProducts]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ProductsPerCat]'))
DROP VIEW [dbo].[ProductsPerCat]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Products]') AND type in (N'U'))
DROP TABLE [dbo].[Products]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Categories]') AND type in (N'U'))
DROP TABLE [dbo].[Categories]
GO
IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'ExpireDate' AND ss.name = N'dbo')
DROP TYPE [dbo].[ExpireDate]
GO
CREATE TYPE [dbo].[ExpireDate] FROM [datetime] NULL
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Categories](
    [CategoryID] [int] IDENTITY(1,1) NOT NULL,
    [CategoryName] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED 
(
    [CategoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)

…

You can see, choose a right SMO option is not trivial.  There are many SMO options, combining of which may have different effects.  We do need some trial and errors to apply the right SMO option for advanced usage.

Xinyang Qiu
SDETII

Visual Studio Web Tools

Web Development Tools Microsoft

Follow