SQL Server 2014 - Exporting/restoring database diagrams

Within Microsoft SQL Server database diagrams provide a GUI oriented way to create relationships between tables - primary key/foreign key and so on. Since they are not first class database objects they are not included if you script out a database.  There's a nice way to do this though, so you can copy diagrams from an old database instance to a newly created instance.

On a PC with the bcp command installed open us a CMD prompt and cd to a working directory where you can create a bcp file.

 

In the CMD prompt type something like:

bcp sourceDatabaseName..sysdiagrams out alignmentDBdiagrams.bcp -c -T -S myServerName

then

bcp targetDatabaseName..sysdiagrams in alignmentDBdiagrams.bcp -c -T -S myServerName

This will copy all database diagrams in the sourceDatabaseName database to the targetDatabaseName database - both on the myServerName server.

 

 

 

Update:
In the above, replace:
out alignmentDBdiagrams.bcp
with
out alignmentDBdiagrams_%date:~-4,4%%date:~-7,2%%date:~-10,2%.bcp

and you will create a file called:

alignmentDBdiagrams_20150502.bcp

Useful if you want to automate the process...