Setting up Transactional Replication from a back up
Here's a brief guide to set up Transactional Replication from a back up copy.
-
Restore the publication database onto subscriber.
-
Restore the last transaction log backup onto subscriber.
-
Drop all foreign key constraints on the subscriber database.
SELECT DISTINCT 'ALTER TABLE [dbo].[' + object_name(fkeyid) +
'] DROP CONSTRAINT ' + object_name(constid) +
CHAR(13) + CHAR(10) + 'go'
FROM SYSFOREIGNKEYS
GO
UPDATE syscolumns SET xtype=173, xusertype=173 WHERE xtype=189
--If updates not allowed use foll. script
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
-
Set up Publication on the publisher database using the Replication -> Configure Publishing, Subscription and Distribution wizard.
-
Get the code for sp_scriptpublicationcustomprocs from MASTER database. Make sure the width of the results window is set to 1200 in the Tools -> Options window for both Publisher and Subscriber.
-
Compile and create the stored proc code on the publication database.
-
Execute the stored proc on the publication. The stored proc generates all the required Add/Update/Delete stored procedures for all the articles.
exec sp_scriptpublicationcustomprocs @publication = N'Pub Name here'
Note: If publisher DB is not enabled for 'Publish' use the following code to enable it first.
USE master
GO
EXEC sp_replicationdboption @dbname = N'CopyProd', @optname = N'publish', @value = N'true'
GO
-
Compile the generated script on the subscriber database.
-
Set up the Subscriber. Right click on the Publisher -> Properties -> "Push New". Dont forget to choose the "No, the Subscriber already has the schema and data" option on the Initialize Subscription screen.
Issues with updates on tables with IDENTITY Columns
If you are updating a table that has an identity column and if replication distribution agent fails with error: “Cannot update identity column ”, the following steps can help resolve the issue.
The advantage with this method compared to dropping and adding the article from subscription is that there is no need to generate the snapshot which means no need to transfer the millions of rows for large tables over the network to the subscriber.
- Get the script from MASTER db for the stored proc sp_scriptdynamicupdproc
- Compile the stored proc on the publisher DB
- Get the article-Id from sysarticles for the table in question.
- Execute the stored proc sp_scriptdynamicupdproc @artid =
- The stored proc will generate a script for “update” procedure.
- Compile the generated script on the subscriber database.
If you have many tables with IDENTITY columns, you can write up some dynamic SQL to generate the update stored procs as mentioned above to a file and compile all the stored procs at once on the subscriber.