Why do I have multiple (unassociated) temporal history tables?
You need to provide the name of the history table in order to maintain data continuity when turning system versioning off and on.  This behavior is mentioned in the documentation for ALTER TABLE:


  If you don't use the HISTORY_TABLE argument, the system generates a new history table matching the schema of the current table, creates a link between the two tables, and enables the system to record the history of each record in the current table in the history table.


Here's a demo.  I'll create the example table from the documentation:

CREATE TABLE dbo.Employee   
(    
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED   
  , [Name] nvarchar(100) NOT NULL  
  , [Position] varchar(100) NOT NULL   
  , [Department] varchar(100) NOT NULL  
  , [Address] nvarchar(1024) NOT NULL  
  , [AnnualSalary] decimal (10,2) NOT NULL  
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START  
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END  
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)  
 )    
 WITH (SYSTEM_VERSIONING = ON);


This results in a history table named MSSQL_TemporalHistoryFor_1253579504.  Now I'll disable and enable system versioning:

ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = ON);


And I'm in your exact situation:





Now I'll clean everything up:

ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.Employee;
DROP TABLE dbo.MSSQL_TemporalHistoryFor_1253579504;
DROP TABLE dbo.MSSQL_TemporalHistoryFor_1253579504_D0055BB4;


Then create the table with a specific history table name:

 CREATE TABLE dbo.Employee   
(    
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED   
  , [Name] nvarchar(100) NOT NULL  
  , [Position] varchar(100) NOT NULL   
  , [Department] varchar(100) NOT NULL  
  , [Address] nvarchar(1024) NOT NULL  
  , [AnnualSalary] decimal (10,2) NOT NULL  
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START  
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END  
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)  
 )    
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));  


Then turn system versioning off and on, but continue specifying the history table name:

ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));


Note: in your specific situation, you should be able to use this syntax to "reattach" one lost history table to your base table

No extra tables:



The takeaway

Always specify a history table name explicitly when creating temporal tables or enabling system versioning.

The MS docs now call this out specifically on the Stopping System-Versioning on a System-Versioned Temporal Table page:


  When turning system versioning back on, do not forget to specify the HISTORY_TABLE argument. Failing to do so will result in a new history table being created and associated with the current table. The original history table will still exist as a normal table, but won't be associated with the current table.