SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Adding an Article Without a Full Snapshot

By Nick Burns,

Have you got transactional replication in your SQL environment? Do you need to add a new table to your publication, but can't afford the time necessary to create a full new snapshot? Here is a step-by-step guide. Thankfully, adding a single table is easier than I thought.

Here we will use my playbox database, WorldOfMayhem. Currently, the players and the ticktick tables are replicated. We will add the dragons table and the weapons table.

Setup - My Test Replication Environment


First, I will show you what happens if you just go and add this table to the publication articles.

Let's add the weapons table. Right click your publication, go to articles and untick the option to only show included articles. We are going to tick the weapons table and then click OK. This is shown below.

Full Snapshot Example


Now, right click your publication. Go to "View Snapshot Agent Status" and start the snapshot agent. If you head to the Replication Monitor you will see the following:


Look closely at this screenshot. The nice thing is that only the weapons data has been applied to the subscription, you can see this in Replication Monitor. However, the Snapshot Agent Monitor clearly shows that a snapshot of 3 articles was generated. And if you look in your snapshot folder, you can see that all three articles are there - and these files are bcp files and contain the table data.

The good news, is that even though all the articles were part of the snapshot, only the weapons table is actually pushed over to the subscriber. So if you have a local snapshot folder, this isn't a terrible solution. But, if your snapshot folder is on a remote drive then you are potentially going to push a lot of data over the network.

Let's fix this so that the full snapshot is not necessary.

Step 1 Change the publication properties

All you have to do is disable 2 publication properties: allow_anonymous and immediate_sync. Here's how:

exec sp_changepublication
      , @property=N'allow_anonymous'
      , @value='false';
exec sp_changepublication
      , @property=N'immediate_sync'
      , @value='false';

Step 2 Add your article

Now go back to the properties of the publication and add in a new article. This time, we will add the dragons table. This is the same as the procedure shown above for adding the weapons table.

Step 3 Start the snapshot agent

Once you have added your article, right click the publication again and select "View Snapshot Agent Status" and start the snapshot agent. Fire up Replication Monitor and head to your snapshot folder. You will see a very different scenario this time. There is only one table in the snapshot folder.


And that's all there is to it. Very quick, low impact and makes for a happier change team in the small hours of the morning.

Update - What Didn't Work

Thought I should add a note about what didn't work when I was trying to do this. Using the GUI was the key. When I tried to add the article to the publication via stored procedures I got a number of different errors.

1) using sprocs, if I did not drop the subscription, then I would get an error when I went to run the snapshot agent. It didn't not pick up that there had been changes to the publication. There is probably a way around this, but I didnt' find it.

2) when I did drop the subscription, then add the article to the publication, then recreate the subscription -> a full snapshot was generated. No good.

Total article views: 3220 | Views in the last 30 days: 8
Related Articles

Snapshot Agent in Transactional Replication

Function of Snapshot Agent in Transactional Replication


snapshot publication - time out expired

snapshot publication - time out expired


Replication Gotcha - Including An Article In Multiple Publications

When administering replication topologies it's common to group articles into publications based on r...


Snapshot Replication

Now that Andy has converted Steve Jones to a DMO believer we asked him to spend some time discussing...


Replication Publication issue

Replication Publication issue