filegroup restore problem

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
--Harvinder
Note: Already reviewed this article
http://support.microsoft.com/default...;EN-US;Q295371
Following are the steps I am testing this:
1) complete/full database backup
2) create Jan filegroup
3) populate data into Jan as well as primary filegroup
4) transaction log backup
5) put Jan as Read only
6) Jan filegroup backup
7) create Feb filegroup
8) populate data into Feb as well as primary filegroup
9) transaction log backup
10) put Feb as Read only
11) Feb filegroup backup
12) create Mar filegroup
13) populate data into Mar as well as primary filegroup
14) transaction log backup
15) put Mar as Read only
16) Mar filegroup backup
17) Create Apr filegroup
18) populate data into Apr as well as primary filegroup
19) 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
Consider differential backups,, perhaps on a weekly basis. This way, you
restore the filegroup, then the most-recent differential, then the remaining
logs.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
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
--Harvinder
Note: Already reviewed this article
http://support.microsoft.com/default...;EN-US;Q295371
Following are the steps I am testing this:
1) complete/full database backup
2) create Jan filegroup
3) populate data into Jan as well as primary filegroup
4) transaction log backup
5) put Jan as Read only
6) Jan filegroup backup
7) create Feb filegroup
8) populate data into Feb as well as primary filegroup
9) transaction log backup
10) put Feb as Read only
11) Feb filegroup backup
12) create Mar filegroup
13) populate data into Mar as well as primary filegroup
14) transaction log backup
15) put Mar as Read only
16) Mar filegroup backup
17) Create Apr filegroup
18) populate data into Apr as well as primary filegroup
19) 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
|||In addition to Tom's post:
What you are asking for is a planned feature for SQL Server 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
> 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
> --Harvinder
> Note: Already reviewed this article
> http://support.microsoft.com/default...;EN-US;Q295371
> Following are the steps I am testing this:
> 1) complete/full database backup
> 2) create Jan filegroup
> 3) populate data into Jan as well as primary filegroup
> 4) transaction log backup
> 5) put Jan as Read only
> 6) Jan filegroup backup
> 7) create Feb filegroup
> 8) populate data into Feb as well as primary filegroup
> 9) transaction log backup
> 10) put Feb as Read only
> 11) Feb filegroup backup
> 12) create Mar filegroup
> 13) populate data into Mar as well as primary filegroup
> 14) transaction log backup
> 15) put Mar as Read only
> 16) Mar filegroup backup
> 17) Create Apr filegroup
> 18) populate data into Apr as well as primary filegroup
> 19) 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
>
>
|||Tibor,
You mentioned that this will be new feature in sql server 2005. I don't see
any white paper on microsoft web site regarding backup on sql server 2005.If
you get this message and if u have any information on this topic do let me
know
Thanks
--Harvinder
"Tibor Karaszi" wrote:

> In addition to Tom's post:
> What you are asking for is a planned feature for SQL Server 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
> news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
>
>