May 5, 2008 at 4:56 pm
Hi guys,
Can anyone describe the steps necessary to restore a transactional replication via scripts only. Specifically I'd like to restore replication without using a snapshot...or backup for that manner. I have custom replication procedures that alter the data going over to the subscriber databases...therefore they should not be overwritten by the snapshot upon restarting the replication.
Just as an example, say you want a subscriber database to act as an archive...therefore the sp_MSdel_tablename stored procedures have been modified to return 1 instead of deleting rows. How would one stop replication, restore from backup or do maintenance and then restart replication without loosing data?
I believe you could use the sp_addsubscription @sync_type = N'none' to accomplish this, but I have been unsuccessful as of yet. Anyone scripted this out before?
use X
GO
exec sp_addsubscription
@publication = N'Replication',
@subscriber = N'HQ15',
@destination_db = N'W',
@subscription_type = N'Push',
@sync_type = N'none',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0
Thanks in advance for you help.
May 12, 2008 at 12:55 pm
Just in case anyone else comes across this same problem. Besides running the above script to restart replication you also have to manually create all of the replication stored procedures. Fortunately I created a script to archive these prior to stopping the replication:
declare @sp-2 varchar(30), @text varchar(4000), @count int, @colid int, @id int
select @count = 0
DECLARE spnames cursor for (select name from sysobjects where type='P' and name like 'sp_MS%' )
open spnames
fetch next from spnames into @sp-2
While (@@FETCH_STATUS = 0)
BEGIN
select @count=@count+1
select @colid=1
select @id = (select id from sysobjects where name=@sp)
while (select max(colid) from syscomments where id = @id)>=@colid
BEGIN
select @text= (select text from syscomments where id = @id and colid=@colid)
print @text
select @colid=@colid+1
END
print 'go'
print ' '
FETCH NEXT FROM spnames INTO @sp-2
END
print '-----------------------------------'
print '-- View Count: '+cast(@count as varchar)
print '-----------------------------------'
CLOSE spnames
DEALLOCATE spnames
Sometimes the output has to be reformatted due to word wrapping but all the data is there...enjoy.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply