Best method for automating transfer of a single table between remote SQL servers (2005)

Question

Post reply

Best method for automating transfer of a single table between remote SQL servers (2005)

mattb 14774

Valued Member

Points: 74
More actions
June 29, 2010 at 4:35 pm

#143842

I recently installed an SQL server in our warehouse on the other side of the country. It is housing an inventory database and some others. I'm trying to determine the best way to automate a nightly, or even weekly is good enough, copy of a single table from our local server to the remote one. Both run SQL server 2005 standard.
I'd also like to be able to trigger the update manually.
It takes just under 2 minutes to do a manual import of the table from server to server using the management studio.
Appreciate any tips!

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

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

If you use the copy table wizard you can save the process as a scheduled SQLAgent job.

---------------------------------------------------------------------

Steve Jones - SSC Editor SSC Guru Points: 741475 More actions · Answer 2

The copy wizard is a good choice. If this might grow, snapshot replication will work as well.

mattb 14774 Valued Member Points: 74 More actions · Answer 3

Alright bear with me here. I've found the database copy utility, but doesn't let me specify an individual table.

I've been able to save the package when doing a database export / import where I am able to specify the table. I haven't found where that package is saved yet.

Is there yet a separate copy table wizard that I am missing? Thank you.

Steve Jones - SSC Editor SSC Guru Points: 741475 More actions · Answer 4

Not copy database, copy table.

In 2005 I think under SSMS, right click a database, say Export or Import data. I don't have a 2005 instance handy, but check for a copy table task in SSIS as well.

mattb 14774 Valued Member Points: 74 More actions · Answer 5

Hmm, nope can't find any copy table wizard. I'll play around with trying to schedule an import/export package.

george sibbald SSC Guru Points: 104210 More actions · Answer 6

right click database

tasks

export data

at the end choose to save it as a SSIS package

then you can schedule that package via SQLAgent

Is this not the way you did it manually in SSMS?

---------------------------------------------------------------------

mattb 14774 Valued Member Points: 74 More actions · Answer 7

george sibbald (6/30/2010)
right click database
tasks
export data
at the end choose to save it as a SSIS package
then you can schedule that package via SQLAgent
Is this not the way you did it manually in SSMS?

Yes I've used the export wizard many times, I just hadn't played around with the SSIS packages or scheduling them before. Thanks!