I'm lost. We have Sql Server 2005 on a remote box (A) and the local pc (B). Both servers have a single database on them. We've been copying the database A to B for some time and its becoming tiresome. I've heard of replication but couldnt figure out
what the MS site was saying.
How do i go about making a differential copy of the A database onto B?
Thanks
-
-
How much data needs to be transfered?
Can't you make use of linked servers and write a stored procedure to transfer the data? Something like
INSERT table_b(a,b,c)
SELECT a, b, c
FROM serverB.dbB.dbo.table_a
Or is it too much data/tables to write sp for? -
- Open up SQL Server Management Studio
- Connect to Server 1 (the database you want copied)
- Expand the server folder structure you will see Replication
- Expand the replication folder
- Rght click and create a new publication under Local Publications
- Once complete you can configure the distribution by right clicking on the replication folder
- Connect to server 2 (where you want the database copied)
- Under the replication folder if it has not already created a subscription to the publication on Server 1 create it.
Creating a linked server should only be used if you need to do cross server queries.
If he just wanted to copy the data fresh everytime you could just setup a dts job that runs x number of times per day.
The option is up to you. The replication will be faster because it will transfer less data. DTS will be the whole package everytime.
Thread Closed
This thread is kinda stale and has been closed but if you'd like to continue the conversation, please create a new thread in our Forums,
or Contact Us and let us know.