January 27, 2011 at 3:42 pm
Hello all,
I setup replication from the backup of publisher database by following these steps:
1.Set up the Distributer – database and share – one time setup
2.Set up Publisher on source server - Don’t use either checkbox for snapshot.
3.Enable the flag under the Publication properties to allow “initialize from backup”. (Right-click on the publication, properties)
4.Disable the distribution cleanup agents. (Under SQL agent jobs)
5.Make a Full backup of the database. Keep a local copy as you'll need it later.
6.Copy database to other site. (Over the network, courier pigeon, magic, whatever)
7.Restore database with the same name
8.Create pull subscription on the destination server. (scripts below)
Script 1:
-----BEGIN: Script to be run at Subscriber 'Subscribing_SQLServerName'-----------------
use [Your_DB_Name]
exec sp_addpullsubscription @publisher = N'Publishing_SQLServerName', @publication = N'Your_DB_Name_PUB', @publisher_db = N'Your_DB_Name', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 0
Script 2:
exec sp_addpullsubscription_agent @publisher = N'Publishing_SQLServerName', @publisher_db = N'Your_DB_Name', @publication = N'Your_DB_Name_PUB', @distributor = N'Publishing_SQLServerName', @distributor_security_mode = 0, @distributor_login = N'dist_login_acct', @distributor_password = N'dist_password', @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 = 20090902, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = N'domain\username', @job_password =N'user_password', @publication_type = 0
GO
-----END: Script to be run at Subscriber 'Subscribing_SQLServerName'-----------------
Script 3:
-----BEGIN: Script to be run at Publisher 'Publishing_SQLServerName'------------
------- backupdevicename has to be located on the Publisher machine -------
use [Your_DB_Name]
exec sp_addsubscription @publication = N'Your_DB_Name_PUB', @subscriber = N'Subscribing_SQLServerName', @destination_db = N'Your_DB_Name', @sync_type = N'initialize with backup', @backupdevicetype = 'disk', @backupdevicename = 'e:\BACKUP\Your_DB_Name090209.bak', @subscription_type = N'pull', @update_mode = N'read only'
GO
-------END: Script to be run at Publisher 'Publishing_SQLServerName'-------------
Now the subscriber is created, but when i check the replication monitor, it gives me uninitialized subscription. When i go check the distribution agent at the subscription it says "cannot connect to distributor". It is the same error when i check the "view syncronization status".
January 27, 2011 at 3:43 pm
I am actually using pull subscription
October 15, 2016 at 10:33 am
is it resolved ..i followed same steps and getting same error...please let us know if it works or not
cant we configure transactional replication from full backup ...its working while taking tlog backup and setup..
The below link is having steps if you have option to gp with tlog backups
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply