Backup very large table

I have to update certain values of a large table (for the sake of a presumed example, it is called 'Resource' and it is over 5M rows) and thus I have to make a backup before performing the changes. We do not have enough DB free space in order to store the full backed-up table.

Which is the best way? Is there a way to do this by blocks? I mean something like: backing up the first 100K rows from the original table, updating those 100K rows in the original table, deleting those 100K rows from the backed-up table, backing up the following 100K rows from the original table, and proceeding analogously. Is this feasible?

Two thoughts come to mind.

• If you are concerned that this update may not properly affect the table the way you think, have you thought about putting the update inside a transaction.

• You could do the update, query the data, and if it all looks okay, Commit the Transaction. If it fails, you could perform a Rollback.

Alternative

You may want to look at the BCP utility to extract the table to a flat file outside of the SQL Server.

Presumably, you could store the contents of the table in a location where you aren't under as much storage pressure. If the update process fails, you could attempt to restore the contents back into your table.

StackExchange Database Administrators Q#42633, answer score: 4