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?

BradC
  • 10,073
  • 9
  • 51
  • 89

2 Answers2

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.

Community
  • 1
Mark Storey-Smith
  • 31,860
  • 9
  • 90
  • 125
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.

StanleyJohns
  • 5,982
  • 2
  • 25
  • 44