up vote 4 down vote favorite
share [g+] share [fb]

I have a database that is around .. 20ish Gig. I need to (manually*) back up this database, 7zip the backup and then DOWNLOAD this backup from my server to my desktop. (Rinse, repeat sorta often).

I knew that the size would be big, I split my tables into a number of Files and FileGroups :-

enter image description here

So the files XWing_Boundaries and XWing_BoundayStreets are (more or less) static data**. So I put those in their own file and filegroup so I don't need to back these up and download them. I only want to download (and restore locally) the dynamic data, which is the file XWing. I don't care about the log file either.

So, is there a trick so I can just backup/download/restore the single file/filegroup I need only, instead of grabbing -all- the data, each time?

Notes:

  • * We have nightly full backups, so that's all fine / not an issue.
  • ** I manually change this data once in a blue moon. I even toyed with the idea of making these files READONLY for the suggest extra speed increase, too.
link|improve this question
93% accept rate
1  
I'm not even going to lie. I +1'd based solely on your database names and your avatar. – Holocryptic Aug 26 '11 at 1:23
It's not a bad question, either :) – Holocryptic Aug 26 '11 at 1:26
2  
@Holocryptic - wink .. Oh! and this question is not a trap, either. – Pure.Krome Aug 26 '11 at 1:41
feedback

1 Answer

up vote 4 down vote accepted

Sure, you can backup specific filegroups/files. See http://technet.microsoft.com/en-us/library/ms186865.aspx for full details.

Backing Up Specific Files or Filegroups

BACKUP DATABASE database_name FILE = XWing TO <backup_device>

You can also add WITH COMPRESSION to compress the backups prior to writing to disk.

link|improve this answer
sure? there's no mention of FILEGROUPS or FILES in that sample code, above.... Maybe want to elaborate your answer using my Filenames / filegroups? – Pure.Krome Aug 26 '11 at 1:40
XWing is your file's logical name, which is referenced in the sample code. I didn't know the database_name, so I left "database_name" there for you to substitute in. – Derek Kromm Aug 26 '11 at 1:42
but would that backup your ENTIRE db? all files/filegroups? – Pure.Krome Aug 26 '11 at 1:42
Sorry, I just realized I left off the "FILE =" part of the syntax - I'm a dolt and it's late. – Derek Kromm Aug 26 '11 at 1:45
Ahh!! u've updated your post.. kewl :) that does help now! I'll give that a go. – Pure.Krome Aug 26 '11 at 1:46
feedback

Your Answer

 
or
required, but never shown
discard

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