That’s awesome on the backup.
Question: how many of the rows in the backup table are current with the altered table?
Perhaps you should consider inserts and updates on the backup table instead.
1) check for any constraints on the table. Also be wise and see what indices are used for the operation.
- a covering index is significantly less expensive than lookups or a full Clustered index scan.
2) Can you tolerate downtime for the whole restore?
- Or can you temporarily build an index, insert with this index, remove said index, and run stats on the table...and end with less impact?
3) Check the column data length and estimate the log size required for the operation.
SELECT MAX(DATALENGTH(col)) / 1024.0/ 1024.0 AS SpaceMB FROM TABLE
4) You may need to split the transaction up.
(Using either a cursor or a number for the table possibly through ID name)
WHILE @@FETCHSTATUS = 0
SET COL = BAK.COL
FROM TABLE RES
INNER JOIN TABLE_BAK BAK ON BAK.UNIQUE_ID = RES.UNIQUE_ID
Hint: the error you get from your sub query is because the join is wrong. Better to find what columns make a unique row and update on that. In a way, be glad this rule on subqueries is enforced as you may have ended with dirty data. 😅