Environment

-> Below queries will be used to create the required database and objects on Publisher JBRESEARCH\IN2019,

create database JBDB
GO
use [JBDB]
GO
create table Table1(
SNO INT PRIMARY KEY ,
SNAME VARCHAR(255))
GO
create table Table2(
SNO INT PRIMARY KEY ,
SNAME VARCHAR(255))
GO
create table Table3(
SNO INT PRIMARY KEY ,
SNAME VARCHAR(255))
GO
insert into Table1 values (1,'A')
GO
insert into Table2 values (1,'A')
GO
insert into Table3 values (1,'A')
GO

-> Configure Distribution using below queries on Publisher JBRESEARCH\IN2019,

use master
exec sp_adddistributor @distributor = N'JBResearch\IN2019', @password = N'Pa$$W0rd'
GO
exec sp_adddistributiondb @database = N'distribution'
, @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL15.IN2019\MSSQL\Data'
, @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL15.IN2019\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72
, @history_retention = 48, @deletebatchsize_xact = 5000, @deletebatchsize_cmd = 2000, @security_mode = 1
GO
exec sp_adddistpublisher @publisher = N'JBResearch\IN2019', @distribution_db = N'distribution', @security_mode = 1
, @working_directory = N'\\JBRESEARCH\Backup', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO

-> Configure Publication using below query on Publisher JBRESEARCH\IN2019,

use [JBDB]
exec sp_replicationdboption @dbname = N'JBDB', @optname = N'publish', @value = N'true'
GO

use [JBDB]
EXEC sp_addpublication
@publication = N'JBDBREPL',
@status = N'active',
@allow_push = N'true',
@allow_pull = N'true',
@independent_agent = N'true',
@immediate_sync = N'true',
@allow_initialize_from_backup = N'true'
GO

EXEC sp_addarticle @publication = N'JBDBREPL',@article =N'Table1',@source_object =N'Table1'
GO
EXEC sp_addarticle @publication = N'JBDBREPL',@article =N'Table2',@source_object =N'Table2'
GO
EXEC sp_addarticle @publication = N'JBDBREPL',@article =N'Table3',@source_object =N'Table3'
GO

-> Checking the available data on objects Table1, Table2 and Table3 on Publisher JBRESEARCH\IN2019.

select * from JBDB..Table1
GO
select * from JBDB..Table2
GO
select * from JBDB..Table3

-> Backup JBDB database using below query on Publisher JBRESEARCH\IN2019,

use master
backup database JBDB to disk = 'c:\temp\JBDB_14OCT2020.bak' with STATS=1

-> Let us add some new data using below query after the database backup on Publisher JBRESEARCH\IN2019. We are adding new data to check if replication picks up these data too after replication configuration. In real world scenario there is high possibility that new data will be added when we are performing backup on the publisher and then restore it in subscriber.

use [JBDB]
GO
insert into Table1 values (2,'B')
GO
insert into Table2 values (2,'B')
GO
insert into Table3 values (2,'B')
GO
insert into Table1 values (3,'C')
GO
insert into Table2 values (3,'C')
GO
insert into Table3 values (3,'C')
GO

-> Checking the available data on objects Table1, Table2 and Table3 on Publisher JBRESEARCH\IN2019.

select * from JBDB..Table1
GO
select * from JBDB..Table2
GO
select * from JBDB..Table3
GO

-> Restore database JBDBSub on the Subscriber SQL Server Instance JBRESEARCH\IN2019_1 using the backup that was performed on Publisher server JBRESEARCH\IN2019.

USE [master]
RESTORE DATABASE [JBDBSub] FROM DISK = N'C:\Temp\JBDB_14OCT2020.bak' WITH FILE = 1
, MOVE N'JBDB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.IN2019_1\MSSQL\DATA\JBDB.mdf'
, MOVE N'JBDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.IN2019_1\MSSQL\DATA\JBDB_log.ldf', NOUNLOAD, STATS = 1
GO

-> Execute below query to get LastLSN details from the database backup that was performed on Publisher server JBRESEARCH\IN2019.

