0

I am trying to find a way in VS2010 to add a few objects to an existing database without scripting out all of the existing objects. This is a maintenance project that is adding a table, altering an existing table by adding a column, creating a foreign key constraint between them and altering a stored proc to use a new column in the existing table rather than a hard coded case statement. I tried a database project and then converted it to SSDT in an attempt to find a way to not drop the database.

The scripts generated from this database project generate drop and create statements for the database. If I manually remove the database portion of the scripts, the result is exactly what I need. Is there a way to force the project to NOT drop and recreate the database, or is there a project type I can use instead?

Improve this question
4
  • Do you mean VS 2010? If so, are you using SSDT projects or one of its previous incarnations?
    – Mark Storey-Smith
    Commented Feb 6, 2013 at 19:46
  • vs 2010 on TFS. Tried both.
    – William Salzman
    Commented Feb 6, 2013 at 19:54
  • 1
    Tried both what? Also, what do you mean by "my project is just a couple of tables..."? An SSDT database project represents a database in its entirety. If you create a project containing two tables and try to publish to an existing database, it will want to drop all tables in the target that don't exist in the source.
    – Mark Storey-Smith
    Commented Feb 6, 2013 at 20:04
  • Trying to find a way in VS2010 to add a few objects to an existing database without scripting out all of the existing objects. This is a maintenance project that is adding a table altering an existing table creating a foreign key constraint between them and altering a stored proc to use a new column in the existing table rather than a hard coded case statement. I tried a database project and then converted it to SSDT in an attempt to find a way to not drop the database.
    – William Salzman
    Commented Feb 6, 2013 at 20:18

1 Answer 1

Reset to default
3

For SSDT, when you choose Publish to generate a script the advanced options include "Always re-create database". Untick that and you're good to go.

Advanced Publish Settings

Same option applies if you do a local debug build.

Debug

Similar options buried in the old VS2010 Database Project options somewhere.

To be honest, from the description of what you're doing SSDT and/or VS2010 database projects are overkill. Scribble a script in SSMS and be done with it.

Improve this answer
2
  • Thanks, however that box is not checked on my project... Guess I will just fall back to the script like you suggest.
    – William Salzman
    Commented Feb 6, 2013 at 20:55
  • You were right. I was publishing against a local db which did not have the database, so it added drop/create statements for the db. When I published against a test server with the proper db already there, it generated an incremental change script. Thanks,
    – William Salzman
    Commented Feb 6, 2013 at 21:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.