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.
Best Answer
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.