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')
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.