Moving tables from one SQL server to another

Question

Moving tables from one SQL server to another

gaaq

SSC Veteran

Points: 204
More actions
December 12, 2003 at 2:43 pm

#59104

Hello all,
I have a question and wondering if anyone here can help.
I have a database that I have dettached and re-attached onto another server but still both copies each on there own server. The older server will be retired.This went fine.
Unknown to me was that a small department is using about 12 tables to input and search for "warranty" info,
Now these users access these tables by a web interface.
My problem is that they are pointing to the old server which is being retired for their queries.
I only noticed there was a problem with them when i dettached the database to free up space. So if anyone can tell me how to copy the tables from the old SQL server to the new one that would be greatly appreciated.
Thanks
GAAQ

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

fhanlon SSChampion Points: 13131 More actions · Answer 1

Are these tables in the database you moved? If so the users need to just point to the new server. Tables aren't just part of a server, they are part of a database. If these "warranty" tables are part of a different database, the detach and reattach this databases as well. Maybe the users don't know how to point to the new server?

Francis

newtons2 SSC Enthusiast Points: 193 More actions · Answer 2

Because they have the ability to insert as well as read you need to notify them to point at the new server but you also need to DTS the files over to the new server so that they dont lose any data.

If you need to know how to contact me by mail s.newton@rhi.net.

Regards

Steve

gaaq SSC Veteran Points: 204 More actions · Answer 3

fhanlon,

Thanks for the reply, Yes the tables are in the same database but since I moved it over they have still been entering data into the old server tables and not the new one.

newtons2,

Thanks also for the reply, Im not sure about DTS since im a newbie to SQL..:-)And I appreciate the help I will definately take you up on your offer. 🙂

Both of your reply's are welcomed but is there a way for me to move the older tables to add to the new server.

Thanks

Gaaq

fhanlon SSChampion Points: 13131 More actions · Answer 4

I see the problem now. There are the same tables on the old server and the new server but you want the data in the old server to be copied. Do you need to replace the new tables with the old tables or merge the data? If you had a product like Red Gate's SQL Compare or SSW SQL Total Compare this would be fairly quick and easy (you may want to check into this) Other wise if you don't need to merge you could try creating INsert statements from the tables on the old server (LockWood Technology AutoInsert products does this as does Laplas SQLExecMS) Or perhaps best of all see http://www.sqlservercentral.com/scripts/contributions/36.asp for a free script. Cut and paste the INSERT statements on the new server to add the data. Check carefully to ensure all the data is added properly.

Edited by - fhanlon on 12/15/2003 09:13:54 AM

Edited by - fhanlon on 12/15/2003 09:17:16 AM

Francis

gaaq SSC Veteran Points: 204 More actions · Answer 5

fhanlon,

Thank you again for replying. Yes I need to have the old tables that the "warranty people" are still using moved or as you stated merged to the new server. So you recommend using the script or getting the SW thqat you mentioned. Keep in mind im fairly new to SQL. 🙂 So any hand holding would be greatly appreciated. :-))

Thanks

GAAQ

maijoe SSC Enthusiast Points: 140 More actions · Answer 6

maijoe

SSC Enthusiast

Points: 140

December 15, 2003 at 12:21 pm

#486244

DTS in sqlserver can do the job for you.

fhanlon SSChampion Points: 13131 More actions · Answer 7

If your boss is willing to shell out the money for either Red Gate's SQL Compare or SSW SQL Total Compare then this is may be a easy if you are very new to SQL Server. Check out their website, you may even be able to trial the software. Otherwise it is not difficult to use the script I mentioned. Use Query Analyzer to create the Insert statments on the old server. On the new server issue "truncate table" statements and the paste the INSERT statments previously generated to create the new data. As maijoe also suggested DTS can be used. There is a wizard with DTS - see Right click on the Data Transformation Services folder in Enterprise Manager, chose All Tasks the Export (or Import Data) then follow the prompts to copy data from one server to another. Check out Books On Line for more help. Make sure the uses of these tables are not doing any work when you copy the data, then shut down the old server and get everyone to point to the new box.

Edited by - fhanlon on 12/16/2003 07:35:28 AM

Francis

gaaq SSC Veteran Points: 204 More actions · Answer 8

fhanlon,

Thank You very much :-)) I will try it and Let you know the outcome.

Gaaq