If you want an EXISTS equivalent to the IN try:

SELECT adminproduct_id,rowversionnumber,therapyadmin_id,packagedproduct_id,adminrecordmethod
   ,actioncode,doseamount,doseunits,dispensableproduct_id,dispensesize,routedproduct_id
   ,dosetext,lotnumber, expirationdate,manufacturercode
FROM hcs.adminproduct P
WHERE EXISTS
(
    SELECT 1
    FROM hcs.therapyadmin T
    WHERE T.therapyadmin_id...

If you are on at least SQL2012, I suspect the easiest approach would be to create a sequence and then add that sequence as the default constraint on the column.

You probably need to ensure that all the filters on the outer table are in the JOIN and not the WHERE clause.
Logically the FROM clause, with JOINS, is processed...

Sounds like a race condition on insert.
I would forget about the explicit transaction and run the following:

UPDATE t1
SET table1.column1 = l.staging_2,
table1.column2 = l.staging_3

Also, check the number of VLFs on the source db.

DBCC LOGINFO


If the number of rows is greater than about 200 look at reducing them:

Your problem is the cursor.
It is difficult to write a set based approach without DDL and comsumable test data.
Try something like the following:

--declare @TransactionEffDate DATETIME2(3) =...

Bulk-logged still has a couple of problems:
1. Point in time recovery will not work for any time the DB was in bulk-logged mode.
2. While bulk-logged recovery logs less...

You might be able to use fn_dump_dblog to read your log backups:

https://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/

While there are performance reasons for not using linked servers, I suspect the main reason your architect team does not want them used is if the link goes down then...

Another thing to add to mix is in high transaction environments where foreign keys look up primary keys on another table.

If the primary key on the other table is clustered,...

Ola Hallengren's solution is the simple and tested solution.

(Well...

In my case I can login to the remote server using a local user...

I have a similar problem in getting backups off a production server in a timely manner.

(I also have to encrpyt them with 7-Zip as it only runs standard edition.)

Running a...