December 2, 2011 at 9:26 am
We have a 1tb database that is expected to grow to 4tb. Right now we are doing full backup weekly with multiple daily logs. We have the database set up with filegroups and partitions. I have read some on backuing up by filegroup and the ability to restore by filegroup. I am wondering if anyone can point me to something that will give a more detailed description of the backup process by filegroups. Can I break the full backups into multiple days by filegroup to spread the load and if so then how do the daily logs correlate back to those full backups?
December 2, 2011 at 9:35 am
Partial Backups
http://msdn.microsoft.com/en-us/library/ms191539.aspx
Backing Up Specific Files or Filegroups
BACKUP DATABASE { database_name | @database_name_var }
<file_or_filegroup> [ ,...n ]
TO <backup_device> [ ,...n ]
[ <MIRROR TO clause> ] [ next-mirror-to ]
[ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]
[;]
Creating a Partial Backup
BACKUP DATABASE { database_name | @database_name_var }
READ_WRITE_FILEGROUPS [ , <read_only_filegroup> [ ,...n ] ]
TO <backup_device> [ ,...n ]
[ <MIRROR TO clause> ] [ next-mirror-to ]
[ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]
[;]
December 2, 2011 at 9:36 am
Also...
Performing File Restores (Full Recovery Model)
December 5, 2011 at 2:53 pm
I have looked at those and they are helpful. What I am really looking for though is if I set up a job that runs a filegroup backup for primary and filegroup1 on Sunday, Filegroup2 on Tuesday, Filegroup3 on Wednesday and run logs everyday. If I have to restore filegroup2 on Friday will I be able to recover all of the data from the Tuesday filegroup2 backup and the daily logs? Or do the logs only point to an actual full backup?
December 5, 2011 at 3:23 pm
If you're doing partial restores the important thing to note is that the files must be brought up to date with the DB. You can't restore a file and leave it at a time earlier than the rest of the DB.
Hence, if today (Tuesday) you had to restore the backup of File 4 for some reason and that file's last backup was on Saturday, you'd then need to restore the log backups from Saturday up to current time to bring that file up to date with the rest of the DB. Doesn't matter when/if other backups (full/diff) were taken during that period.
If you're backing different filegroups up different days, you MUST keep log backups back until at least the least recently backed up filegroup, otherwise you won't be able to restore the backup of that least recently backed up filegroup. Preferably keep log backups that cover 2 sets of backups comprising the DB, just in case (same as generally keeping log backups going back 2 full backups).
File and filegroup backups can get very complex, play around on a small test DB and make sure you're completely comfortable with the complexities. There's nothing quite like losing a DB because you mis-calculated how many days of log backups had to be retained. 😉
Also, before you make a fixed decision, try out some third party tools like RedGate's Hyperbak and SQLBackup, Quest's LiteSpeed and see if a mix of compressed full and differentials will work.
For piecemeal restores (restoring a complete database, one filegroup at a time), you want to read this: http://msdn.microsoft.com/en-us/library/ms177425.aspx It's not fantastic, but it's not an area that there's much written about (possibly too complex, or most people simply don't use it). Also this on partial database availability (I assume you have Enterprise Edition at that size DB) http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/PartialDBAvailability.doc
There's also a video (very deep) http://technet.microsoft.com/en-us/sqlserver/gg545009.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply