Remove only subscription Recreate New Reinitialize 10

Test 2 For existing Replication which was initialized using snapshot files, remove only subscription and test.

Try with existing publication initialize from backup and add table from GUI and T-SQL.

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

Note: Only issue if we leave all tables in subscriber database after the restoration, if you want to replicate later remaining tables that’s not replicating it.

If you drop tables in subscriber and add it in the replication it is working.

Add only single table method – Working

Add only table using normal GUI and start the snapshot agent – Working

-- 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)