CODEWITHSUNDEEP
sql-servervisual-studio-2015ssmsdiagram
Clay
Clay

Reputation: 5084

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

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?

Upvotes: 5

Views: 471

Answers (3)

bielawski
bielawski

Reputation: 1702

Anyone is still wanting to deal with diagrams should see this blog Scripting an SSMS Diagram. Basically it gives code that turns any diagram into an SSMS script. Once scripted you can do anything you want including restore it to another system.

Keep in mind that diagrams only reference objects in the database. They don't store any object data except their names. So only objects that have the same type and name will appear in the diagram and others will give the 'will be removed from the diagram' warning.

It's a WordPress link so it should not change but if it does for some reason you can Google Rick Bielawski Script Diagram to find it wherever it is.

Upvotes: 1

Lewis Worley
Lewis Worley

Reputation: 279

I do not have a lot of experience with visual studio database development, so this is more of an idea than an actual answer.

If you created a materialized (indexed) view of dbo.sysdiagrams and included it in the VS project would that prevent dbo.sysdiagrams from being blown away when you deployed? 

create view test with schemabinding as select name, principal_id,diagram_id,version, definition from dbo.sysdiagrams 

CREATE UNIQUE CLUSTERED INDEX CIX_test_diag 
    ON dbo.test(name, principal_id,diagram_id,version)

Upvotes: 0

db_brad
db_brad

Reputation: 923

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

Upvotes: 3

Related Questions