Filegroup backups/restore design problems

At the design stage of an application using SQL server 7 I assumed the
following could be done:
- Separate monthly buckets of data into separate filegroups.
- Flag current filegroups as readonly when the new month rollsover.
- design was proposed in this way to minimise backups of the entire
database
of which most will be read-only. Was I completely wrong ?
Backup:
I assumed we could backup the primary, active (current) filegroups and
the previous filegroup.
I assumed a complete db could be rebuilt using the primary and active
filegroup backup.
Is this possible ?
Is there an option on FULL DB BACKUPS to ignore read-only filegroups
and therefore facilitate restoration of a smaller db with only the
primary and active filegroups?
Archiving:
When using this approach - and imagining 2 years of filegroups -
Suppose I would drop one of the tables ?
Could you restore this archived filegroup to the current db ?
thanks
bill
bill
If you don't place the table on a separated physical disks you won't get a
perfomance benefit.
Did you consider to use a full backup database a week period and backup log
file a on an hour period?
"bill k" <bkatelis@.yahoo.com> wrote in message
news:b11556a0.0408020050.63e48688@.posting.google.c om...
> At the design stage of an application using SQL server 7 I assumed the
> following could be done:
> - Separate monthly buckets of data into separate filegroups.
> - Flag current filegroups as readonly when the new month rollsover.
> - design was proposed in this way to minimise backups of the entire
> database
> of which most will be read-only. Was I completely wrong ?
> Backup:
> I assumed we could backup the primary, active (current) filegroups and
> the previous filegroup.
> I assumed a complete db could be rebuilt using the primary and active
> filegroup backup.
> Is this possible ?
> Is there an option on FULL DB BACKUPS to ignore read-only filegroups
> and therefore facilitate restoration of a smaller db with only the
> primary and active filegroups?
> Archiving:
> When using this approach - and imagining 2 years of filegroups -
> Suppose I would drop one of the tables ?
> Could you restore this archived filegroup to the current db ?
>
> thanks
> bill
|||see inline
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"bill k" <bkatelis@.yahoo.com> wrote in message
news:b11556a0.0408020050.63e48688@.posting.google.c om...
> At the design stage of an application using SQL server 7 I assumed the
> following could be done:
> - Separate monthly buckets of data into separate filegroups.
> - Flag current filegroups as readonly when the new month rollsover.
> - design was proposed in this way to minimise backups of the entire
> database
> of which most will be read-only. Was I completely wrong ?
IF you are talking about 1 filegroup per month, you need to be aware that
the max # of filegroups per database is 256. Additionally, you would have to
name each of the partition tables differently, a single table can not span
multiple filegroups. Each filegroup can be marked as read-only... The idea
you have is correct however, rolling historical data into another filegroup
which doesn't need to be backed up as frequently is one of the primary
usages for filegroups... Maybe you roll everything up into annual
filegroups instead of monthly...
> Backup:
> I assumed we could backup the primary, active (current) filegroups and
> the previous filegroup.
> I assumed a complete db could be rebuilt using the primary and active
> filegroup backup.
> Is this possible ?
You may restore ANY filegroup individually... What you might do , is put
the historical filegroups on a separate raid array from the current
information. Then back the historical stuff up monthly when it changes, and
keep the backups for (maybe) 3 months.

> Is there an option on FULL DB BACKUPS to ignore read-only filegroups
> and therefore facilitate restoration of a smaller db with only the
> primary and active filegroups?
No there isn't... Your full database backup will have to specify the
filegroups you wish to backup... I would still do a REAL full database
backup occasionally.

> Archiving:
> When using this approach - and imagining 2 years of filegroups -
> Suppose I would drop one of the tables ?
> Could you restore this archived filegroup to the current db ?
You may restore any filegroup independently, but everthing in the filegroup
will be restored... You may also do a partial restore to another database,
then pull only the tables you need back into the production database..

>
> thanks
> bill
Your thinking is sound... But before you go production make sure you do
testing to ensure your plan works as you intend...
|||Wayne,
I appreciate your comments.
As for the 1 table per filegroup - yes I am doing that and am aware of
the 256 limit. And yes my table are named differently and they do not
span filegroups - they are created dynamically in their own filegroup
and with a yyyymm extension on each.
Could I please ask you to clarify further my understanding.
You mention that you can do a partial restore but from my
reading/understanding this can only be done on a full db backup.
To recreate my db as proposed I understood that I would need to
1. restore primary filegroup
2. restore active filegroup
3. restore read-only filegroups
4. restore ALL transaction log backups since the last read-only
filegroup backup.
Is this correct ?
And can I do the following on a completely new server:
1. restore primary filegroup
2. restore active filegroup
3. restore ALL transaction log backups since the last primary/active
filegroup backup.
thanks
bill
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:<uD0$2vIeEHA.592@.TK2MSFTNGP11.phx.gbl>...
> see inline
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "bill k" <bkatelis@.yahoo.com> wrote in message
> news:b11556a0.0408020050.63e48688@.posting.google.c om...
> IF you are talking about 1 filegroup per month, you need to be aware that
> the max # of filegroups per database is 256. Additionally, you would have to
> name each of the partition tables differently, a single table can not span
> multiple filegroups. Each filegroup can be marked as read-only... The idea
> you have is correct however, rolling historical data into another filegroup
> which doesn't need to be backed up as frequently is one of the primary
> usages for filegroups... Maybe you roll everything up into annual
> filegroups instead of monthly...
> You may restore ANY filegroup individually... What you might do , is put
> the historical filegroups on a separate raid array from the current
> information. Then back the historical stuff up monthly when it changes, and
> keep the backups for (maybe) 3 months.
>
> No there isn't... Your full database backup will have to specify the
> filegroups you wish to backup... I would still do a REAL full database
> backup occasionally.
> You may restore any filegroup independently, but everthing in the filegroup
> will be restored... You may also do a partial restore to another database,
> then pull only the tables you need back into the production database..
>
> Your thinking is sound... But before you go production make sure you do
> testing to ensure your plan works as you intend...