Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Copy Schema

Author  Topic 

Starting Member

7 Posts

Posted - 2002-05-06 : 20:55:40

I want to use the schema of my prototype database to be the starting point of many new databases that may develop independently of the original over time. Unfortunately when creating this database I saved the SQL Script for each object in a separate file, so I don't know what order I would need to execute them in order for the dependencies to work out right. The Generate Sql Script command doesn't seem to pay any attention to that either. Is there a relatively painless way to copy, or script the creation of, a database with all of the objects and constraints and whatnot in tact?


Yak Posting Veteran

73 Posts

Posted - 2002-05-06 : 23:08:08
The best way is to detach the database and then simply copy the .mdf and .ldf files and reattach the new ones.
If your database cannot be freed for detaching I find the best way is to use a script with a series of select into statements that write all data and tables into a new db (use the system tables to get your full list). Then script out all constraints, keys, triggers etc and impose this on the new db - it should work without a hassle, although if you are using EM to create this script you have to take out the lines that re-create the actual tables manually or you lose all your data.


Go to Top of Page

A custom title

1408 Posts

Posted - 2002-05-07 : 03:27:29
Take a backup of the database and restore in the new db. if you dont want any data run sp_msforeachtable 'truncate table ?' .


Go to Top of Page

Starting Member

7 Posts

Posted - 2002-05-07 : 15:39:38
Thank you both! Nazim - worked like a charm, saved my life :)

Go to Top of Page

- Advertisement -