I’ve been using SQL server for around 10 years now and for a long time I found the scripting available in it was poor at best. Have you ever tried copying a number of tables from one database to another? It used to be you would either:
1) First copy the tables, indexes and keys by scripting them, then use Enterprise Manager to copy the tables. This of course fails if the tables are copied in the wrong order for the foreign keys to work. (The topmost level FK table has to be copied first, then the second most, etc.) This would only work if your keys matched alphabetically. Arg!
2) Copy the tables using Enterprise Manger to start, this would cause you to loose all of your foreign keys, indexes, etc. Not exactly an optimal choice.
Well, finally Microsoft has provided us with a much better alternative. It’s called “Microsoft SQL Server Database Publishing Wizard” and is available for download here:
http://www.microsoft.com/downloadS/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en (requires .Net version 2.0)
By default it installs to your machine here:
C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\SqlPubWiz.exe
I won’t go into allot of detail as to how to use it here, I find it quite self explanatory. The big deal is that this tool is capable of both scripting your objects, indexes, foreign keys AND your data. To boot it does so in the correct order so the data will actually insert into your tables. Give it a try, you’ll be happy you did!