All we need is an easy explanation of the problem, so here it is.
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?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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).
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