Problem
There are situations where you might have a large SQL Server database that you need to replicate. Since the database is very large we want to initialize the Subscriber from the SQL Server backup and not use a snapshot. How do we bundle all the steps together to achieve this without performing them manually?
Solution
I want to cover the basic steps needed to setup a “Push Type Transactional Replication”. This tip assumes you already have an understanding of SQL Server Transactional Replication and I will show how to bundle all the steps into one process.
Before we go get started, let’s cover some acronyms/abbreviations used throughout the discussion.
- PubServer – Publisher Server
- DtbServer – Distribution Server
- SubServer – Subscriber Server
Assumptions/Notes: Publisher, Distributor and Subscriber are identified and configured correctly.
First create a batch file (SetupReplSteps.bat) like below. This will be the calling process that will call each individual script for each step of the process. If you review this you can see that I have 8 steps that will be run and they are discussed in more detail below.
echo ' ' > C:\ReplSteps\RepLog.txt
echo *********************** Step 1 *********************** >> C:\ReplSteps\RepLog.txt
SQLCMD -S"PubServer" -E -i"C:\ReplSteps\1_CreatePublication.sql" >> C:\ReplSteps\RepLog.txt
echo ' ' >> C:\ReplSteps\RepLog.txt
echo *********************** Step 2 *********************** >> C:\ReplSteps\RepLog.txt
SQLCMD -S"DtbServer" -E -i"C:\ReplSteps\2_DisableDistCleanAgentJob.sql" >> C:\ReplSteps\RepLog.txt
echo ' ' >> C:\ReplSteps\RepLog.txt
echo *********************** Step 3 *********************** >> C:\ReplSteps\RepLog.txt
SQLCMD -S"PubServer" -E -i"C:\ReplSteps\3_BackupPublisherDatabase.sql" >> C:\ReplSteps\RepLog.txt
echo ' ' >> C:\ReplSteps\RepLog.txt
echo *********************** Step 4 *********************** >> C:\ReplSteps\RepLog.txt
SQLCMD -S"SubServer" -E -i"C:\ReplSteps\4_CopyBakFileFromPubToSub.sql" >> C:\ReplSteps\RepLog.txt
echo ' ' >> C:\ReplSteps\RepLog.txt
echo *********************** Step 5 *********************** >> C:\ReplSteps\RepLog.txt
SQLCMD -S"SubServer" -E -i"C:\ReplSteps\5_RestorePubDatabaseToSub.sql" >> C:\ReplSteps\RepLog.txt
echo ' ' >> C:\ReplSteps\RepLog.txt
echo *********************** Step 6 *********************** >> C:\ReplSteps\RepLog.txt
SQLCMD -S"SubServer" -E -i"C:\ReplSteps\6_DisableAllDbTriggersOnSub.sql" >> C:\ReplSteps\RepLog.txt
echo ' ' >> C:\ReplSteps\RepLog.txt
echo *********************** Step 7 *********************** >> C:\ReplSteps\RepLog.txt
SQLCMD -S"PubServer" -E -i"C:\ReplSteps\7_AddSubscriptionOnPub.sql" >> C:\ReplSteps\RepLog.txt
echo ' ' >> C:\ReplSteps\RepLog.txt
echo *********************** Step 8 *********************** >> C:\ReplSteps\RepLog.txt
SQLCMD -S"DtbServer" -E -i"C:\ReplSteps\8_StartDistribCleanupAgent.sql" >> C:\ReplSteps\RepLog.txt
It is a good practice to log the execution result of all the steps for troubleshooting purposes. Hence, I have re-directed the output to a text file “RepLog.txt”. Adjust the above code as needed for your environment.
Once the .bat file is laid out, the steps become self explanatory.
Step 1: Script your publication
The easiest way to do this is to use SSMS to create the publication and then script out the code or if you prefer you can write the code yourself. In the below example I create a publication and added one article to replicate.
USE [DBname]
GO
EXEC sp_replicationdboption @dbname = N'DBname'
,@optname = N'publish'
,@value = N'true'
GO
-- Adding the transactional publication
USE [DBname]
GO EXEC sp_addpublication @publication = N'DBname_Repl'
,@description = N'Transactional publication of database ''DBname'' from Publisher ''PHLSQL''.'
,@sync_method = N'concurrent'
,@retention = 0
,@allow_push = N'true'
,@allow_pull = N'true'
,@allow_anonymous = N'false'
,@enabled_for_internet = N'false'
,@snapshot_in_defaultfolder = N'true'
,@compress_snapshot = N'false'
,@ftp_port = 21
,@allow_subscription_copy = N'false'
,@add_to_active_directory = N'false'
,@repl_freq = N'continuous'
,@status = N'active'
,@independent_agent = N'true'
,@immediate_sync = N'false'
,@allow_sync_tran = N'false'
,@allow_queued_tran = N'false'
,@allow_dts = N'false'
,@replicate_ddl = 1
,@allow_initialize_from_backup = N'true'
,@enabled_for_p2p = N'false'
,@enabled_for_het_sub = N'false' GO
EXEC sp_addpublication_snapshot @publication = N'DBname_Repl'
,@frequency_type = 1
,@frequency_interval = 1
,@frequency_relative_interval = 1
,@frequency_recurrence_factor = 0
,@frequency_subday = 8
,@frequency_subday_interval = 1
,@active_start_time_of_day = 0
,@active_end_time_of_day = 235959
,@active_start_date = 0
,@active_end_date = 0
,@job_login = NULL
,@job_password = NULL
,@publisher_security_mode = 1 GO
USE [DBname]
GO
EXEC sp_addarticle @publication = N'DBname_Repl'
,@article = N'ACL'
,@source_owner = N'dbo'
,@source_object = N'ACL'
,@type = N'logbased'
,@description = NULL
,@creation_script = NULL
,@pre_creation_cmd = N'drop'
,@schema_option = 0x000000000803509F
,@identityrangemanagementoption = N'manual'
,@destination_table = N'ACL'
,@destination_owner = N'dbo'
,@vertical_partition = N'false'
,@ins_cmd = N'CALL sp_MSins_dboACL'
,@del_cmd = N'CALL sp_MSdel_dboACL'
,@upd_cmd = N'SCALL sp_MSupd_dboACL' GO
Note I have set @allow_initialize_from_backup = N’true’. This will let us use the backup to initialize replication.
The above code for your publication should be saved in a file called “1_CreatePublication.sql”.
Step 2: Disable the replication clean up job
The next step will disable the replication cleanup job.
USE distribution
GO EXEC msdb..sp_update_job @job_name = 'Distribution clean up: distribution', @enabled = 0 GO EXEC msdb..sp_stop_job @job_name = 'Distribution clean up: distribution' GO
Note: We have to do this in our Production environment to ensure if there are issues in the subsequent steps, the transactions are not deleted from the Distributor. This relates to the Minimum Retention time parameter configured on the Distributor.
The above code should be saved in a file called “2_DisableDistCleanAgentJob.sql”.
Step 3: Backup the Publisher Database
The next step is to create a backup of the published database.
USE master GO BACKUP DATABASE DBname TO DISK = 'T:\DBname.bak' GO
The above code for your database should be saved in a file called “3_BackupPublisherDatabase.sql”.
Step 4: Move the backup file to the Subscriber
We now need to copy the backup file from the publisher to the subscriber server. I am using xp_cmdshell and XCOPY to copy the backup. I am also turning on xp_cmdshell using sp_configure and then turning it off at the end.
sp_configure 'xp_cmdshell', 1 GO reconfigure GO EXEC master..xp_cmdshell 'XCOPY "\\PubServer\T$\DBname.bak" "\\SubServer\G$" /J /Y' GO sp_configure 'xp_cmdshell', 0 GO reconfigure GO
The above code for your database backup should be saved in a file called “4_CopyBakFileFromPubToSub.sql”.
Step 5: Restore the database on the Subscriber Server
Now we need to restore the database backup on the subscriber. If the database already exists on the subscriber I am taking it offline and then doing the restore.
USE master GO IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'DBname') ALTER DATABASE DBname SET OFFLINE WITH ROLLBACK IMMEDIATE GO USE master GO RESTORE DATABASE DBname FROM DISK = '\\SubServer\G$\DBname.bak' WITH MOVE 'DBname' TO 'R:\MSSQL\DBname.mdf' ,MOVE 'DBname_log' TO 'S:\MSSQL\DBname_Log.ldf' ,STATS = 5 ,REPLACE; GO
The above code for your database restore should be saved in a file called “5_RestorePubDatabaseToSub.sql”.
Step 6: Disable any triggers on the Subscriber
This step is only needed when Triggers are present. We don’t want them to fire on the subscriber, so we disable all triggers in the database. I have listed the triggers for my table, but you could create T-SQL code to find and disable all triggers in the database.
USE DBname< GO
DISABLE TRIGGER trg_ReportWorkflowSave ON ReportPreference; DISABLE TRIGGER trg_ReportWorkflowReportTbl ON Report;
The above code for your database triggers should be saved in a file called “6_DisableAllDbTriggersOnSub.sql”.
Step 7: Add the Subscription
The following code will add the subscription. Note that we are using push replication.
USE DBname GO
EXEC sp_addsubscription @publication = N'DBname_Repl'
,@subscriber = N'SubServer'
,@destination_db = N'DBname'
,@subscription_type = N'Push'
,@sync_type = N'initialize with backup'
,@backupDeviceType = N'Disk'
,@backupDeviceName = N'T:\DBname.bak'
,@update_mode = N'read only'
,@subscriber_type = 0
GO
EXEC sp_addpushsubscription_agent @publication = N'DBname_Repl'
,@subscriber = N'SubServer'
,@subscriber_db = N'DBname'
,@job_login = NULL
,@job_password = NULL
,@subscriber_security_mode = 1
,@frequency_type = 64
,@frequency_interval = 1
,@frequency_relative_interval = 1
,@frequency_recurrence_factor = 0
,@frequency_subday = 4
,@frequency_subday_interval = 5
,@active_start_time_of_day = 0
,@active_end_time_of_day = 235959
,@active_start_date = 0
,@active_end_date = 0
,@dts_package_location = N'Distributor' GO
The above code for your subscriber should be saved in a file called “7_AddSubscriptionOnPub.sql”.
Step 8: Enable the Distribution clean up job
USE distribution GO EXEC msdb..sp_update_job @job_name = 'Distribution clean up: distribution', @enabled = 1; GO EXEC msdb..sp_start_job @job_name = 'Distribution clean up: distribution' GO
The above code should be saved in a file called “8_StartDistribCleanupAgent.sql”.
Next Steps
- Test the script in a Development or Test environment and validate the results.
- Setup the batch file as a SQL Agent Job in order to further automate the process.
Prashant Shekhar has been working with SQL Server since 2001. He started as a SQL Server Developer for Property & Casualty (P&C) Insurance Company. Thru the years he has dabbled with several MS SQL Server technologies as the product has matured. He has concentrated in the Insurance and Finance vertical for the most part and is currently a SQL Server Database Administrator for a health care company located in Malvern, PA. His present core responsibilities include – support for Development, QA, UAT and Production environments. This entails setting up and configuring SQL Server instances, managing security logins, regular database maintenance, automating day-to-day tasks, performance tuning, source control, system monitoring and SQL audits. He has worked for prestigious companies like – Prudential, CSC, Chubb, Philadelphia Insurance, Lincoln Financial, CHASE and Ally bank.
He is a Microsoft Certified Technology Specialist (MCTS). He also has a Project Management Professional (PMP) certification conducted by the Project Management Institute (PMI). He has presented lectures on Database Tuning Advisor (DTA) at the Philadelphia SQL Server User Group (PSSUG) and also at the Philly Code Camp. He is an active member of the Philly chapter for SQL Server professionals. He recently completed an online course on “Tackling the Challenges of Big Data” thru MIT. Prashant lives in the western suburbs of Philadelphia.
Hi Jorge,
should the restore command be this? Using ‘Replica_TEST’ instead of ‘REP_Replica_TEST’
RESTORE DATABASE [REP_Replica_TEST] FROM DISK = ‘\\192.168.3.230\F$\Replica_test.bak’
WITH MOVE ‘Replica_TEST’ TO ‘E:\DATA\REP_REPLICA_TEST.mdf’,
MOVE ‘Replica_TEST_log’ TO ‘G:\LOG\REP_REPLICA_TEST_log.ldf’, STATS = 5, REPLACE;
Hi. Excelent!!! But I have Problem to restore from Publisher script 5 on subscriber server.
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = ‘REP_REPLICA_TEST’)
ALTER DATABASE REP_REPLICA_TEST SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [REP_Replica_TEST] FROM DISK = ‘\\192.168.3.230\F$\Replica_test.bak’
WITH MOVE ‘REP_Replica_TEST’ TO ‘E:\DATA\REP_REPLICA_TEST.mdf’,
MOVE ‘Replica_TEST_log’ TO ‘G:\LOG\REP_REPLICA_TEST_log.ldf’, STATS = 5, REPLACE;
GO
Error
Msg 3234, Level 16, State 2, Line 8
Logical file ‘REP_Replica_TEST’ is not part of database ‘REP_Replica_TEST’. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 8
RESTORE DATABASE is terminating abnormally.
Completion time: 2024-02-20T15:21:22.1253367-06:00