Let's say in SQL Server, I have a publisher database DatabaseA and I created a copy of it and restored it into another SQL Server with the name DatabaseB. Now, if I want to setup the Transactional Replication between DatabaseA and DatabaseB, then can I do that without dropping or truncating the tables on the subscriber side. If this trick is possible, then please suggest.
I have used the database backup method of initialization to solve this scenario. Also, I find out that if Publisher and Subscriber databases are created in the same SQL Server Instance, then it allows DDL operations at the subscriber database. Whereas, if we set up the subscriber database in any other SQL Server Instance, then it does not allow any DDL Operation and gives the following error:
Msg 4929, Level 16, State 1, Line 5
Cannot alter the table 'AR_ACCESSRIGHT' because it is being published for
replication.
Msg 3727, Level 16, State 0, Line 5
Could not drop constraint. See previous errors.
Msg 1779, Level 16, State 0, Line 12
Table 'AR_ACCESSRIGHT' already has a primary key defined on it.
Msg 1750, Level 16, State 0, Line 12
Could not create constraint or index. See previous errors.
I used SQL Server 2019 to do this.