December 15, 2011 at 12:05 am
I have a large database split into multiple file groups. We have one filegroup for each year ( fg2011,fg2010,fg2012 etc ) and as each one maintains transaction data for that particular year.
The current year data gets inserted,updated and selected the most. The previous year data/file groups has majority select statements but very very rare updates ( cant mark them readonly)
the backup strategy was
* Take a full backup monthly 1 once.
* Take a file group backup of latest file group daily
* Take a differential backup daily
* Take hourly transaction log backup.
As I have to take differential and transaction backup, what is the advantage of having a file group backup.Under the current scenario, I expect filegroup backups to occupy more space than differential.
Can somebody suggest a better strategy. Data fault tolerance is 1 hour data.
Regards,
Raj
December 15, 2011 at 1:08 am
*Take a full backup monthly 1 once.
* Take a file group backup of latest file group daily
* Take a differential backup daily
* Take hourly transaction log backup.
If you are taking differential backup of database daily, then you don't need to take the filegroup backup. Mixing full backups & file backups along with differential can make your restore strategy fairly complex.
You can follow this:
1. Full Backup - Once in a month
2. Diff. backup - Daily
3. Log Backup - Every 1 hour
You can go through below links to understand it better & to know if you require any changes in your current strategy:
http://msdn.microsoft.com/en-us/library/ms175526.aspx
http://msdn.microsoft.com/en-us/library/ms178046.aspx
December 15, 2011 at 1:52 am
Before I answer this, what's your maximum downtime and what edition of SQL are you running? How long does it take to restore that monthly full, the latest diff and the log backups?
What's in the primary filegroup and how busy is it? Also, is the primary filegroup part of the daily filegroup backup or not?
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
December 15, 2011 at 2:21 am
1) Maximum downtime : 1 day
2) Edition: SQL 2008 R2 , Enterpirse
3) How long does it take to restore:10-12 hours
4) Primary filegroup contains mainly application meta data and it is not huge. It is a part of Filegroup backups.
Thxs Gail.
Regards,
Raj
December 15, 2011 at 2:39 am
Just to add, Primary file group is not busy as it contains only meta data. Few selects and very very rare write activity.
Regards,
Raj
December 15, 2011 at 3:22 am
A full day downtime is allowed? Wow, that's a lot of down time. No one's going to complain if the DB is down for 24 hours?
The reason for a filegroup backup like you have in Enterprise edition would be partial database availability and piecemeal restores. With the backup strategy you currently have, if the DB was lost you could use that filegroup backup to restore primary and the current filegroup to yesterday then roll the logs forward to bring part of the database up fast. Then later you could use the full backup, diff and logs to restore the older filegroups while the main portion of the DB was online.
That would be essential if your downtime allowance was much less than the time required to restore the DB, say if you were only allowed to be down but restoring the database takes 12.
Since your downtime allowance is 2x the restore time of the DB, that's no so necessary. Up to you if you still want the option or if you rather want to save the backup space.
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
December 26, 2011 at 4:12 am
Opening the topic again as I have doubt.
Assume,I perform a quick latest filegroup restore ,bring the database online and then transactions are issued against the partial database. After few days, I want to get the complete databases back. So, I attempt to restore the older filegroups and at the same time I want to have the transaction that were issued against the partial restoration. Is this possible?
I tried it a few times, but was failing with the 'restoration path of two databases are different' error. Can somebody clarify?
Regards,
Raj
December 26, 2011 at 7:57 am
Please verify your steps with following.
Performing File Restores (Full Recovery Model)
http://msdn.microsoft.com/en-us/library/ms190710.aspx
So, I attempt to restore the older filegroups and at the same time I want to have the transaction that were issued against the partial restoration. Is this possible?
IIRC there is no easy way. Your database would be consistent till last backup (restore) only. You have to manually SYNC up missing transactions (create two copies of DB then data import).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply