Just create a separate drive path for backups on that machine and, to keep it simple, make sure there are no spaces or other unexpected chars in the path name.

Btw,...

For an OUTER JOIN, you must put conditions on the possibly-missing table in the JOIN clause, not in the WHERE clause.

select * from #temp1 T

left join #temp2...

I don't know about the overall logic, but the JOINs above seem needlessly convoluted.  Instead, maybe:

  INNER JOIN #UserB AS B
    ON ...

I think the Milestone table does not contain a column named "TOT_ABSENCES".  Please review the column names in the Milestone table.

Create an index keyed on pbRecordId on that table.

The clustered index is the table itself.  That is, all columns are stored in the clustered index.  Thus, the width is the total width of the entire row.

The system itself already has all the metadata views you need.  It's best to just use those views: sys.objects, sys.columns, sys.key_constraints, etc..

I too would not drop compression to do a load, especially a large one.  Page compression can help data load much faster by reducing I/O (compression takes more CPU but...

If these are staging tables, just be sure to TRUNCATE the table rather than DELETE the rows.  TRUNCATE will keep the table allocations clean without having to create a clustering...

I prefer to use CROSS APPLY(s) for such calcs.

Select emp_code, emp_name,emp_last_name, emp_max_pay, emp_yearly_salary
From dbo.table_name
Cross Apply (
    Select case when emp_max_pay < 10000 then emp_max_pay...

I agree with Jonathan: you've already got the exact index needed to support that query.

What specific version and edition of SQL Server are you on?

In particular, does the specific version...

UPDATE dbo.table_name
SET TPN = RIGHT(REPLICATE('0', 8) + CAST(TPN AS varchar(9)), 9)
WHERE TPN NOT LIKE '%[^0-9]%'

That codes parses fine for me on SQL 2016.

, CAST(p.PeopleID AS varchar(10)) + CASE WHEN p.instructorInt = -1 THEN '-1' ELSE '' END AS OriginalDatabaseId

Try this:

select 
cast(SystemUser as varchar(25)) as "User",
LogonTime as "Date",
cast(HOST_NAME as varchar(25)) as "Workstation",
SPID as "session",
cast(APP_NAME as varchar(55)) as "Program",
count(SPID) over () AS SystemUserCount /*add this line*/
from ServerLogonHistory
where...