4

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.
3
  • 1
    I'm not even going to lie. I +1'd based solely on your database names and your avatar. Aug 26, 2011 at 1:23
  • It's not a bad question, either :) Aug 26, 2011 at 1:26
  • 2
    @Holocryptic - wink .. Oh! and this question is not a trap, either.
    – Pure.Krome
    Aug 26, 2011 at 1:41

1 Answer 1

4

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.

5
  • 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, 2011 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
    Aug 26, 2011 at 1:42
  • but would that backup your ENTIRE db? all files/filegroups?
    – Pure.Krome
    Aug 26, 2011 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
    Aug 26, 2011 at 1:45
  • Ahh!! u've updated your post.. kewl :) that does help now! I'll give that a go.
    – Pure.Krome
    Aug 26, 2011 at 1:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .