Unanswered: Moving subsets of data between servers
I have a fairly complicated SQL Server database (around 300+) tables and I need to move portions of those tables to another SQL Server database instance.
My client has all his customer's data in one big database. One of those customers decided that for security reasons, they wanted their database onsite. Now I have to move all the data, for just that customer, to another database. This customer's data is spread all over the 300+ tables and it's fairly normalized so lots of parent-child relationships, etc...
Has anyone ever tried to do something like this? Do I just have to write scripts/sql to do it manually? I was hoping for something easier than just writing all the sql.
I think you are going to need to write scripts based on what field value identifies that customer. Based on the amount of data, I would extract the schema, create it on the new server and write bcp scripts to bulk out/in the data, then create whatever idxs and constraints on the tables post data migration. I guess there will be some tables where you need all the data (dimension and/or lookup tables), etc.