Best way to move a bunch of SQL Server 2005 tables to another Server?

views:

answers:

+3 Q:

Best way to move a bunch of SQL Server 2005 tables to another Server?

I've been looking for a way to move a bunch of tables, more than 40, to another server with all the data in them. I've looked around for scripts to generate inserts but so far I'd have to run them once for every table, then copy all the scripts over and then run them on the server. Seems like there is a better way.

--Update-- My strategy for doing this may have been for naught. The end script, using MS SQL Server Publishing Wizard and Red Gates SQL Data Compare (excellent tool, btw) results in a file over 1GB. This makes my system plead for mercy and I'm not willing to risk crashing a clients server just opening the file to run it. I may have to rethink this whole thing and break down to just individual per table scripts. I'm not looking forward to that.

Ever used SQL Server Integration Services? (SSIS)?

Dave Markle 2010-02-09 00:01:47

Have you looked at SQL Compare? http://www.red-gate.com/products/SQL_Compare/index.htm

Mr-sk 2010-02-09 00:03:04

You'd need to use SQL Compare and SQL Data Compare. Depending on the complexity and volume of the data I'd guess that a backup/restore would be much less tedious (even if these tools do do a lot of the work for you, and I use them for other tasks all the time, just not this kind).

Aaron Bertrand 2010-02-09 00:15:58

Excellent tool. I highly recommend this if you have the cash. Does have a 14 day full trial.

Mikecancook 2010-02-09 16:52:56

+4 A:

If You're Creating a New DB on your other Server:

Have you tried just doing a Backup and Restore?

On the first Server, Backup the DB to a .bak file.

Copy that file over to your new server.

Do a restore using the .bak file

If You're Updating the DB on the other Server.

Try SQLExaminer, it's one of my must have tools. http://www.sqlaccessories.com/SQL_Examiner/

andy 2010-02-09 00:03:25

any reason for the downvote?

andy 2010-02-09 00:06:51

I don't see a reason for the down vote either. The OP didn't mention that this was anything more than a one-time transfer. I use the backup/restore all the time.

aaaa bbbb 2010-02-09 00:11:24

I would also have opted for Backup/Restore. Drive-by down-voting is so cowardly, I wish there wasn't a way to do so anonymously.

Aaron Bertrand 2010-02-09 00:14:46

yeah, weird huh. On 2nd thoughts on his options, you guys are right, actually you could use the Backup/Restore option even if you were updating... if you're ok with a full overwrite... to keep it simple.

andy 2010-02-09 00:17:09

The server is at remote location. I can't just transfer it unless I can take just these tables. And I can't overwrite. I need just these tables.

Mikecancook 2010-02-09 00:18:30

@mikecancook. I see, have you looked at SQLExaminer? It's a really good tool. You could use it to generate all the Schema scripts... to create the appropriate tables on the new server. This way you could deal with the schema and data separately, which might be nice since there's so much data. Also, you can use SQLExaminer to generate a script to copy all data from multiples tables at once.

andy 2010-02-09 00:21:43

some options...

sql server import export wizard, just right click a database on either server.
BCP OUT then IN
Linked Server
ODBC type solution

for 40 tables i'd use the import/export wizard, it will give you a list of tables to export, very simple.

Paul Creasey 2010-02-09 00:04:24

Unless I'm mistaken my only options are flat files and excel since I'm transferring this to a remote server. I've used Import/Export on the same server but not like this.

Mikecancook 2010-02-09 00:21:43

You could export the tables you want and all of each tables content using the SQL Server Database Publishing Wizard. This would give you a .sql file that you can easily upload and run on the new server.

The link to download and install from MS is over here - http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

Richard Reddy 2010-02-09 00:20:22

Try SQL Examiner - database comparison and synchronization tool

SQLDev 2010-05-13 19:31:06

ansaurus

tags:

views:

answers:

Best way to move a bunch of SQL Server 2005 tables to another Server?

If You're Creating a New DB on your other Server:

If You're Updating the DB on the other Server.

related questions