Need help merging multiple full SQL Server Backups into single database

Got a really strange issue with SQL Server database backup files I am getting backup files from the state of California for petroleum production. Each year a database with the same name is created for the data for that year The database is always named, WellProductionInjection. Each year the same tables with the same contents are created and populated. The only difference is that the table names contain the year, so separate tables as supposed to exist for each year. I can get what is labelled as a full backup file for a single year. But when I try to use SQL Server Management Studio to restore all the backup files to a single database, I get only the data for one year, or an error about the database already existing. Any tips on how deal deal with merging multiple backups into a single database? Examples of the backup files can be put on the web if anyone is willing to try their luck with this.

SQL server doesn’t have any built-in capacity to merge multiple backups into a single database, you’ll have to restore each backup with a different database name (like WellProductionInjection_2019 and WellProductionInjection_2020), then do the work yourself to combine them however you need.

Once the databases are restored, you can combine them using a variety of tools; the simplest one is called the “Import/Export Wizard”. You can use this to copy entire tables from one database to another (if all tables have different names), or to append the data from table in one database to the tables in the other (if you actually want to combine the data into the same table).

There is a fairly simple way that I use for a similar task:

1. Restore a backup under a different name. For example, WellProductionInjection_new_data

2. Right-click on WellProductionInjection_new_data -> Task -> Generate Script
   

3. In the advanced options specify

    Script USE DATABASE = False
    Types of data to script = Data Only
   

4. Save the script to a .sql file

5. Import data. Open Command Prompt and enter a command like this:

   sqlcmd -d WellProductionInjection < C:UsersUserDocumentsscript.sql

Where:

WellProductionInjection – the database in which you aggregate data

C:UsersUserDocumentsscript.sql – the path to the script that you specified in step 2.

The described method will import only data with new PK, and this method will only work if the schema database does not change.

If desired, the algorithm can be fully automated; to automate the second step, use https://github.com/microsoft/mssql-scripter