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 开发者_StackOverflow社区all the tables, then views, then functions etc.
Or there is another, normal way to do that without so much headache?
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?)
If 3rd party tools are acceptable, Red Gate SQL Compare is a great tool.
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.
Try SQL Examiner Suite: http://www.sqlaccessories.com/SQL_Examiner_Suite/ - you can copy both schema and data with this tool
精彩评论