Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rubs_65
Posting Yak Master
144 Posts |
 Posted - 2004-10-12 : 12:52:08
|
| Hi,We are planning to implement filegroup backup strategy for one of our big database. We are planning to divide the database by dates so that jan data will be in 1 filegroup and feb data in separate filegroup so basically we will have 12 filegroups per year. As the month finish we will put the filegroup as read only and take the filegroup backup and then later on if we need to recover this filegroup in case of disaster we just need to restore this filegroup backup and don’t need to apply all the log files after the filegroup as this is read only and sql server should assume that since this is read only it should not expect log files after this filegroup restore. But this is not happening: when I restore the filegroup backup sql server still force me to apply all the log files after that. But this will mean we have to keep all the log files need for recovery ..so in fact we don’t have advantage of putting filegroup as readonly. So any suggestions on how to avoid applying log files or we are looking for feedback about how other people are doing this?Thanks--HarvinderNote: Already reviewed this article http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q295371Following are the steps I am testing this:1) complete/full database backup2) create Jan filegroup 3) populate data into Jan as well as primary filegroup4) transaction log backup5) put Jan as Read only6) Jan filegroup backup7) create Feb filegroup 8) populate data into Feb as well as primary filegroup9) transaction log backup10) put Feb as Read only11) Feb filegroup backup12) create Mar filegroup 13) populate data into Mar as well as primary filegroup14) transaction log backup15) put Mar as Read only16) Mar filegroup backup17) Create Apr filegroup18) populate data into Apr as well as primary filegroup19) If at this point we lost Datafile belonging to Feb filegroup I expect only to apply backup taken at step 11) but SQL Server forced me to take the log backup of tail and apply backups taken at step 11, 14, t-log tail backup i.e. all the transaction log backups after filegroup backup |
|
|
|
|
|
Filegroup Recovery question