Use backup to initialize without drop Publication Subscription 11

Use backup to initialize without drop Publication Subscription

3. Test – For existing Replication which was initialized using snapshot files, remove both publication (Script out Pub) and subscription and try it , which is configured by using snapshot method.

Better script out Publication and distributor.

Both removing only subscription (OR) publication and subscription – both are working.

Drop subscription from the publication and remove the publication. While creating the publication we need to change parameter @sync_method = N’concurrent’ to @sync_method = N’native’

Try add new table in GUI and start the snapshot agent and check –Working

Try adding new table by using sp_addarticle  (Do not run sp_refreshsubscriptions) T-SQL for single article and start the snapshot agent and check – Working

 

Test  For existing Replication which was initialized using snapshot files. Do not remove both publisher and subscriber. DML chnages are not replicating.

 

-- Check and change allow_anonymous & immediate_sync 0 i.e false
use Muthu
EXEC sp_helppublication @publication='Muthu_Replica'


--T-SQL: To enable this publication option we need execute the "sp_changepublication" stored procedure at the Publisher on the publication database.
use Muthu
  EXEC sp_changepublication 
  @publication = 'Muthu_Replica', 
  @property = 'allow_initialize_from_backup', 
  @value = 'True'
  GO 

--When immediate sync is on, all changes in the log are tracked immediately after the initial snapshot is generated even if there are no subscriptions. 
  EXEC sp_changepublication 
  @publication = 'Muthu_Replica', 
  @property = 'immediate_sync', 
  @value = 'True'
  GO 


  
  -- backup the db
  backup database [Muthu] to disk ='\\NODE1\Repl_Snap_files\Muthu_Replica_full.bak' with init
  backup database [Muthu] to disk ='\\NODE1\Repl_Snap_files\Muthu_Replica_diff.bak' with differential,init


  --Go to subscriber

    use master
  restore filelistonly from disk = '\\NODE1\Repl_Snap_files\Muthu_Replica_full.bak'
  
  alter database [Muthu_Replica] set single_user with rollback immediate

  restore database [Muthu_Replica] from disk ='\\NODE1\Repl_Snap_files\Muthu_Replica_full.bak' 
  with 
  move 'Muthu'to'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Muthu_Replica.mdf',
  move 'Muthu_log'to'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Muthu_Replica_log.ldf',
  replace,norecovery

  restore database Muthu_Replica from disk ='\\NODE1\Repl_Snap_files\Muthu_Replica_diff.bak' with recovery


  
/*
Execute the stored procedure sp_addsubscription at the Publisher on the publication database, 
 Specify the following parameter:
 @sync_type = 'initialize with backup',
   @backupdevicetype = 'Disk',
 @backupdevicename = 'Backup Path'
 */

DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = N'Muthu_Replica'; -- change
SET @subscriber = N'Node2'; -- cahnge
SET @subscriptionDB = N'Muthu_Replica'; -- change

--Add a push subscription to a transactional publication.
USE [Muthu]
EXEC sp_addsubscription 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @destination_db = @subscriptionDB, 
  @subscription_type = N'push',
  @sync_type = 'initialize with backup',
  @backupdevicetype = 'Disk',
  @backupdevicename = '\\NODE1\Repl_Snap_files\Muthu_Replica_diff.bak', -- change
  @update_mode = N'read only';



  -- insert a row and validate

  select * from [dbo].[Repl_Tbl1]
  insert into [dbo].[Repl_Tbl1] values (7,'BB',getdate())

 -- to add new table use how to add single artile into existing publication

  create table [dbo].[Repl_Tbl7] (ID int primary key)
  insert into [Repl_Tbl7] values (1)