Jan 29, 2016, 2:30:20 PM1/29/16
We are currently upgrading to SQL Server 2014 and am trying to use the Transfer SQL Server Objects Task, copying select tables from one server / db to another strictly for building local master tables.
The source db is a copy of our production EMR data (electronic medical record) and the destination is a data warehouse server. There are many filegroups on the source server but minimal on the destination.
I would like to copy these tables but keep getting the error that "Invalid Filegroup XXX Specified". However, I don't need (or want) to copy the filegroup structure from the source. I have read that the solution to this is to create the same filegroups on the destination as the source. Is there any way to work around this? I would like to just copy these few tables from several filegroups into the primary of the destination db. I can see the benefit to enforcing this for larger purposes. What are my options, short of creating an individual data pump task for every table I would like to bring over?
Thanks for any and all help! I am not new to SQL Server but have never used the transfer objects task before.