Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
You could benefit from reading this question: The smallest backup possible … with SQL Server, and especially Brent's answer. –  Marian Jul 21 '11 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 '11 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. –  Shawn Melton Jul 25 '11 at 4:20

3 Answers 3

up vote 6 down vote accepted

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

share|improve this answer
3  
And when @gbn says "remove unwanted data", he means "truncate table", not "drop table"... –  Martin S. Stoller Jul 21 '11 at 6:09
    
@Martin S. Stoller: clarified, thanks –  gbn Jul 21 '11 at 6:14

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.

share|improve this answer

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

Explained here

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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