SQL Server Backup – How to Backup and Restore Part of a Database

backupexportsql serversql-server-2005

We have a sql-server 2005 database that we regularly transfer from our client site to ours. This takes a long time because we don't have a direct connection and have to transfer the file over their web based file transfer application. The database is currently about 10GB however we don't need all the data – most of it is in audit tables and tables that hold calculated values that can be re-generated.

I have looked at creating a filegroup to hold the audit tables and was hoping I could just backup and restore the primary filegroup. I can backup fine but when restoring I get an error saying that I'm not restoring it to the same database. Is it possible to restore part of a database to a different server using filegroups? Is there a better way to do this?

Best Answer

To be honest, this is easiest:

  • backup/restore a copy locally
  • remove unwanted data from the copy (with DELETE or TRUNCATE TABLE, not DROP...)
  • ship the copy

I wouldn't bother with filegroups because of the added complexity you noted...