0

I have a large database, ~100 gig or so, and about 5 gigs of it is mission critical data, the rest, I could reload from a web service, so it's not crucial.

Backing up that data is expensive, and not worth it, when it could just be reloaded.

So, I want to run simple recovery, and backup the primary file group. The issue is, it's grayed out, and the only way I can get it opened up is by using Full or Bulk-Logged recovery model.

These other recovery models generate a 50-100gig log file, because again, my large database has lots of operations going on to daily rank stuff and store, etc etc.

Is there anyway to do what I want, simple recovery with a backed up primary file group, and if not, is there anyway I can get to somewhere closer then having these massive files full of data I just don't care enough to backup.

Thanks

bladefist
  • 355
  • 1
  • 3
  • 13
  • Are all the filegroups here read-write? If so, can your "mission critical" data be moved to a read-only filegroup? If so, you can backup read-only filegroups w/o having to flip to full recovery. – Ben Thul Nov 24 '11 at 02:48

2 Answers2

1

You could switch the database to full logged and setup a log backup to run every few minutes to a local or network disk and overwrite the previous backup. Just because the log is being backed up doesn't mean you actually need to keep the backups.
As has already been suggested, you could move the important data into a secondary filegroup that you could backup indepentantly.
Another option would be to put the important data into its own database. I don't know anything about the application that is using this data but you may be able to use views / synonyms so it looks to the application like everything is still in one database.

pipTheGeek
  • 1,152
  • 5
  • 7
  • If you backup the log file and disregard it, does that have _ANY_ impact on restoring a regular bak file? Would a regular restore still be 100% possible, or would it be asking for your log backup file? – bladefist Nov 23 '11 at 21:30
  • 1
    If you've taken a full backup using BACKUP DATABASE, you will be able to restore it to the point in time where the backup was taken. The restore operation does not depend on the presence of previous or subsequent log backups. Although if you **have** continuous logs from subsequent transactions, you could just re-apply the logs to the restored database and get a more recent set of data. – the-wabbit Nov 23 '11 at 21:39
0

SQL Server won't allow an explicit backup of the Primary File group unless your database recovery model is FULL or BULK-LOGGED.

What about "rebuilding" the database such that what you currently have on PRIMARY is placed on a secondary-file group? That might be not be practical/doable/or just crazy but otherwise I don't see how you can get there from here.

jl.
  • 1,076
  • 8
  • 10
  • How would that help? You'd still have a massive log file, and be unable to back up a single file in simple recovery.. – bladefist Nov 23 '11 at 21:23
  • Clearly I misunderstood when you twice mentioned your desire to "backup the primary file group". I presumed you were in a multiple file group scenario as I could not otherwise imagine the multiple references to the primary (default) group. I don't have details but separating database objects into different file groups might give you better control of what you back up, hence my suggestion. – jl. Dec 19 '11 at 23:12