Is it possible to update a SSDT DB project from a database?

Question

We have two software projects that both communicate with a single database. Right now SQL updates are all done on the database and it's relying on developers to make sure to update both sets of projects independently to use the latest database model. Making these matters worse both projects are in separate solutions in separate source control repositories.

While I acknowledge this is a terrible situation to be in, I inherited this situation, and while my long term goal is to consolidate and share the (lots) of duplicated logic between them in one common project shared among both sets of application for various reasons it is not feasible to jump right into that right now due to critical deadlines coming up and the need to combine them iteratively and schedule it with other developers to not disrupt work too much.

Keeping that in mind, I really want to use SSDT to at least start bringing the database structure under source control and make it easier to manage, as there are quite a few database changes that I'm about to do.

The problem with SSDT in this scenario is that you can only import from database once. After that the option is greyed out and unavailable, which is apparently a design decision of SSDT, since it's explicitly listed in the MSDN documentation.

Is there any easy way to update my SSDT project without nuking the current project and recreating it each time someone makes a change to the database structure?

Erie

Answer 1

Firstly you are right, it is a horrible situation so work on improving it in the long term!

There are two things you can do, firstly you could use SSMS "Generate Scripts" to export all the objects and then use the import in SSDT to import from the scripts - this isn't greyed out.

The second thing you can do is manually bring the changes in using the schema compare in SSDT, you can set the database as the source and project as the destination and choose what you drop, update and import.

Jonna

Didn't think about either of those options, both should work well for my purposes. Thanks, – Erie

I have a question though, I want to do the same thing as well but when I tried doing option #2 which is to use SSDT Schema compare with the database as the source and the project as the target, I encountered the error " schema drift detetected " what can I do about that? – Debunk

Answer 2

its bit delay in answer. I am using VS2017 Database project in which I have achieved this task by comparing a local database with database project once the comparison is over you can update the database by update button

Step 1 right click on the database project and click on schema compare item.

Step 2 select target -> select database connection option

Step 3 change source and target

Review Screenshots for more detail

Aerialist

Answer 3

I am going with compare solution :
Choose schema compare and make your database as a source and database project as a target then compare and update

see the this answer

Extrauterine

Answer 4

Make a new temp Database project (outside of TFS) and import all the objects. Checkout the Database project (inside TFS) and copy and paste all the folders (excluding BIN, OBJ folders) from the new temp Database Project into the Database Project (in TFS) and check in. This was way you get the latest DB object into TFS without duplicating. If you expect new files in the copy/paste operation, then the new files should be included in the DB Project. Delete the temp Database project folder. You will need to do the process whenever you want to update all DB Objects into TFS.

This is a workaround which worked for me for this file duplicating issue.

Bookbinder