Try removing the interim storage. Instead of inserting into temp table then reading from temp table, do the grouping/joining directly.

Dropping it, not much. Recreating it however will require table locks unless you have Enterprise edition and specify ONLINE for the index creation.

There are three blob columns in that table (varchar(max)/nvarchar(max)), they can be up to 2GB each. Gut feel, that's where the space is going.

Check the Datalength() of each of those...

Is it a heap (no clustered index)?

There's nothing in an execution plan (estimated or actual) that reflects the time required to execute it.

Change the EXEC to a print and examine the generated statement. That usually helps find errors in dynamic SQL.

Problem is, without the execution plan we're doing nothing more than making guesses.

Maybe start with this:

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Execution plan please (actual, not estimated)

Nothing whatsoever to do with compatibility level.

You cannot restore down-version. A SQL 2012 database (a database attached to a 2012 instance) cannot be restored to anything other than SQL Server...

Table definitions, index definitions and execution plan please.

Leave it running. No point in reinitialising unless something breaks.

You can (and probably should) leave your nightly full backups as normal full backups (without the copy only), especially if you...

Or [ url ] http://www.google.com [ /url ] (remove spaces)

http://www.google.com

Advertisement for their database 'recovery' product. Look at where...

nm