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:
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…
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.