May 28, 2003 at 4:49 pm
Hello
I'm trying to transfer tables from a database on an SQL 7 machine, to a database on a different machine running SQL 2000.
I have tried using DTS to transfer the object, but each time it seems to be working, and then fails all of a sudden.
My database has many tables, most of which have primary keys, default values and indentity columns.
How can I transfer the tables whole and intact to the new server including all the data?
The two servers belong to two different web hosting companies I run my website with (I'm moving to a new host).
Please help.
If anyone wants to help me directly, you can contact me via MSN Messenger at tcl_adm_ryster@hotmai.com or by email using ryster@tcln.com
Thanks
Ryan Spooner aka Ryster
May 28, 2003 at 5:07 pm
Any chance you could use SQL to make a backup of the db on SQL7, then copy the file to the SQL2k server and do a restore?
Otherwise, you might try scripting the tables and then creating them on the SQL2k box and then inserting the data into the new tables. In DTS you would need to enable Identity Insert and then you could even use the same identity values you already have setup.
Michelle
Michelle
May 28, 2003 at 5:30 pm
I will try creating the BAK file and copying it over.
If I am unable to do that, can you clarify how I would go about this "identity insert" you mentioned? I think I am fairly comfortable with creating scripts, and transferring data. I'm just not to keen on having to go through all of my tables resetting primary keys, identity colors and default values. Especially seeing as I am on dialup and the server is in a different country to me, im sure you understand 🙂 Will the scripting retain primary keys and default values?
May 28, 2003 at 5:59 pm
Within Enterprise Manager, you can right click on the database and choose All Tasks -->Generate SQL Scripts... On the options tab you can choose whether you want primary keys, foreign keys, defaults, etc. scripted.
Then you should be able to take that script and get the structures created on the new server.
In DTS, you can choose to copy the tables and with the transform button, you have the ability to 'enable identity insert'. This means even though you have the field set to be an identity, it will allow you to put your own values in the field while the upload is happening.
Hope this Helps!
Michelle
Michelle
May 28, 2003 at 6:25 pm
Sorry for sounding like a complete novice, but where is this enable indentity insert option you mentioned? I dont see a transform button anywhere in the DTS wizard.
May 28, 2003 at 6:41 pm
I'm not good with HTML, so I sent the pictures via email to you. Does this help?
"Hope this isn't too big for your email, but I don't really know how to explain where this 'transform' option is. After you open the DTS wizard, you enter the connect info for the source and destination servers. Then you choose copy tables option. Then you get this first screen. When you place a checkmark next to a table you want to copy, the third column becomes active and when you hit the ellipsis button, you will see the second picture where you can choose the 'enable identity insert' option."
Michelle
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply