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?

  • 1
    You could benefit from reading this question: The smallest backup possible … with SQL Server, and especially Brent's answer.
    – Marian
    Commented Jul 21, 2011 at 9:46
  • Since this doesn't look possible without much Hackery I believe we might look at moving all audit log tables to a separate database Commented Jul 25, 2011 at 3:33
  • Would this not require a code change in the application? That is a bit much to ask the client to go through.
    – user507
    Commented Jul 25, 2011 at 4:20

5 Answers 5


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


The way you speak of only needing the primary file group, it sounds like to me that you only want the database structure and a very small amount of data. I would suggest if you just want an update of the database structure (objects, tables, etc) to simply script the database out with all the objects. This can be done quickly and easily with PowerShell.

Then the data you actually do need, that cannot be regenerated, simply export that out (a PowerShell script could do this too). I am sure the export file can then be compressed down to a small size and then transfered over the connection to your site.

Of course just like this one, @gbn suggestion could be scripted so it would be up to you to determine which option takes the least amount of time.


This is possible using piecemeal restore. The source database's objects and filegroups have to be organised in a way that supports this. The RESTORE requires additional specific keywords.

While the concept may take a little bit of getting used to, the amount of scripting to implement is no more than that for other solutions suggested.


You cannot backup specific tables to a .bak file, you can export them to csv or script them out or use bcp to put them into a file.

What you can do if you want to back up specific tables (and they are always the same) is move them into a separate file group and back up that file group.

See Back Up Files and Filegroups for the documentation.


Right click and generate scripts, then change the name of the database to target (USE db).

Explained here

  • What's wrong with it?
    – Jude
    Commented Jan 15, 2016 at 10:14

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.