I haven't had time to check out any strange implications this may have but I would experiment with synonyms

You can create a synonym for a dbname.schema.name.tablename combination, so your code...

You need to investigate what is going on with DTC (Distributed Transaction Coordinator)

Is it running on both machines involved?

Can the two DTC instances communicate? - it is a long...

As always - it depends.

Lets say you have a table of 100 million rows and your ETL is inserting/updating a few thousand.

In that case dropping amd recreating constraints is...

Personally I always include the as, and I wish it was mandatory to aid readability.

Mostly personal preference I suppose regarding the readability side, but I also find for...

Just migrating stored procedures from an older version to new does not require (nor benefit) from with recompile option.

Generally the idea of a stored procedure re-using the compiled plan is...

There is not normally a need to. What exactly makes you think you should need to do this - especially fo rone row?

Normally the space will be reused a new...

Rather a broad question - have you any specific error messages to go on? In what context was this asked?

Mike

I would also have the table defined WITH the three columnns that are being added rather than create it/load it /add columns.

Make them non-nullable with default values if possible and...

My immediate reaction is don't do it.

I would have a separate xref table for each of the many:many relationships that you need for the following reasons:

a) It more clearly...

If the main problem is ETL loading speed I would also look at a few configuration details:

a) You say the data file is all on one disc - where are...

If you have an encrypted database it can compress very poorly, so it is often worth ensuring no compression is selected for a database that is encrypted.

It sounds like you are backing up to the same file every time, and you have multiple backups in the one file.

Various choices, you say you are new to this,...

Its is not likely many people can help remotely, but give us some details to work with.

a) DO you have a backup?

b) Can you restore it to a clean db?...

Before you worry anout tools what recovery model is the db in?

Redgate do one - http://www.red-gate.com/products/dba/sql-log-rescue/

I have not used it personally but have heard good reports

Mike

I think your only option is restore most recent backup to a different database, and then extract the lost data from the restored backup and re-insert it to the original...