First of all apologies for the novel but there's a lot to explain...
We have over 100 databases for our product, 1 per server, each of which has variations on other ones (ideally they should all be exactly the same, but this is where over 20 years of product development gets you). For reasons I cannot say my task is to write a script which imports ALL data for a specific client from their DB. There can be many clients in a single DB so we have to detect if customerID is exists on each table and filter on that column if it does. I am using linked servers, a SQL Server Agent job, sprocs, a cursor for all the tables and dynamic SQL to get as far as importing all of the data using
select * into. The job simply drops / recreates the DB on the test server, sets compatability to 80, alters the DB to match the settings of the source DB, creates the tables and then the sprocs / triggers / functions / etc. This works perfectly up to creating the sprocs, triggers, functions etc.
Basically there is one table, on the live server we're running this against in our tests, which used to have columns 'insertedpersonid' and 'updatedpersonid', but for reasons unknown these can now only be found on one server to my knowledge (and there may well be other tables where this is an issue on other servers, so simply creating the columns on the servers which don't have them is not an option). There is a sproc which references these columns, whether or not they exist, so when I try to create the sproc from a DB which does not have these columns the script just kinda falls over. I'm using try catch around every sproc to get it to go past the erroneous sproc, but I'm not happy with that as the erroneous sproc is not created and therefore the DB is not an exact copy of live. I could also be missing other errors in this way.
I have done a lot of searching on this topic, but cannot find any way to ignore object dependencies. Does anyone know of a way to do this?
Also if you can think of any better ways to do any or all of this then please feel free to say so. Note that this will be used by several teams around the world through a web interface (select the server > select the client > click import), so it can't be a simple backup / restore onto another server (firstly not all teams have access to the server and if we import 10 clients then in some cases the DB size will exceed 300GB, which simply won't fit onto the server we're importing to).
Lastly I am afraid I cannot give any detail on what version of SQL Server we're using as there are over 100 servers, some of which are on different versions of Windows Server. We're not happy that the testing server may not be on the same OS or SQL Server version, but it's the way it has to be. In the ideal world we'd have 1 server per SQL Server version per OS.
Thanks in advance. Regards, Richard