October 5, 2003 at 4:22 pm
Starting to design a database to be used under msde. The database will have about 150MB of data that will be read-only and need to be updated quarterly. Users will have on average 20mb of RW data. How can I most easily do the quarterly updates and provide a backup scheme (restores to last backup not up to the minute) that uses the minimum disk space. I was thinking of filegroups and attaching the new data when update is required but never done that. File group backups seem to require tlog backups. Need some advice to get started.
October 6, 2003 at 10:04 am
If the restores only need to be periodic and not up to the minute, then I'd use the "Simple" recovery model for each db. This will handle the logs for you, but you will need to schedule full backups. SQL Litespeed can help with minimizing disk space, but if this is lots of clients, might not be economical. You can always run the backup with the native tools and use Winzip to compress the files.
For the updates, perhaps a snapshot replication model for certain tables might be the best here.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
October 7, 2003 at 8:47 am
[For the updates, perhaps a snapshot replication model for certain tables might be the best here.]
Steve:
Thanks for the reply. The MSDE instances will not have connection to the home office so to distribute data I'll need to mail a cd or provided ftp download. If I put all the data that is readonly in one file group can I just detach a file group and then attach the new data? I only see references to detaching/attaching entire databases. I guess I could detach the database, overwrite a file group then attach but never tried that.
Likewise for the backups I'd like to avoid a full backup since a vast majority of the data in the fullbackup is read-only. Is it possible to exclude some filegroups from a fullbackup since they never change and could be replaced by a re-install from cd then the variable data replaced? TIA for any starting points.
October 7, 2003 at 10:02 pm
Not sure you can attach a filegroup. You can do a restore, so perhaps that is an option.
I believe snapshot replication can use ftp, but I'm not sure. Might do some digging in BOL and see.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
October 8, 2003 at 7:13 am
quote:
The MSDE instances will not have connection to the home office so to distribute data I'll need to mail a cd or provided ftp download. If I put all the data that is readonly in one file group can I just detach a file group and then attach the new data? I only see references to detaching/attaching entire databases. I guess I could detach the database, overwrite a file group then attach but never tried that.
Yes, this could work, but it can be touchy. Put the R/W tables on the primary filegroup and the R/O tables on a read-only secondary filegroup. Then, to make your update, detach the database, copy over the .ndf file, attach, and run DBCC UPDATEUSAGE (0) WITH COUNT_ROWS. Do not make any schema changes to the R/O part of the database, as you risk having the internal ids in the system tables not match the ids in the file. If the R/W data is also being updated from the home office, forget about filegroups and just send a database backup to be restored.
quote:
Likewise for the backups I'd like to avoid a full backup since a vast majority of the data in the fullbackup is read-only. Is it possible to exclude some filegroups from a fullbackup since they never change and could be replaced by a re-install from cd then the variable data replaced? TIA for any starting points.
No; not possible.
As you mentioned, you will not be able to use filegroup backup as you're using the Simple recovery model. It seems to me that you could, however, take and save a full backup after the R/O data is updated and then just a differential backup each night. Restoring would use the quarter's full backup plus the last differential backup.
--Jonathan
--Jonathan
October 8, 2003 at 8:35 am
Thank you very much for the all that information. Now time to experiment!
Jim kane
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply