August 5, 2011 at 7:39 am
I'm doing a File Backup for our data warehouse and I have the data on several filegroups. One of the filegroups only contains data over 3 years old that does not change, however, I do need to push data onto it as it becomes over 3 years old, once a year.
If I want to only backup the active files nightly and, in an emergency possibly only restore those files and not the oldest filegroup, I believe I need to make that filegroup Read Only. Is that correct?
The biggest question I have is, can I make the oldest filegroup Read Only for most of the year, change it back to a normal filegroup when I want to push the oldest partition to it and then make it Read Only again when I'm done? Does this cause problems? Do I need to look at making more filegroups for every year I push data to a Read Only status?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 5, 2011 at 11:14 am
I've done that back-and-forth thing for archive tables many times without any ill effects.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 5, 2011 at 11:16 am
GSquared (8/5/2011)
I've done that back-and-forth thing for archive tables many times without any ill effects.
Awesome, thanks. I would normally just give it a try, but since this is all on production I wanted some reassurances first. I tried it with a small db I created, but I don't have space to do tests on a big database with large filegroups.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 5, 2011 at 12:01 pm
I've done it, but I made very sure I tooke a backup immediately before changing from read only to allow write, and again immediately after changing back. Maybe paranoia on my part - they tell me that I tend to be utterly paranoid about recoverability, security, the need for defensive programming everywhere, and error conatinment. I never used any of those particular backups (except when testing to be sure I could restore them).
Tom
August 5, 2011 at 12:29 pm
Tom.Thomson (8/5/2011)
I've done it, but I made very sure I tooke a backup immediately before changing from read only to allow write, and again immediately after changing back. Maybe paranoia on my part - they tell me that I tend to be utterly paranoid about recoverability, security, the need for defensive programming everywhere, and error conatinment. I never used any of those particular backups (except when testing to be sure I could restore them).
Not a bad idea, thanks. I'll try to work out where I'll have space for those backups.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 7, 2011 at 10:14 am
We intended that to be used with some of our partitioned tables, to save on backup space.
However, reality proved the data wasn't that "read only" after all, so we had to leave that path.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply