Sql-server – SQL Server Backup Failure

Try to set your log file to autogrow or temporarily add some extra log files which can handle such an operation

BUT

it may be simplier and faster to do something like this:

use SalesDWH
go
ALTER TABLE npidata ADD npiNew varchar(20)
GO
UPDATE npidata SET
  npiNew = SUBSTRING(npi, 1, 20)
GO
ALTER TABLE npidata DROP COLUMN npi
GO
EXEC sp_rename 'npidata.npiNew', 'npidata.npi', 'COLUMN'
GO
DBCC CLEANTABLE(0, 'npidata', 100)

In this case log file usage can be controlled by UPDATE statement, you may perform this by small chunks like:

use SalesDWH
go
ALTER TABLE npidata ADD npiNew varchar(20), ready bit
GO
WHILE 1 = 1
BEGIN
  UPDATE TOP (100) npidata SET
    npiNew = SUBSTRING(npi, 1, 20),
    ready=1
  WHERE ready is null

  IF @@ROWCOUNT = 0 BREAK
END
GO
ALTER TABLE npidata DROP COLUMN npi
GO
ALTER TABLE npidata DROP COLUMN ready
GO
EXEC sp_rename 'npidata.npiNew', 'npidata.npi', 'COLUMN'
GO
DBCC CLEANTABLE(0, 'npidata', 100)

For better performance, in case when the table is big - you may apply temporary index on column ready

OH, YES!!!

Like @RemusRusanu said - TURN OFF THE AUTOSHRINK

You cannot set your database to single-user mode or offline because your database is already in a RESTORING state.

Second, your script tries to use the stored procedure sp_fixusers. This is a custom stored procedure to fix orphaned users. If you need to use this stored procedure, you need to re-create it first, using the script in the link above. But it is possible that your databases uses a different version and it will be better if you use the sp_fixusers from your server/database.