what method is the most used and efficient to copy "database schema" to other servers not the db!

views:

answers:

+1 Q:

what method is the most used and efficient to copy "database schema" to other servers not the db!

servers all sql server 2008, and win xp

i have the following task

create a huge database, DONE
distribute it to the 20 waiting servers!!
if there were two or three i would have taken the trouble of creating the db's on all of using sql server managemnt stdio

but i am guessing that there is an efficient way

please note,

only the copy of the database structure, the schema is needed not the values within the cells!

thank you

+7 A:

alt text

Gabriel McAdams 2010-10-22 18:17:02

One caveat, you may need to tweak the scripts to create objects in the correct order. For instance you may want to ensure all PK tables are first then the child tables and then any function, and then any views and then any stored procs. That way you won't run into issues where your script won't run properly because the object it was depending on didn't exist. You also need to be careful to create indexes/PK/FKs/constraints etc. Sometimes you can manage to create a script without thee things and then the new db may have data integrity or performance problems until the issue is resolved.

HLGEM 2010-10-22 18:31:28

http://stackoverflow.com/questions/4003030/running-the-script-made-by-generate-script-on-different-server-gives-error i will mark this as answer if it works, i cannot see what am i doing wrong

2010-10-23 07:59:13

+1 A:

What Gabriel McAdams has shown, or, Redgate SQL Compare does this very nicely also.

Ash Machine 2010-10-22 18:22:43

+1 A:

If you can spare the moolah, using a tool like Red Gate's SQL Packager is an option i have used in the past and it works well!

The tool can do a lot more as well and may not be worth the spend though if you do not need the other features!

In that case, Gabriels'option above is definitely the easiest one to go with!

InSane 2010-10-22 18:22:57

+2 A:

Or of course you should have been creating the scripts as you went along and putting them in Source control. Then you would have exactly which scripts you needed for this version of the software and be used to doing the same thing for later modifications. You would also script the data inserts for any lookup tables you need to build.

Not having that, you can script the entire database. or use a SQL compare tool. But I strongly urge you to start treating database code like all other code and scripting, storing it in source control and versioning it. Life is so much better when you do that.

HLGEM 2010-10-22 18:27:35

ansaurus

tags:

views:

answers:

what method is the most used and efficient to copy "database schema" to other servers not the db!

related questions