Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.
share|improve this question
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
add comment

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.

share|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
add comment

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.