hi

I simply want to add an article to an existing publication WITHOUT creating a new snapshot of the entire database, but rather just a snapshot of the new items.

I can add the article just fine either through the EM or sp_addarticle, but when I come to start the snapshot agent, it copies out the enitre DB. This DB is 150GB and the subscriber is in France (im in UK) so im going to be old(er) and grey(er) by the time the snapshot is copied over.

how do I accomplish this simple, yet seemingly utterly impossible task (as I have been looking and posting all over the place.)

Thanks

Alastair Jones.

it's really very easy .. here's an example for sql2k adding a table to transactional replication:-

--
-- add table to publication
--
exec dbo.sp_addarticle 'mypublication' ,@article='mytable',@source_table='mytable',@destination_table='mytable',
@force_invalidate_snapshot=1
go
--
-- refresh the subscription
--
exec dbo.sp_refreshsubscriptions  'mypublication'
go
--
--
-- now run the snapshot agent which will only update the object changed/ added new object
--
exec msdb.dbo.sp_start_job @job_name='xxxx 'mypublication'
go
--

get the name of  'mypublication'

 --

-- execute within the published database e.g. the database being replicated

-- to obtain the publication name

--

exec dbo.sp_helppublication

go

ill buy you a beer if it works - cant test it at the moment as my box is being hammered by a previous attempt that then snapped the entire dbase.

cheers

Alastair Jones.

As I cant qualify

exec msdb.dbo.sp_start_job @job_name='xxxx

with the actual job name, I run this section alone from the EM...and I get a snap of the entire database....

can you tell me how to fine the job name to start the agent from QM so I can see if this makes a difference...also im running sql 2005 - dont know if that makes a difference in the SP's to run...

Thanks

Alastair Jones

You can also just add the article through management studio.  Publication properties -- Articles.

It prompts you that a new snapshot must be generated, but only the change(s) will be applied to the subscriber.

alistair - sorry for delay have responded!

you could probably do it through the GUI but it's not an auditable process within a controlled production environment so no use to me other than testing.

ps. this takes out an object

--do this in the publishing database

exec dbo.sp_dropsubscription  @publication='mypublication',@article='myobjecttodrop',
@subscriber='MYSERVER'
exec dbo.sp_droparticle @publication='mypublication',@article='myobjecttodrop',
@force_invalidate_snapshot=1
exec dbo.sp_refreshsubscriptions  'mypublication'
go
--