How to copy architecture of an existing database to a new database in SQL Server

Question

I'm using

Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64) Express Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

and I need to copy the database architecture(Tables and their constraints) to a new database so I can extract the database diagram. The current database won't allow me to(extract a diagram), but any new database I create, will.

I tried creating one with the old ones mdf but it threw an error.

I tried to do an export, which copied all of the tables, but none of the constraints!!!

Why not just do a backup and restore it to the new database? Then you will have all the structure and the data. — Arion, Dec 17 '14 at 07:14
@Arion , I tried that. But the mdf references the old database, and it throws an error? — IEnjoyEatingVegetables, Dec 17 '14 at 07:43

score 1 · Accepted Answer · answered Dec 17 '14 at 07:15

1

In SQL Server Management Studio, right click on your database, select Tasks, Generate Scripts. Select the options for generating tables, indexes and constraints. Use the generated script(s) to recreate a new database where you can create the diagrams.

answered Dec 17 '14 at 07:15

David Brabant

41,623
16
83
111

David, the script is littered with references to the old database. Is there an easier way then just picking out all the old references from the script? – IEnjoyEatingVegetables Dec 17 '14 at 07:40

Anil · Answer 2 · 2014-12-17T07:25:04.440

1

Management studio->Right click on the database
Task->Generate script->Script entire database and all database objects
generate the script to a file/clipboard/new query window
Create new database schema from this script

edited Dec 17 '14 at 07:25

answered Dec 17 '14 at 07:18

Anil

3,722
2
24
49

David's was more descriptive. I'll probably have to choose his answer. – IEnjoyEatingVegetables Dec 17 '14 at 07:41

score 1 · Answer 3 · answered Dec 17 '14 at 09:13

1

If you have access to (friendly) developer they may have a copy of MS Visual Studio with SQL Server data tools installed; in it there is a handy feature called SQL Schema Compare that can copy whole or parts of schemas between databases.

answered Dec 17 '14 at 09:13

Rikalous

4,514
1
40
52

How to copy architecture of an existing database to a new database in SQL Server

3 Answers3