Handling partial backups from many different databases

Question

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.

Answer 1

In order to do a piecemeal restore of only the primary filegroup, you need to ensure the following conditions are met:

1. There can be no pre-existing target for the database restore statement. i.e. you must DROP any existing database before you run the piecemeal restore. This applies even if you use the REPLACE keyword in the RESTORE DATABASE statement.

2. Use a MOVE clause to specify the storage location of the primary filegroup for the database you're restoring. Specifying the location allows you to ensure you don't try overwriting some other database file, which would result in an error during the restore.

I would restore the database temporarily, copy the data from the restored database into a permanent database, then drop the restored database. From the details in your question, there is no need to keep the restored database attached to the instance once you've copied the data into the permanent location.

I wrote a blog post showing the problem with performing a piecemeal restore onto an existing database, and how to rectify the problem at SQLServerScience.com

Answer 2

Given the size you've estimated for all remote databases and the fact that the schemas are identical, I would suggest using either SSIS to handle this data movement rather than backup and restore.

If using SSIS, you could have a control table that lists the remote servers and databases. Your package can then read this list and iterate over it to fetch the data from the remote databases into the local database. You can also add a foreign key to your local data tables back to your control table to identify the original source of each data row.