My team recently rebuilt their SQL Server 2005 development database from scratch using the TFS source files.
Afterwards, we had a nice clean database that more closely matched our test and production environments. However, we also lost the database diagrams that had been created over the months in the old development database.
Does anyone know of a way to export a SQL Server 2005 database diagram from one database to another that contains the same tables and schema objects?
UPDATE:
Both Valentin and CMS's answers below should work just fine. I accepted Valentin's because it was first and I had already started using it.
I ended up creating a temp table called 'robsysdiagrams' and copied all the data into that and used the Microsoft SQL Server Database Publishing Wizard to script the data inserts out to disk. I then renamed robsysdiagrams to sysdiagrams in the script and ran it on the destination database. Everything worked fine.
Right click on the database containing the diagrams. Click on All Tasks then on "Export data". The Import / Export option will be displayed, click Next. Define the source and then click Next.
Where the database diagrams are stored? Actually the diagrams are stored in a table called “sysdiagrams”. The sysdiagrams contains a column named “definition”. The structure of the diagram is stored in the “definition” column using varbinary data type.
An old but elegant answer I came across while looking to solve the same issue: bcp
To extract:
bcp YOUR_DATABASE..sysdiagrams out YOUR_DIAGRAM_NAME.bcp -c -T -S SERVERNAME
To import:
(cd to the directory the diagram is in) bcp YOUR_DATABASE..sysdiagrams in YOUR_DIAGRAM_NAME.bcp -c -T -S SERVERNAME
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With