Using the Replication Manager SQL Server 2000

The SQL Replication Manager is used to copy data from one database to another.

You can use the SQL Replication Manager to either push or pull data to or from another database.

If you currently use the Replication Manager and you want to add tables to it, you first will need to remove it and recreate it.

Removing the Replication

1) Select the Database Name (that does not say local) and Select the Tools Menu->Replication->Disable Publishing.

2) Follow the steps to disable the publishing. The Replication Monitor folder should disappear.

3) Now you need to remove all the publications and subscriptions pointing to this replication.

a) Locate all servers that might be using this Replication and open the Replication Folder.

b) Select the Publications folder and Subscriptions folder. If you see any items listed in these two folders, you will need to remove them. To remove them, open query analyzer and run this store procedure exec sp_removedbreplication '' where “DB Name Here” is the name of the database listed under the Database column for the subscriptions or publications. Refresh the database and verify that the Publication and Subscription has been removed.

4) Your replication has been removed.

Adding Replication


1) You might want to drop all the tables first before replicating.

2) Select the database and select tools->Replication->Create and Manage Publications

3) At the publication wizard select Next after selecting show advanced options

4) Select your database and press next.

5) Select snapshot and press next, Press Next, Select No, Subscribers receive data directly and press Next.

6) Select Servers running SQL server 2000 and press next

7) Select the tables you will need to copy over to the new database.

8) Name the publication

9) Select No, create the publication as specified and press next

10) Select Finish and you should see SQL Server Enterprise Manager successfully etc... Press Close

11) You will now be forwarded to create the subscription. Select Push subscription. This subscription will push the data over to the new database as it duplicates the data.

12) Select the name of your server and press Next.

13) Run the agent at the Distributor and select next

14) Select Using the following schedule and change it to say Occurs once at 12:00AM every day (or when ever yoiu need the data copied) press next

15) Select Yes, initialize the schema and data and press next

16) Press next

17) Press finish

18) Verfiy that the tables from step 7 are created and populated in the new database. If the tables aren’t there, you might need to select the Distribution Agents, and right mouse button on the Distribution Agent and select start. Refresh the database and you should see the tables created and populated with data.