I've got an issue and I'm looking for best practice advice / whatever I can do to make this work.
I need to come up with a way to automate getting a backup from many offsite databases of unknown name, and there is one table that I cannot back up. From there then merging the data from all these backups into a single database and append a column stating where it is from.
The remote databases will all be running SQL 2016 standard, or sql 2012 Enterprise, or newer. They will not have SSIS. The local database server is running SQL 2016 Standard and can have SSIS.
After some looking around, my plan was to separate the one table I cannot back up into it's own filegroup. Then from there backing up the primary filegroup only, getting that sent to us, and then using SSIS or a service monitoring a folder for new backups. From there restoring the backup to a DB of name I can pre-set, to act as a staging area so I can merge the data as needed.
The reason for wanting the backup to go into a DB name I set here and not necessarily what is at the remote site is due to the fact that I can't guarantee what they name the databases and I'm worried about a conflict of a database we have on the server that will be handling these backups.
The issue I found with that method is that I can't seem to restore only the primary filegroup, it either complains about the name being different(even if I have with replace), or even if the name is the same it will complain that it did not back up a file from the second filegroup. I get this error even if I include the "REPLACE" keyword: "The backup set holds a backup of a database other than the existing 'TestImport' database."
I have the ability to change the methodology as much as I need to during this stage, but receiving backups from an unknown number of databases of unknown name, and automating that into a centralized location are the items that are set. I'm open to any ideas that anybody has.