How to transfer SQL Server DB structure to another server

views:

120

answers:

+1 Q:

How to transfer SQL Server DB structure to another server

Tell me please what's the best way to copy db structure to another server?

I'm trying to generate a script in Management Studio and then run that script on the remote machine. And I hate that thing. It works unpredictable, you have to fix manually a lot of crap in the generated script. I guess that isn't the best solution.

Then I tried to generate SSIS package with DTSWizard.exe but I know nothing about that stuff, and I couldn't find a way to copy only the structure without data.

Maybe there is a way somehow to break entire db structure into smaller pieces and work with them - first do all the tables, then views, then functions etc.

Or there is another, normal way to do that without so much headache?

+2 A:

If 3rd party tools are acceptable, Red Gate SQL Compare is a great tool.

Mike C. 2010-06-21 17:39:10

Red Gate SQL Compare is a great tool!

code4life 2010-06-21 17:55:34

Yeah red gate's cool... although entire tool-belt's damn expensive

Ike 2010-06-22 00:55:38

I wonder if there is any tool that can scan your database structure or schema script and report about inconsistencies. Because if you generate script you can see problems only at script's runtime.

Ike 2010-06-22 00:59:09

+4 A:

Generating a script is the best solution. If you have to fix it manually, you are scripting it incorrectly. Use the "Generate Scripts..." task in Management Studio

Be sure to check:

Are you generating the script for the appropriate version?
Are you specifying "DROP and CREATE" and "IF EXISTS" options if running on an earlier version of the db?
Are you scripting all the auxiliary objects (users, triggers, indexes, etc?)

BC 2010-06-21 17:42:31

My larger point is this: I use the script generator to maintain db scripts for initializing and upgrading applications. I don't find that it works unpredictably if I scripted the database with the same set of assumptions that I use when I run it. For instance, does the script create the database itself or does the database already need to exist and be selected? Does the script create security objects?

BC 2010-06-21 17:54:43

The normal scripting tool does NOT properly generate all the permissions, at least not for my DB, despite selection of the Generate Object-level Permissions option.

Chris Wuestefeld 2010-06-21 21:35:05

I am trying to script a database on sql2005 server using management studio of 2008. Big headache here is that script generator puts Create VIew statements randomly wherever it wants. Sometimes even before tables. And I have to waste my time with tedious cut-n-paste job. Although I'm thinking about four scripts instead of one. I'll try to script all objects separately - functions, tables, storedprocs, views...

Ike 2010-06-22 00:51:11

Try SQL Examiner Suite: http://www.sqlaccessories.com/SQL_Examiner_Suite/ - you can copy both schema and data with this tool

SQLDev 2010-06-22 19:32:58

I'll give it a try... thanks

Ike 2010-06-24 01:40:13

Hey mate,
Try DBSourceTools. (http://dbsourcetools.codeplex.com)
Its open source, and specifically designed to script an entire database - tables, views, procs to disk, and then re-create that database through a deployment target.
You can script all data, or just specify which tables to script data for.
Additionally, you can zip up the results for distribution.
We use it for source control of databases, and to test update patches for new releases.
In the back-end it's built around SMO, and thus supports SQL 2000, 2005 and 2008.
DBDiff is integrated, to allow for schema comparisons.
Have fun, - Nathan.

blorkfish 2010-07-14 04:03:29

Thank you, I will try it!

Ike 2010-07-14 14:02:45

ansaurus

tags:

views:

answers:

How to transfer SQL Server DB structure to another server

related questions