Currently, we have around 50 remote servers, each with a production database. Each night, all 50 of these servers run a backup job and zips the backup. Each morning, a local server runs a job for each site that copies the zip file over, and restores the database to a 'temp' server. The job then copies individual tables (not all tables are copied) to their corresponding database on a 'reporting' server. The database on the reporting server may contain custom tables to track information, so we can't simply drop the database every night.
I feel this process is somewhat redundant. I don't like having the same data in two places, and I don't like have to create scripts to copy individual tables for each remote server added (roughly 1 a month). However, I can't find a good way to do this and keep the custom tables we use.
One of my ideas was to turn the 'temp' server into a repository for the production database backups, and use the 'reporting' server solely for custom tables, but this leads to needing to use linked server joins.
Does anyone have an idea on how I can make this process better?