18

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?

Improve this question
3
  • 1
    You could benefit from reading this question: The smallest backup possible … with SQL Server, and especially Brent's answer.
    – Marian
    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
    – Adam Butler
    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
    Jul 25, 2011 at 4:20

5 Answers 5

Reset to default
14

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

Improve this answer
1
7

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.

Improve this answer
2

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.

Improve this answer
0

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.

Improve this answer
-1

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

Explained here

Improve this answer
1
  • What's wrong with it?
    – Jude
    Jan 15, 2016 at 10:14

Your Answer

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

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