{"id":3524,"date":"2009-07-02T20:31:52","date_gmt":"2009-07-02T20:31:52","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/webdev\/2009\/07\/02\/vs2010-beta1-quotdelete-existing-objects-before-creatingquot-flag-for-db-deployment\/"},"modified":"2009-07-02T20:31:52","modified_gmt":"2009-07-02T20:31:52","slug":"vs2010-beta1-quotdelete-existing-objects-before-creatingquot-flag-for-db-deployment","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/dotnet\/vs2010-beta1-quotdelete-existing-objects-before-creatingquot-flag-for-db-deployment\/","title":{"rendered":"VS2010 Beta1 "Delete existing objects before creating" flag for DB Deployment"},"content":{"rendered":"

 <\/p>\n

Here is an example of end to end scenario of the last blog VS2010 Beta1 Web Application Project Database package and SMO options<\/a>. Here, we\u2019re going to package a web application and its database with \u201cDelete existing objects before creating\u201d 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.<\/p>\n

1. Create a Web Application Project<\/p>\n

2. Go to its property page, Deploy SQL property page, click Add to add an connection name.  Select it, check \u201cPull data from an existing databases\u201d.  Input a connection string for the source database.  Input a connection string for the destination connection string.<\/p>\n

\"image\"<\/a> <\/p>\n

3. Saving the project.<\/p>\n

4. Looking for the supported SMO options from msdeploy IIS7 module.  Start IIS7, and choose \u201cExport Application\u201d.<\/p>\n

\"image\"<\/a> <\/p>\n

5. Click \u201cAdd Components \u2026\u201d button.  Add a new provider, choose dbFullSql.<\/p>\n

\"image\"<\/a> <\/p>\n

6. Click \u201c\u2026\u201d in the new provider\u2019s \u201cProvider Settings\u201d column, we\u2019ll see all the available SMO options.<\/p>\n

\"image\"<\/a> <\/p>\n

7. Set ScriptDropsFirst=\u201dTrue\u201d <\/strong>inside the project file\u2019s PreSource XML node (e.g. appName.vbproj\/csproj file), and reload it in VS.<\/p>\n

<<\/span>PublishDatabaseSettings<\/span>>\n  <<\/span>Objects<\/span>>\n    <<\/span>ObjectGroup <\/span>Name<\/span>=<\/span>"NewConnection1<\/span>" Order<\/span>=<\/span>"1<\/span>">\n      <<\/span>Destination <\/span>Path<\/span>=<\/span>"Data Source=1p18-fwg35%3bInitial Catalog=SimpleDB1_test1%3bIntegrated Security=True<\/span>" \/>\n      <<\/span>Object <\/span>Type<\/span>=<\/span>"dbFullSql<\/span>">\n        <<\/span>PreSource <\/span>Path<\/span>=<\/span>"Data Source=1p18-fwg35%3bInitial Catalog=SimpleDB1%3bIntegrated Security=True<\/span>" ScriptSchema<\/span>=<\/span>"True<\/span>" ScriptData<\/span>=<\/span>"False<\/span>" SchemaQualify<\/span>=<\/span>"True<\/span>" ScriptDropsFirst<\/span>=<\/span>"True<\/span>"<\/strong> \/>\n        <<\/span>Source <\/span>Path<\/span>=<\/span>"objDebugAutoScriptsNewConnection1_SchemaOnly.sql<\/span>" \/>\n      <\/<\/span>Object<\/span>>\n    <\/<\/span>ObjectGroup<\/span>>\n  <\/<\/span>Objects<\/span>>\n<\/<\/span>PublishDatabaseSettings<\/span>><\/span><\/pre>\n
8. Package the project from command line: %projectDir%>msbuild WebApplication2.vbproj \/target:package<\/p>\n
9. Read sample deploy command line batch file and set the environment: %projectDir%objdebugpackage> WebApplication2.deploy.cmd<\/p>\n
set Path=%Path%;"C:Program FilesIISMicrosoft Web Deploy"<\/p>\n
10. Test the package deploy locally from command line: %projectDir%objdebugpackage> WebApplication2.deploy.cmd \/y<\/p>\n
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:<\/p>\n
IF  EXISTS <\/span>(SELECT <\/span>* FROM <\/span>sys.foreign_keys WHERE object_id <\/span>= OBJECT_ID<\/span>(N'[dbo].[FK_Products_Categories]'<\/span>) AND <\/span>parent_object_id = OBJECT_ID<\/span>(N'[dbo].[Products]'<\/span>))\nALTER TABLE <\/span>[dbo].[Products] DROP CONSTRAINT <\/span>[FK_Products_Categories]\nGO\nIF  EXISTS <\/span>(SELECT <\/span>* FROM <\/span>sys.check_constraints WHERE object_id <\/span>= OBJECT_ID<\/span>(N'[dbo].[CK_Products_UnitPrice]'<\/span>) AND <\/span>parent_object_id = OBJECT_ID<\/span>(N'[dbo].[Products]'<\/span>))\nALTER TABLE <\/span>[dbo].[Products] DROP CONSTRAINT <\/span>[CK_Products_UnitPrice]\nGO\nIF  EXISTS <\/span>(SELECT <\/span>* FROM <\/span>sys.check_constraints WHERE object_id <\/span>= OBJECT_ID<\/span>(N'[dbo].[CK_Products_UnitsInStock]'<\/span>) AND <\/span>parent_object_id = OBJECT_ID<\/span>(N'[dbo].[Products]'<\/span>))\nALTER TABLE <\/span>[dbo].[Products] DROP CONSTRAINT <\/span>[CK_Products_UnitsInStock]\nGO\nIF  EXISTS <\/span>(SELECT <\/span>* FROM <\/span>sys.objects WHERE object_id <\/span>= OBJECT_ID<\/span>(N'[dbo].[TotalProductsCost]'<\/span>) AND <\/span>type in <\/span>(N'P'<\/span>, N'PC'<\/span>))\nDROP PROCEDURE <\/span>[dbo].[TotalProductsCost]\nGO\nIF  EXISTS <\/span>(SELECT <\/span>* FROM <\/span>sys.objects WHERE object_id <\/span>= OBJECT_ID<\/span>(N'[dbo].[ShowCategories]'<\/span>) AND <\/span>type in <\/span>(N'FN'<\/span>, N'IF'<\/span>, N'TF'<\/span>, N'FS'<\/span>, N'FT'<\/span>))\nDROP FUNCTION <\/span>[dbo].[ShowCategories]\nGO\nIF  EXISTS <\/span>(SELECT <\/span>* FROM <\/span>sys.objects WHERE object_id <\/span>= OBJECT_ID<\/span>(N'[dbo].[ShowProducts]'<\/span>) AND <\/span>type in <\/span>(N'FN'<\/span>, N'IF'<\/span>, N'TF'<\/span>, N'FS'<\/span>, N'FT'<\/span>))\nDROP FUNCTION <\/span>[dbo].[ShowProducts]\nGO\nIF  EXISTS <\/span>(SELECT <\/span>* FROM <\/span>sys.views WHERE object_id <\/span>= OBJECT_ID<\/span>(N'[dbo].[ProductsPerCat]'<\/span>))\nDROP VIEW <\/span>[dbo].[ProductsPerCat]\nGO\nIF  EXISTS <\/span>(SELECT <\/span>* FROM <\/span>sys.objects WHERE object_id <\/span>= OBJECT_ID<\/span>(N'[dbo].[Products]'<\/span>) AND <\/span>type in <\/span>(N'U'<\/span>))\nDROP TABLE <\/span>[dbo].[Products]\nGO\nIF  EXISTS <\/span>(SELECT <\/span>* FROM <\/span>sys.objects WHERE object_id <\/span>= OBJECT_ID<\/span>(N'[dbo].[Categories]'<\/span>) AND <\/span>type in <\/span>(N'U'<\/span>))\nDROP TABLE <\/span>[dbo].[Categories]\nGO\nIF  EXISTS <\/span>(SELECT <\/span>* FROM <\/span>sys.types st JOIN <\/span>sys.schemas ss ON <\/span>st.schema_id = ss.schema_id WHERE <\/span>st.name = N'ExpireDate' <\/span>AND <\/span>ss.name = N'dbo'<\/span>)\nDROP <\/span>TYPE [dbo].[ExpireDate]\nGO\nCREATE <\/span>TYPE [dbo].[ExpireDate] FROM <\/span>[datetime] NULL\n<\/span>GO\nSET <\/span>ANSI_NULLS ON\n<\/span>GO\nSET QUOTED_IDENTIFIER ON\n<\/span>GO\nCREATE TABLE <\/span>[dbo].[Categories](\n    [CategoryID] [int] IDENTITY<\/span>(1,1) NOT NULL<\/span>,\n    [CategoryName] [nvarchar](15) COLLATE <\/span>SQL_Latin1_General_CP1_CI_AS NOT NULL<\/span>,\n    [Description] [ntext] COLLATE <\/span>SQL_Latin1_General_CP1_CI_AS NULL<\/span>,\n CONSTRAINT <\/span>[PK_Categories] PRIMARY KEY CLUSTERED \n<\/span>(\n    [CategoryID] ASC\n<\/span>)WITH <\/span>(PAD_INDEX  = OFF<\/span>, STATISTICS_NORECOMPUTE  = OFF<\/span>, IGNORE_DUP_KEY = OFF<\/span>, ALLOW_ROW_LOCKS  = ON<\/span>, ALLOW_PAGE_LOCKS  = ON<\/span>)\n)<\/pre>\n
\u2026<\/p>\n
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.<\/p>\n
Xinyang Qiu \n  
SDETII <\/p>\n
Visual Studio Web Tools<\/p>\n","protected":false},"excerpt":{"rendered":"
  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\u2019re going to package a web application and its database with \u201cDelete existing objects before creating\u201d flag, so that we can test the deploy multiple times without the need to […]<\/p>\n","protected":false},"author":404,"featured_media":58792,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[197],"tags":[31,7341,7336,7323,7329],"class_list":["post-3524","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-aspnet","tag-asp-net","tag-database","tag-msdeploy","tag-visual-studio-2010","tag-web-deployment"],"acf":[],"blog_post_summary":"
  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\u2019re going to package a web application and its database with \u201cDelete existing objects before creating\u201d flag, so that we can test the deploy multiple times without the need to […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts\/3524","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/users\/404"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/comments?post=3524"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts\/3524\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/media\/58792"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/media?parent=3524"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/categories?post=3524"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/tags?post=3524"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}