How to avoid deleting diagrams from database when publishing in Visual Studio 2015

Question

I've got a SQL Server 2014 database project in VS 2015. I've also created a couple of schema diagrams in SSMS (since VS doesn't have any schema diagramming that I'm aware of). It's early in the project's lifecycle and plenty of refactoring occurs. The SSMS diagrams are good at updating themselves in the face of change - which is really nice. I'd like to preserve the diagrams on (re)publish in the dev environment.

The problem is, to keep up with the refactorings, I'd need to delete things in the target database that are no longer in the project (or just recreate the target db in the dev environment)...but this will cause my diagrams to be blown away. I haven't figured out a clean way to avoid this and it's a kick in the shins to rebuild the diagrams. Has anybody figured out a reasonably clean way to incrementally publish while deleting project cruft yet preserving the diagrams?

Answer 1

Stage the Diagrams to a global temp table or a table in another database before you recreate the dev database. You can do this by running something like this:

IF (OBJECT_ID(N'tempdb..##TempTable') IS NOT NULL) DROP TABLE ##TempTable ;

SELECT  * 
INTO ##TempTable
FROM    sysdiagrams

Unfortunately you cannot successfully run this from a pre-deploy script if you Drop and recreate the dev database. The pre-deploy script runs after the drop. So I would look at creating a powershell script that publishes the dacpac, and before the dacpac deploy step execute the above statement to stage the diagrams.

After the dacpac is deployed, run something like this to recreate the diagrams:

INSERT INTO dbo.sysdiagrams (name, principal_id, version, definition)
SELECT name, principal_id, version, definition
FROM ##TempTable AS tt