declare @Backupfile nvarchar(1000)
set @Backupfile = 'c:\temp\JBDB_14OCT2020.bak'
declare @sql nvarchar(1000)
declare @LASTLSN numeric(25,0)
create table #BackupLSNDetails
(
BackupName nvarchar(128),
BackupDescription nvarchar(255),
BackupType smallint,
ExpirationDate datetime,
Compressed bit,
Position smallint,
DeviceType tinyint,
UserName nvarchar(128),
ServerName nvarchar(128),
DatabaseName nvarchar(128),
DatabaseVersion int,
DatabaseCreationDate datetime,
BackupSize numeric(20, 0),
FirstLSN numeric(25, 0),
LastLSN numeric(25, 0),
CheckpointLSN numeric(25, 0),
DatabaseBackupLSN numeric(25, 0),
BackupStartDate datetime,
BackupFinishDate datetime,
SortOrder smallint,
[CodePage] smallint,
UnicodeLocaleId int,
UnicodeComparisonStyle int,
CompatibilityLevel tinyint,
SoftwareVendorId int,
SoftwareVersionMajor int,
SoftwareVersionMinor int,
SoftwareVersionBuild int,
MachineName nvarchar(128),
Flags int,
BindingId uniqueidentifier,
RecoveryForkId uniqueidentifier,
Collation nvarchar(128),
FamilyGUID uniqueidentifier,
HasBulkLoggedData bit,
IsSnapshot bit,
IsReadOnly bit,
IsSingleUser bit,
HasBackupChecksums bit,
IsDamaged bit,
BeginsLogChain bit,
HasIncompleteMetaData bit,
IsForceOffline bit,
IsCopyOnly bit,
FirstRecoveryForkID uniqueidentifier,
ForkPointLSN numeric(25, 0),
RecoveryModel nvarchar(60),
DifferentialBaseLSN numeric(25, 0),
DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription nvarchar(60),
BackupSetGUID uniqueidentifier,
CompressedBackupSize bigint,
Containment tinyint,
KeyAlgorithm nvarchar(32),
EncryptorThumbprint varbinary(20),
EncryptorType nvarchar(32)
)
set @sql= 'RESTORE HEADERONLY FROM DISK = '''+@Backupfile+''''
insert into #BackupLSNDetails
exec (@sql)
select @LASTLSN = LastLSN from #BackupLSNDetails
DROP table #BackupLSNDetails
declare @numericlsn numeric(25,0)
declare @high4bytelsncomponent bigint,@mid4bytelsncomponent bigint,
@low2bytelsncomponent int
--set the lsn here
set @numericlsn = @LASTLSN
select @high4bytelsncomponent = convert(bigint, floor(@numericlsn / 1000000000000000))
select @numericlsn = @numericlsn - convert(numeric(25,0), @high4bytelsncomponent) * 1000000000000000
select @mid4bytelsncomponent = convert(bigint,floor(@numericlsn / 100000))
select @numericlsn = @numericlsn - convert(numeric(25,0), @mid4bytelsncomponent) * 100000
select @low2bytelsncomponent = convert(int, @numericlsn)
SELECT convert(binary(4), @high4bytelsncomponent) + convert(binary(4), @mid4bytelsncomponent) + convert(binary(2), @low2bytelsncomponent) as 'LastLSN_To_Be_Used_In_@subscriptionlsn'

-> I see it to be 0x00000025000009300001 in my case.

-> Create the push subscription on Publisher server JBRESEARCH\IN2019 using below query.

USE [JBDB]
GO
EXEC sp_addsubscription
@publication = N'JBDBREPL',
@subscriber = N'JBResearch\IN2019_1',
@destination_db = N'JBDBSub',
@sync_type= N'initialize from lsn',
@backupdevicetype='DISK',
@backupdevicename=N'C:\Temp\JBDB_14OCT2020.bak',
@subscription_type = N'push',
@subscriptionlsn =0x00000025000009300001,
@update_mode = N'read only'
GO

exec sp_addpushsubscription_agent
@publication = N'JBDBREPL',
@subscriber = N'JBResearch\IN2019_1',
@subscriber_db = N'JBDBSub',
@subscriber_security_mode = 1,
@dts_package_location = N'Distributor'
GO

-> Verify replication monitor to see there are no failures. Query the involved objects on either side to check if data’s are in sync,

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.