Sql-server – identity value way off in a restored database

Your colleague is an idiot.

The solution won't be scalable, the UDF isn't concurrent (same reason as this). And how do you deal with multi-row inserts: this would require a UDF call per row

And migrating to other RDBMS doesn't happen often in real life... you may as well not use SQL Server now and use sequences on Oracle and hope you don't migrate away.

Edit:

Your update states that moving data is for refreshing non-production databases.

In that case, you ignore the identity columns when refreshing. You don't compromise your implementation to make non-prod loading easier. Or use temp tables to track the identity value changes.

Or use processes: we refresh our test system every night from production which avoids the issue entirely. (And ensures our prod backup can be restored too)

Assuming your Publisher is using an int identity that begins at 1, you could issue DBCC CHECKIDENT('dbo.mytable', RESEED, -2147483648) at the subscriber. You can then use the range from -2147483648 to 0 to hold your "temporary deltas".