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.

  • Is the schema on the unknown databases the same on all databases? How big are the databases on average?
    – HandyD
    Mar 13 '19 at 1:34
  • I don't know whether you can change the filegroup option to read-only where that table resides at source temporarily while taking backup, which you don't want in your staging area. That way, you can take partial backup and that filegroup will be excluded from your backup media. At the time of restoration, you can use --> RESTORE DATABASE [DB_NAME] FILEGROUP = 'PRIMARY' FROM DISK = 'PATH' with RECOVERY, PARTIAL Mar 13 '19 at 8:23
  • @HandyD, the schema will be the same on all of them, it's only the name that can change per the sites request. The size will be very small, that's why we have to exclude the one table. Without that table it will be less than 1gb in most cases. We can't change it to read only unfortunately, but it can be in it's own filegroup.
    – Ncramer
    Mar 13 '19 at 12:03

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


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.

  • We unfortunately won't be able to have SSIS on the remote servers, but I do plan on using it to import the data and then use a foreign key exactly as you mentioned once the files are at a centralized location.
    – Ncramer
    Mar 18 '19 at 17:32
  • You won't need SSIS on the remote servers. SSIS can run on your central server and simply read the data from your remote servers (as long as they are accessible over the network).
    – HandyD
    Mar 19 '19 at 0:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.