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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Copy Schema
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

PlasticLizard
Starting Member

7 Posts

Posted - 05/06/2002 :  20:55:40  Show Profile  Reply with Quote
Aloha,

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?

Thanks,
N.

DavidD
Yak Posting Veteran

Australia
73 Posts

Posted - 05/06/2002 :  23:08:08  Show Profile  Reply with Quote
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.

Regards
DD

Go to Top of Page

Nazim
A custom title

United Arab Emirates
1408 Posts

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

HTH

--------------------------------------------------------------
Go to Top of Page

PlasticLizard
Starting Member

7 Posts

Posted - 05/07/2002 :  15:39:38  Show Profile  Reply with Quote
Thank you both! Nazim - worked like a charm, saved my life :)

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000