Create database add-in: database project on existing third party database

Question

Post reply

Create database add-in: database project on existing third party database

Patrick_Fiedler

SSCommitted

Points: 1874
More actions
April 26, 2011 at 7:17 am

#238471

Hi everyone,

I have actually the problem that I have a third party database which I want to extend with some tables, procedures and triggers with the capability to create unit tests with visual studio for that. I am working with Visual Studio 2010 Premium Edition.

I have created a new database project and added a database reference to it. Works partly fine, I see in the schema view all objects grayed from the third party database. I can add objects like tables and stored procedures. It also works, but if I build the sql script it contains always recreating the whole database.

How can I create a database project which will upgrade an existing database and not recreate this?

Thanks for your help.

[font="Arial"]Kind regards,

Patrick Fiedler
Consultant
Fiedler SQL Consulting
www.fiedler-sql-consulting.de
[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

You need to look in the project properties for the database. It's set to recreate the entire database. You can have it do incremental builds. This article[/url] is a bit long in the tooth, but many of the concepts are the same. I think it'll help. If not, get a copy of SQL Server Team-based Development[/url]. I've got a chapter in there on deployments using Visual Studio as well as Red Gate tools.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Patrick_Fiedler SSCommitted Points: 1874 More actions · Answer 2

Hi Grant,

thanks for the answer. Have you checked it? I have deactivate the "Always re-create database" option in sqldeployment settings, but the resulting script is already with code part of recreate database. Or do you mean another option?

[font="Arial"]Kind regards,

Patrick Fiedler
Consultant

Fiedler SQL Consulting

www.fiedler-sql-consulting.de
[/font]

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

Yeah, I checked it. At my previous employer we were automating builds, both complete and incremental, for the last couple of years. Make sure it's checked on all projects involved.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Patrick_Fiedler SSCommitted Points: 1874 More actions · Answer 4

I have examined the "Always re-create database" flag and I must determine that the resulting sql scripts are the same, activated and deactivated?!?! 🙁

Why?

I have only one project in my solution.

Is that flag the right flag to change or is there another flag to prevent from recreating the database?

My sqldeployment settings are the following:

<?xml version="1.0" encoding="utf-8"?>



<DeploymentConfigurationSettings xmlns="urn:Microsoft.VisualStudio.Data.Schema.Package.DeploymentConfigurationSettings">

  <Version>1.0</Version>

  <Properties>

    <AlwaysCreateNewDatabase>False</AlwaysCreateNewDatabase>

    <BlockIncrementalDeploymentIfDataLoss>False</BlockIncrementalDeploymentIfDataLoss>

    <BuildtimeContributorsMustExist>True</BuildtimeContributorsMustExist>

    <CommentOutSetVarDeclarations>False</CommentOutSetVarDeclarations>

    <DeployDatabaseProperties>False</DeployDatabaseProperties>

    <DeploymentCollationPreference>UseTargetModelCollation</DeploymentCollationPreference>

    <DoNotUseAlterAssemblyStatementsToUpdateCLRTypes>False</DoNotUseAlterAssemblyStatementsToUpdateCLRTypes>

    <GenerateDeployStateChecks>False</GenerateDeployStateChecks>

    <GenerateDropsIfNotInProject>False</GenerateDropsIfNotInProject>

    <PerformDatabaseBackup>False</PerformDatabaseBackup>

    <ScriptDatabaseCollation>False</ScriptDatabaseCollation>

    <SingleUserMode>False</SingleUserMode>

  </Properties>

</DeploymentConfigurationSettings>

[font="Arial"]Kind regards,

Patrick Fiedler
Consultant

Fiedler SQL Consulting

www.fiedler-sql-consulting.de
[/font]

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 5

That's the right flag. I just did a full test to validate, incrementals are created by having this off. Do you have your target set appropriately? If the target is not the right database it will try to recreate the db. It's absolutely working here, so there has to be something in the config on your end.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software