Complicated Backup Strategy Help Requested

I have a fairly complicated backup scenario I could use some help with.
We have a SQL 2000 database loaded up (by batch processing) to monthly
tables (Table_200507, Table_200508, Table_200509, etc.). Once the
month is complete, the data in that table NEVER changes. Additionally,
once the month is complete, we alter the indexes on that table to 100%
fill factors to conserve space. Because we do batch processing, and it
is easy to recreate lost data, we use SIMPLE recovery mode. So, in
summary, we have one live monthly table, and a slew of older, read-only
monthly archive tables.
I want to implement a solution where after each month is over, I can
backup the data for that single month only. Additionally, I want to
support the ability to restore any single month to a new database.
I thought I would be able to do this with filegroups and filegroup
backups; however, SQL appears to be holding me back. I see 2 problems.
First problem - Can't use SIMPLE recovery model with filegroup backups.
I tried to get around that by setting my database to FULL, creating
the backup, then resetting the database back to SIMPLE. The problem
there is that upon restoring the backup, it wants all the subsequent
log backups (which don't exist) even though the data in that particular
filegroup is unchanged.
Second problem - Need to restore all files to a new database before
database will come online. I tried restoring my single filegroup
backup to a new database. SQL told me it first needed the PRIMARY
filegroup. Fine. I made a backup and restored that PRIMARY filegroup
first. Then I restored my single filegroup backup. Fine. Only
problem is that the database has been left in "Loading" status. I
tried issuing a RESTORE DATABASE WITH RECOVERY and got:
Server: Msg 5180, Level 22, State 1, Line 1
Could not open FCB for invalid file ID 2 in database '<db name>'.
Is there any way to use sql to accomplish my goal of usable monthly
backups? One other limitation is that I do not want to create multiple
databases (complicated business reasoning - please trust me). Any help
or advice with similar situations would be much appreciated.
Thanks.If you only have one table that has data you need to backup why not BCP out
the data each night and back that file up? If you have a way to reproduce
the database and all the objects in it you can rebuild by bcping the data
back in.
--
Andrew J. Kelly SQL MVP
<jalbenberg@.yahoo.com> wrote in message
news:1126291436.212696.148970@.g44g2000cwa.googlegroups.com...
>I have a fairly complicated backup scenario I could use some help with.
> We have a SQL 2000 database loaded up (by batch processing) to monthly
> tables (Table_200507, Table_200508, Table_200509, etc.). Once the
> month is complete, the data in that table NEVER changes. Additionally,
> once the month is complete, we alter the indexes on that table to 100%
> fill factors to conserve space. Because we do batch processing, and it
> is easy to recreate lost data, we use SIMPLE recovery mode. So, in
> summary, we have one live monthly table, and a slew of older, read-only
> monthly archive tables.
> I want to implement a solution where after each month is over, I can
> backup the data for that single month only. Additionally, I want to
> support the ability to restore any single month to a new database.
> I thought I would be able to do this with filegroups and filegroup
> backups; however, SQL appears to be holding me back. I see 2 problems.
> First problem - Can't use SIMPLE recovery model with filegroup backups.
> I tried to get around that by setting my database to FULL, creating
> the backup, then resetting the database back to SIMPLE. The problem
> there is that upon restoring the backup, it wants all the subsequent
> log backups (which don't exist) even though the data in that particular
> filegroup is unchanged.
> Second problem - Need to restore all files to a new database before
> database will come online. I tried restoring my single filegroup
> backup to a new database. SQL told me it first needed the PRIMARY
> filegroup. Fine. I made a backup and restored that PRIMARY filegroup
> first. Then I restored my single filegroup backup. Fine. Only
> problem is that the database has been left in "Loading" status. I
> tried issuing a RESTORE DATABASE WITH RECOVERY and got:
> Server: Msg 5180, Level 22, State 1, Line 1
> Could not open FCB for invalid file ID 2 in database '<db name>'.
> Is there any way to use sql to accomplish my goal of usable monthly
> backups? One other limitation is that I do not want to create multiple
> databases (complicated business reasoning - please trust me). Any help
> or advice with similar situations would be much appreciated.
> Thanks.
>|||>I want to implement a solution where after each month is over, I can
>backup the data for that single month only. Additionally, I want to
>support the ability to restore any single month to a new database.
Create a "backup DB" on another server and export it there once a month.
--
ChrisR
"jalbenberg@.yahoo.com" wrote:
> I have a fairly complicated backup scenario I could use some help with.
> We have a SQL 2000 database loaded up (by batch processing) to monthly
> tables (Table_200507, Table_200508, Table_200509, etc.). Once the
> month is complete, the data in that table NEVER changes. Additionally,
> once the month is complete, we alter the indexes on that table to 100%
> fill factors to conserve space. Because we do batch processing, and it
> is easy to recreate lost data, we use SIMPLE recovery mode. So, in
> summary, we have one live monthly table, and a slew of older, read-only
> monthly archive tables.
> I want to implement a solution where after each month is over, I can
> backup the data for that single month only. Additionally, I want to
> support the ability to restore any single month to a new database.
> I thought I would be able to do this with filegroups and filegroup
> backups; however, SQL appears to be holding me back. I see 2 problems.
> First problem - Can't use SIMPLE recovery model with filegroup backups.
> I tried to get around that by setting my database to FULL, creating
> the backup, then resetting the database back to SIMPLE. The problem
> there is that upon restoring the backup, it wants all the subsequent
> log backups (which don't exist) even though the data in that particular
> filegroup is unchanged.
> Second problem - Need to restore all files to a new database before
> database will come online. I tried restoring my single filegroup
> backup to a new database. SQL told me it first needed the PRIMARY
> filegroup. Fine. I made a backup and restored that PRIMARY filegroup
> first. Then I restored my single filegroup backup. Fine. Only
> problem is that the database has been left in "Loading" status. I
> tried issuing a RESTORE DATABASE WITH RECOVERY and got:
> Server: Msg 5180, Level 22, State 1, Line 1
> Could not open FCB for invalid file ID 2 in database '<db name>'.
> Is there any way to use sql to accomplish my goal of usable monthly
> backups? One other limitation is that I do not want to create multiple
> databases (complicated business reasoning - please trust me). Any help
> or advice with similar situations would be much appreciated.
> Thanks.
>|||Great idea - sometimes the "low tech" solutions are the best ones.
Thanks.