Transaction Replication:Intialize from Backup

Question

I have a database which i want to replicate to another server. Since the database is huge(~1.5 TB) i have decided to do initialize from backup. But since it is a production live database,new insert/updates happen all the time. I have done intialiaze from backup from a full backup earlier. I want to know if i can implement transactional replication from a full backup + differential backup.

So when i create subscriber using the below script,do i have to give the differential.bak as the backupdevicename or the fullbackup file name.

-----------------BEGIN: Script to be run at Publisher 'DESKTOP-K0UF973\DEMOSERVER'-----------------
use [AdventureWorks2012]
exec sp_addsubscription @publication = N'Demo_pub', @subscriber = N'DESKTOP-K0UF973\SQLTEST', @destination_db = N'AdventureWorks2012', @sync_type = N'initialize with backup', 
@subscription_type = N'pull', @update_mode = N'read only',@backupdevicetype=N'disk',
@backupdevicename=N'C:\Rep\Replication_Scripts\differential.bak'
GO
-----------------END: Script to be run at Publisher 'DESKTOP-K0UF973\DEMOSERVER'-----------------



-----------------BEGIN: Script to be run at Subscriber 'DESKTOP-K0UF973\SQLTEST'-----------------
use [AdventureWorks2012]
exec sp_addpullsubscription @publisher = N'DESKTOP-K0UF973\DEMOSERVER', @publication = N'Demo_pub', @publisher_db = N'AdventureWorks2012', 
@independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', 
@immediate_sync = 0

exec sp_addpullsubscription_agent @publisher = N'DESKTOP-K0UF973\DEMOSERVER', @publisher_db = N'AdventureWorks2012', 
@publication = N'Demo_pub', @distributor = N'DESKTOP-K0UF973\SQLTEST', @distributor_security_mode = 1, 
@distributor_login = N'', @distributor_password = null, @enabled_for_syncmgr = N'False', @frequency_type = 64, 
@frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, 
@frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, 
@active_start_date = 20180731, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', 
@use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0
GO
-----------------END: Script to be run at Subscriber 'DESKTOP-K0UF973\SQLTEST'-----------------

Since it is a live database,i cannot make the database into single user mode and take full backup and initialize the subscriber.I have trans logs also being taken every hour.So i am planning to restore recent full backup + differential to subscriber and will disable trans log backup job temporarily. Is this a correct approach.? if anyone encountered similar situation ,please let me know.

Answer 1

You don't have to take the publisher offline when you initialize from a backup. Just like initializing from a snapshot, the initialization just needs to bring the subscriber database to a point-in-time within the Distribution Retention Period, and to configure the subscription with the LSN to start from.

Perhaps you're thinking of Manual Initialization which requires you to synchronize the subscriber before beginning repilication.

Answer 2

Let this be helpful to someone who will have to deal with huge database and have to implement replication without initialization. I had set up transactional replication without initialization using backup earlier ,before my database was live.So i was not worried.Recently when i tried to add few articles together it caused a deadlock and my subscriber became inactive with the below message.

The subscription to this publication is not active yet. No user action is required

I researched about this and everybody was pointing me towards dropping the subscription and create a new one,because most of them use snapshot to initialize.But since i use initialize with a backup,and it is production environment(with it's share of performance issues) i was worried. But anyways i had no other way but to drop the subscriber and create a new one and internalize with backup.

For me most recent full backup was previous night's full backup. I had publication already setup with initialize from backup set to true. Then i restored the full backup followed by all transaction log backup. After restoring the database in subscriber,i added subscriber using the below script.

-----------------BEGIN: Script to be run at Publisher 'DESKTOP-K0UF973\DEMOSERVER'-----------------
use [AdventureWorks2012]
exec sp_addsubscription @publication = N'Demo_pub', @subscriber = N'DESKTOP-K0UF973\SQLTEST', @destination_db = N'AdventureWorks2012', @sync_type = N'initialize with backup', 
@subscription_type = N'pull', @update_mode = N'read only',@backupdevicetype=N'disk',
@backupdevicename=N'C:\Rep\Replication_Scripts\last_trans_log.trn'
GO
-----------------END: Script to be run at Publisher 'DESKTOP-K0UF973\DEMOSERVER'-----------------



-----------------BEGIN: Script to be run at Subscriber 'DESKTOP-K0UF973\SQLTEST'-----------------
use [AdventureWorks2012]
exec sp_addpullsubscription @publisher = N'DESKTOP-K0UF973\DEMOSERVER', @publication = N'Demo_pub', @publisher_db = N'AdventureWorks2012', 
@independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', 
@immediate_sync = 0

exec sp_addpullsubscription_agent @publisher = N'DESKTOP-K0UF973\DEMOSERVER', @publisher_db = N'AdventureWorks2012', 
@publication = N'Demo_pub', @distributor = N'DESKTOP-K0UF973\SQLTEST', @distributor_security_mode = 1, 
@distributor_login = N'', @distributor_password = null, @enabled_for_syncmgr = N'False', @frequency_type = 64, 
@frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, 
@frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, 
@active_start_date = 20180731, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', 
@use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0
GO
-----------------END: Script to be run at Subscriber 'DESKTOP-K0UF973\SQLTEST'-----------------

You can see that @backupdevicename=N'C:\Rep\Replication_Scripts\last_trans_log.trn'. it worked.

I was hoping to use a differential backup after recent full backup,but for some weird reasons,i got message that the full back up was not set correctly for the differential.I even checked LSN of the full backup from the differential logs. If someone succeeds with differential it will be great.