Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
@George Oh, I didn't know about that one. Thanks, George. –  iL_Marto May 20 '13 at 18:19
    
Do you have an existing back up already? –  Cougar9000 May 20 '13 at 18:42
    
What version of SQL Server? Do you have backup compression enabled? –  Cougar9000 May 20 '13 at 18:45
    
@Cougar9000, no there is not and existing back up and I'm using SQL Server 2008 R2. –  iL_Marto May 20 '13 at 18:54
8  
If the database is not important enough to have a backup, why is it important enough to back it up before making these changes? Also note that updating 5M rows in your table will most likely cause a certain amount of log usage, and if you are low on space in the database... –  Cade Roux May 20 '13 at 20:54

6 Answers 6

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.

share|improve this answer

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.

share|improve this answer
2  
CREATE TABLE ... AS SELECT is not valid SQL Server syntax. –  Aaron Bertrand May 21 '13 at 21:03
    
The syntax for SQL Server is select * into schema.backuptable from schema.table;. As pointed out below, this duplicates the column definitions and the data of schema.table - not the constraints or indexes or triggers. –  Greenstone Walker May 22 '13 at 4:42

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;
share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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>.

share|improve this answer
    
The question states: "We have not enough DB free space in order to store the full backed-up table". So what you're proposing won't work here. –  Mat May 22 '13 at 7:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.