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.
Pure.Krome
  • 6,508
  • 18
  • 73
  • 87

1 Answers1

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.

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