Sql-server – Archive a single giant table from a SQL 2008 database – filegroup backup

backupfilegroupssql-server-2008

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?

Best Answer

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.

Related Question