How can I dynamically switch between two databases for ALL queries?

Question

I need to copy some entities from one Drupal 9 database to different Drupal 9 database. I have searched and looked high and low, but none of the examples I have found work anywhere. I found no help on drupal.org, so I'm reaching out here.

Both databases are full Drupal 9 databases; they are almost identical, but some nodes and users are present in one, which I need to copy to the other.

In Drupal 7 it was fairly simple. With both databases present in settings.php, let's say they were 'default' and 'second_db', I could then do something along these lines.

// Switch to second database
db_set_active('second_db');

// Read a user record
$user = user_load($some_uid);

// Switch back to regular db
db_set_active();

// I now have data for the user from the second database and can manipulate and save as I please in the current db
// Create a new user in the default db - just illustrating, not tested!
unset($user->uid);
$user->name .= ' copy';
user_save('', (array)$user);

The beauty of this was that two simple lines with db_set_active could switch ALL database activity to another database.

db_set_active('second_db');
// Everything here will work on second_db
db_set_active();
// Now everything works on the default db

All calls to Drupal functions would work, but just get and write data from and to second_db.

How do I get this exact behavior in Drupal 9?

I tried with \Drupal\Core\Database\Database::setActiveConnection('second_db'), but I can't get that to change the globally active database. \Drupal\user\Entity\User::load($uid) still reads from the current database and not from second_db.

With the following code, $account is empty, although the user is there in the second_db.

\Drupal\Core\Database\Database::setActiveConnection('second_db');
$account = user_load_by_name('Some name only in second_db');
\Drupal\Core\Database\Database::setActiveConnection();

I have also played with $connection = \Drupal\Core\Database\Database::getConnection('second_db'); which returns a database connection that can be used for queries in that database, but I need to change the database for all queries, also the ones performed by core and other modules than my own.

I have seen advice on using \Drupal\Core\Database\Database::addConnectionInfo(); but that's not the magic key as far as I can see.

What's the secret spice that will switch all database calls back and forth between two databases in Drupal 9?