Duplicate DB layout on another Server

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Duplicate DB layout on another Server

Author

Topic

GlynD02
Starting Member

13 Posts

Posted - 2007-09-28 : 12:50:25

Hi

Is there anyway of copying an entire database (table layout, stored procs etc) without the data that the tables contain please?

I have a SQL7 DB (production) that I need to replicate on a SQL200 Server (development) but the DB is very large and all I need is the table structure and the stored procs on the development Server...

Cheers

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-28 : 12:51:56

You can script out the db objects. Right click on db -> All tasks -> Generate scripts. Select tables/provs/views all the objects you need. compile the script on the target server.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

GlynD02
Starting Member

13 Posts

Posted - 2007-09-28 : 13:00:35

Thank you Dinakar

The script that it has created - does that need to be run in the SQL Query Analyzer while the Master DB is selected please?

Cheers

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-28 : 13:02:02

no you run it in the target Database. usually when you generate a script like that it includes the USE <db> statement. But its better to check and make sure you are in the target db.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

GlynD02
Starting Member

13 Posts

Posted - 2007-09-28 : 13:06:32

Oh I see:

1. Create a new empty DB
2. Then go to the SQL Query Analyzer
3. Make sure your new empty DB is selected
4. Run the script

Cheers

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-28 : 13:11:13

quote:
Originally posted by GlynD02

Oh I see:

1. Create a new empty DB
2. Then go to the SQL Query Analyzer
3. Make sure your new empty DB is selected
4. Run the script

Cheers

Yes.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

GlynD02
Starting Member

13 Posts

Posted - 2007-09-28 : 13:12:19

Thank you very much that worked a treat!

Cheers

Kristen
Test

22859 Posts

Posted - 2007-09-28 : 14:40:45

3.5) Make sure the script does not have an explicit database name in it - otherwise it may change to that database, pre delete everything, then create new, empty, objects in place of your nicely populated tables

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources