Build replication from backup not publication source

I have an interesting scenario I am trying to find a solution to.

We currently have the current setup.

ProductionDatabase (publisher) is the publication for transnational replication to the following:
WarehouseDatabase (subscriber)
NewWarehouseDatabase (subscriber)

My issue is that WarehouseDatabase has had triggers and other SP’s added to it. When I build replication for NewWarehouseDatabase I built it from a backup of ProductionDatabase. So NewWarehouseDatabase does not have the triggers or SP’s that had been added to WarehouseDatabase.

Doing a SQL Compare generated a list that had a lot more things displayed than I really want to manage.

So I thought I can build replication by backing up WarehouseDatabase and restoring to NewWarehouseDatabase and then enabling replication from ProductionDatabase. Then I thought that will not work as the LSN and other internals that might not sync up for replication to work.

I am wondering if anyone can suggest how I can accomplish this, if you can understand my babbling…

Regards,
GarryB

If I understand you question correctly you could use a schema compare tool to generate a script that would be used to insert the extra SPs and triggers into the new datawarehouse DB.

Redgate have a tool like this in their DBA bundle and I think data and schema compare are now functionalities of visual studio.

When I need to do something like this, I just use sys.objects. After you restore the NewWarehouseDatabase databse, create a Linked Server on the instance where you have WarehouseDatabase to point to the instance where you have the NewWarehouseDatabase databse. Then write an EXCEPT query using sys.objects. Something like this:

select o.name, s.name
from WarehouseDatabase.sys.objects o
inner join WarehouseDatabase.sys.schemas s on s.schema_id = o.schema_id
where o.type in ('List the types you're interested in')
except
select o.name, s.name
from <LINKEDSERVER>.NewWarehouseDatabase.sys.objects o
inner join <LINKEDSERVER>.NewWarehouseDatabase.sys.schemas s on s.schema_id = o.schema_id
where o.type in ('List the types you're interested in')

This should give you a list of the objects that are in the WarehouseDatabase databse but not in the NewWarehouseDatabase database.