Backup very large table

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.

Do not backup a single table. It is usually a bad idea. Always backup the entire database

First of all. I am assuming that the database is not live. Otherwise you could lose operations if you restore a backup.

The answer you want, assuming you know what you are doing:

A simple way to create a backup of a table is to create another table with its contents:

CREATE Table tableBackup as select * from tableToBackup;

if something goes wrong, the delete the tuples from the original table, and insert the tuples from the backup database.

Of course you must be very aware that backing up one single table is usually a bad idea. Usually database integrity depends on the values of the entire database (for example, a value in another table might depend on the existence of a tuple in this one --a foreign key relationship for example).

If there are referential constraints between the table you want to back up and other tables, you might not be able to restore the original table using the method I suggested above.

So unless you know what you are doing, backup the entire database and not the single table. Check the documentation of your database of choice to see how this is done.

To add to what @dmg said. Backing up a single table can be problematic. RI aside if for example your "large" table is 90% of the database then just backing up the table won't really help you much. If you are using SQL 2008 make sure that you are compressing your backups. You can get a fair compression percentage and may actually be able to take a full backup.

Another option is to take a transaction log backup. This is what I usually do when I'm dealing with a large database before making a change. They should always be much smaller (particularly if you take frequent backups) than the full backup. And will be even smaller if you compress them.

Now that will not work if your database is set to simple recovery. In that case your only other traditional backup option is to look into differential backups. These can actually get quite large. However if your last full backup was done recently, or you only make changes to a small percentage of the database then this may be a viable option for you. On the other hand if your database is set to simple then you may not need to worry about being recoverable to before your change.

Last but not least (SQL 2005 or later) there is the OUTPUT clause. This handy little clause let's you output the changes you've made during your command. They can be stored in a table variable or table. Basically they give you access to the INSERTED and DELETED tables that normally you only see in triggers. I effect you can backup your changes (both before and after) into another table. This way you are only backing up the changed rows and columns. Make sure you include your primary key as well of course. Here is the BOL entry. And here is an example from BOL. In this particular example only 4 columns from 10 rows of data are saved. Even if the table happened to be 5 or even 10 mil rows.

USE AdventureWorks;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25 
OUTPUT INSERTED.EmployeeID,
       DELETED.VacationHours,
       INSERTED.VacationHours,
       INSERTED.ModifiedDate
INTO @MyTableVar;

The create table syntax posted won't work, afaik.

The easy way to back up a single table is:

SELECT * INTO [backup-table] FROM [source-table]

Then you can drop [backup-table] when you don't need it.

You could do this across to a diff db (that might be on a diff spindle set), etc.

So far by the given answers you are backing up your base table data, but not the table itself. The table has many other attributes, conducting a SELECT * INTO basically just get's you the cell values and column structure. Although not complete, some additional attributes to consider:

• Partitioning Scheme/Functions
• Indexes
• Foreign Key Relationships
• Object Level Permissions
• -Extended Properties
• etc.

This is a very large table, so I wouldn't be surprised to find partitioning and heavy indexing. The best answer so far IMO is to backup the entire database. If that is not a good option, script out the entire schema and supporting schema (in case of items that reside outside the table like in partitioning). Have a copy of that, then do a SELECT INTO.

Of course, some very knowledgeable people such as Kim Tripp recommend adding your indexes and sometimes partitioning after you dump the data, which is probably sound advice. Just remember if you do partition after you dumped the data, you will want to use a check constraint to ensure partition elimination happens. That's something to look into if you actually see it configured though.

You could take a backup of the existing table alone into a new_table and update your table and if anything goes wrong you always have your backup table to revert back.

Ex:

-- SCRIPT TO BACKUP A TABLE
SELECT * INTO <NEW_TABLE> FROM CURRENT_TABLE

Taking Backup of the single table is much less then taking backup of complete database.

If everything goes well then you may drop the <NEW_TABLE>.