Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sumwanlah
Starting Member
43 Posts |
 Posted - 2002-05-22 : 05:52:37
|
| Hi Hi! Here's the situation. I've got a VLDB, with the data broken down into monthly tables. The previous setup for the datafile was a no-brainer: everything into a single physical file, with default filegroup.Since then I've created new files and a new filegroup. With this, I've moved old monthly tables into the new file and its respective filegroup. Of course, the indices that come along with these tables are moved along too.Now the whole purpose of this is to cut down on the time it takes to restore a single file. By doing this method I thought I can backup these old tables onto tape only once, then safekeep it until there is a need to restore them.Which means, the primary filegroup that contains the new monthly data is backed up frequently, whereas the secondary filegroup is backed up one time, then kept aside till recovery is required. Would this be a logical model to adopt? My objectives are pretty simple. To cut down on the restoration time. I wa thinking also that in the event of a recovery, I will restore the primary filegroup first, then allow for users to use the database, then unknown to the users, restore the secondary filegroup in the background.Can anybody understand this?!?! Hahahaha!!!! |
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-05-22 : 11:43:59
|
| I've done something similar with one of our databases. New data is sent to a table on one file group, while archived data is moved to a table on a secondary filegroup. I can restore archived data, or back it from the archive group without affecting performance on the active table with the new data (which receives data from an automated telephone system). This allows us to keep the 'live' data relatively small, while maintaining several months of archives within the same database. Like you, I only need to restore the new data to be 'up and running', and can restore the archives at my leisure.It's worked well for me so far.... |
 |
|
|
sumwanlah
Starting Member
43 Posts |
Posted - 2002-05-24 : 03:13:06
|
| Hey Kev, just saw this posting from last year, relating to a similar query: "filegroups backup" [url]http://sqlteam.com/Forums/topic.asp?TOPIC_ID=9097[/url]From wat I've read these guys are saying that it's best to avoid filegroup backups.If it's of any help, what I've done now is to switch the secondary filegroup, containing old monthly data, to 'read-only'. The way I see it, log backups are not applicable since I'm getting the bulk of my new data monthly only. If this is the case, seems differential backup is a better way to go then, isn't it?But if filegroup backup is the way to go, how can I perform the restore to ensure that the respective files are in synch? |
|
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-05-24 : 15:52:57
|
| OK. I played with it and have changed my mind."The primary filegroup is always restored along with the filegroups selected to restore. "This alone limits much of the advantage you wanted. If you want to restore the database in its entirety, its all or nothing.I was able to do this-After backing up the transaction log, I was able to restore a single PRIMARY file group , even after transactions had occurred on the secondary group. I had to apply all relevant transaction logs (Microsoft suggests you can skip this step if there are no changes on the secondary file group).If your 'OLD DATA' is in fact read only and unchanging, Then this means you could get away with updating your PRIMARY File group regularly, and your ARCHIVE File group occasionally.......but having played with it, and been educated by your article link, I'm now convinced you would be safer to put the archived data in a separate database, and access it from the original database trhough a view for your users. Then you can back up or recover the databases at will, logging only the active database and leaving your archived one to truncate on checkpoint (Speedy recovery was the original intent, right?).I got some disturbing errors while attempting to restore single filegroups I had backed up in my test database. After I got the process down, it worked OK, but I'd hate to stake my job on it.I still have file groups on the database I referenced earlier, but I'm doing FULL backup and restores only. It's only so our developers can slaughter and abuse the archived records without bringing down production tables.Good thing you got a second opinion, huh? |
|
|
|
sumwanlah
Starting Member
43 Posts |
Posted - 2002-05-27 : 06:58:31
|
| Jeez... It's a bummer then. Thought I can make something out of this. Hah! Anyway, I think it may be a good idea to push the OldData to a separate database. Good suggestion, Kev.But I think I should give this filegroup backup service another chance. It might have it's positives still, despite this little case that I have. Hah!What goes around...? Is my belt! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-27 : 08:41:14
|
| You could do filegroup backups if you don't keep ANY of the major data in the Primary filegroup (just system tables and any ancillary tables you may have) Make a new filegroup for each month and create the tables on that filegroup only. If you need to move an existing table, create a clustered index on the new filegroup; it will physically move the data to that file/filegroup. Check Books Online for the exact syntax. |
|
|
|
sumwanlah
Starting Member
43 Posts |
Posted - 2002-05-28 : 05:42:37
|
| rob, if I'm reading your reply correctly, you're suggesting I split my data into multiple (in this case, monthly) filegroups, yes? This is a nice idea considering the potential size of each file is about 300Megs.But if I read it a little more, it starts to confuse me that bit. If I were to create new filegroups for each month's worth of data, within a year, based on your recommendations, I would end up with a Primary and 12 Secondaries! Is that right? Wouldn't there be a logical restriction on the number of filegroups possible? Even if there isn't a logical restriction, wouldn't there be a physical one instead?Now, I was just reading SQL Server Magazine's May '02 edition. It has an article about restoring databases. Within, there's a sidebar about restoring files and filegroups. It says that it is possible to selectively restore any filegroup, just needing to make sure that the transaction logs are applied from point of backup.Does this mean that say if I've got a year's worth of filegroups, I can decide to restore filegroup for the month of April, ?on top of the existing database?, and then restore the logs? Consequently, if I decide to restore another filegroup on top of this, I should rerun the logs restore right?As for moving existing tables and indexes, am I right to use the table's "Design Properties" option, where I can simply select the new filegroup upon which the index will reside?------------------------What goes around...? Is the belt around my waist! |
|
|
|
|
|
|
|
|
VLDB Restore Puzzle