So I have looked up many other people trying to get Transaction Replication Without Snapshot working and I'm stuck. I've followed what everyone has said so I don't know what I'm missing.
Prod server and Report server.
On the prod server I configured the Distributor from the UI. Then I setup a transnational publication from the UI. I made sure not to check either snapshot options. Created the publication.
Changed 'Allowed initialization from backup file' to true in the Subscription options in the UI.
Now I create a full backup of the database on the prod server and restore it to the report server. I disable all triggers and constraints on the report server.
Now also on the prod server I want to create the subscription for the report server. I choose to run all agents at the Distributor.
I choose 'Add Subscriber' and point it to the report server. Agent Schedule is set to Location: Distributor and Schedule: Run continuously
Subscription Properties are set as follows Initialize: checked Initialize When: At first synchronization
I generate the script file and edit it as below
use [Demo]
exec sp_addsubscription
@publication = N'Demo_Publication',
@subscriber = N'mo-rptsvr',
@destination_db = N'Demo',
@subscription_type = N'Push',
@sync_type = N'initialize with backup',
@backupdevicetype ='disk',
@backupdevicename = '\\backup device path.bak',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0
And next I left unmodified
exec sp_addpushsubscription_agent
@publication = N'Demo_Publication',
@subscriber = N'mo-rptsvr',
@subscriber_db = N'Demo',
@job_login = null,
@job_password = null,
@subscriber_security_mode = 1,
@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 = 20140206,
@active_end_date = 99991231,
@enabled_for_syncmgr = N'False',
@dts_package_location = N'Distributor'
Log Reader Agent Status once in awhile says 'x transaction(s) with x command(s) were delivered'
Now looking at the logs this morning I see the following error
'Cannot insert an explicit value into a timestamp columns. Use INSERT with a column list to exclude the timestamp column, or a default into the timestamp column'
Now I have read some posts saying this is because the row timestamp column. I have 1,000's of tables. If this is the issue what is the fastest way to change correct it?
Also after the Synchronization status errors and starts back up, Why does it always say Initializing? This alone takes over an hour before it errors out again.
Update I found in article properties that I can change timestamp to binary(8). Do I need to take another backup and restore it after this change? How will this effect the production server application along with the reporting application?