April 24, 2003 at 8:09 am
We are in the process of putting up a new DB that will eventually grow to over 1.5 TB. The data is time oriented by year with more recent data getting updated more frequently. The databased is designed with data from different years in different filegroups. I am trying to set up a backup scheme using filegroup backups and would like to hear about any experiences with them or where I can go for resources on implementing backups for VLDBs.
April 24, 2003 at 10:00 am
You should separate the biggest tables in differents data files each on a separate filegroup. Then backup separately those filegroups during the week. The recovery strategy can get complicated with many filegroups to backup, so you should be carefully designing your strategy, and I suggest when posible, to simulate a complete restore process to check that you restored right the backups.
April 24, 2003 at 12:21 pm
Its pretty messy when you have large db's that span over quite a few filegroups. Like said biggest tables in seperate filegroups, but to what you said they are categorized under year wise data. Best bet would be to have differential backups on a daily basis and once or twice full database backup based on space and requirement. You need to test couple of time the recovery process, so that you will have the script in place when need arises. you can try with some dummy db and different filegroups model(same drive with different folder names for each filegroup). By this you want get the pressure when you need to restore. Thats how i set mine up.
Good luck.
April 25, 2003 at 3:44 am
Another thing you need to keep in mind is referential integrity. I usually put static items in on FileGroup and for performance reasons may seperate large tables that a related into other filegroups.
But if they are related do the backups together to ensure consitancy in the data should a restore have to occurr. At that point you restore together.
Unrelated tables within seperate filegroups restore fine backing up seperately. Also, if you choose to do seperate filegroups try to remove everything from the primary filegroup and back it up every time a change in strutucture occurrs. Or with each backup you perform. When you restore this one thou you need to run DBCC UPDATEUSAGE to make sure it reports what is reflected in the Database with sysindexes for sizes.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply