3

I have an extremely large table (400GB data size, inside a 2.5TB database) that is no longer needed. Before we drop it, however, our client contract says we need to archive it to tape in case in needs to be recovered at some point in the future.

The most obvious idea is to simply archive the full database backup, but recovering this one table from that very large backup would be a challenge, due to the total database size.

I suppose I could also export the table to an otherwise-empty database, and back that up. This may be my fallback option.

I wanted to ask about filegroup backups, however, since this table is part of its own filegroup. Is this a valid option for me?

If I understand this article on filegroup restores correctly, the answer is no, but I'm not sure that my case applies here, since my database is in SIMPLE recovery mode (not FULL), and because I'm dropping the filegroup entirely after the backup.

Is filegroup backup an option? Or should I go with one of the other two?

Improve this question
1
  • 1
    I'm hesitant to post an answer on this since I haven't tried this myself. I would say if you move the table to a file group, mark that file group read only, then backup the file group, you should be good for a later restore. But, since you intend to drop the file group, you'd have to test how best to do the restore on the dropped group. Probably create the group empty and then run the restore. What you're looking at is a piecemeal restore: msdn.microsoft.com/en-us/library/ms177425.aspx
    – Grant Fritchey
    Commented Feb 23, 2012 at 21:21

2 Answers 2

Reset to default
2

I'd favour a BCP extract.

  • BCP export to file.
  • Import somewhere (different database or server) to confirm the file "is good".
  • Archive multiple copies, to multiple tapes.

@Marian answered a question of mine with a great list of BCP references that you might find useful. The BCP Basics by SQLFool is also a good starter for ten.

Improve this answer
0

After you drop your big table, there will still be transactions performed on your DB, hence you will need the log backups to restore the DB to a consistent state. These log backups will have the transaction that dropped your big table.

You may have to use your other options. The benefit of using a filegroup restore is that you do not have to do a full restore to reach a consistent state, hence saving on time.

Improve this answer
1
  • There are no log backups, the database is in simple recovery mode.
    – BradC
    Commented Feb 23, 2012 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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