July 26, 2011 at 5:52 am
My client wants me to create a number of quarterly read only filegroups on an ever growing database, and backup each file group just once when it's set to read only, and then verify it's good, so they never have to back it up again - just use partial restore.
I see their point, and that would work if disks were infinite, but they aren't, so I wondered if there's any way of moving the older file/filegroups to another disk without detaching all the files, and without "undoing" the readonly status of the filegroup and doing a merge/split. I can't think of one, but maybe someone can?
They have a thing about 24/7 availability worldwide.
Thanks.
July 26, 2011 at 6:45 am
No. Not without removing the readonly state.
ALTER DATABASE [SSCTest] MODIFY FILEGROUP [FGUserData] READONLY ;
-- The filegroup property 'READONLY' has been set.
alter database SSCTest
MODIFY FILE ( NAME = SSCTest_Data2, FILENAME = 'D:\MSSQL10_50.GNKD006\MSSQL\DataUserDb\SSCTest_FUserdata1.NDF' )
;
/*
Msg 5048, Level 16, State 3, Line 1
Cannot add, remove, or modify files in filegroup 'FGUserData'. The filegroup is read-only.
*/
And even then, to be able to move and re-activate the file, you must take the db offline !
The option offline that can be used with alter database modify file (name=x,offline) needs a restore to re-activate that file.
Remark from BooksOnline:
Use this option only when the file is corrupted and can be restored. A file set to OFFLINE can only be set online by restoring the file from backup. For more information about restoring a single file, see RESTORE (Transact-SQL).
This scenario may open some opportunities, but you'll have to test it to see if it works and if it fits you needs.
Have a look at partial restores in books online.
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
July 26, 2011 at 7:25 am
removed duplicate :blink:
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
July 26, 2011 at 7:39 am
AntoDN (7/26/2011)
My client wants me to create a number of quarterly read only filegroups on an ever growing database, and backup each file group just once when it's set to read only, and then verify it's good, so they never have to back it up again - just use partial restore.I see their point, and that would work if disks were infinite, but they aren't, so I wondered if there's any way of moving the older file/filegroups to another disk without detaching all the files, and without "undoing" the readonly status of the filegroup and doing a merge/split. I can't think of one, but maybe someone can?
They have a thing about 24/7 availability worldwide.
Thanks.
As READ Only status has to be changed, you could consider this option going forward.
1) Create a new data file on new filegroup separate disk.
2) Based on client request move the table data on to this new filegroup as in step 1
3) After this make this new Filegroup READ only.
You could do this for future client requests.
M&M
July 26, 2011 at 8:20 am
Thanks, ALZDBA - I guess you're confirming what I figured, that there's no elegant way to do this, but as I'll have the database in single user mode in order to switch the relevant file group to readonly, I might as well detach it, move the file with xp_cmdshell, and then attach it again from its new location - no one's gonna know! Or better, just do another backup - if only!
July 26, 2011 at 8:28 am
Ya but if you want to minimize downtime you could possibly just copy the data, then do the switching. That way the downtime would be minimal.
Could partitioning be used here (Never used it so I don't know). I know there's a way to drop or move partitions in and out that is really fast.
July 26, 2011 at 8:43 am
Thanks mohammed,
As READ Only status has to be changed ....
and Ninja's_RGR'us
Could partitioning be used here (Never used it so I don't know). I know there's a way to drop or move partitions in and out that is really fast.
This is a partitioned database; once a file group is readonly, there's no need to do another backup of the file group until you undo the read only, and (as is becoming clear), you can't easily move the file group to another disk without undoing the readonly status.
What I was aiming for was to keep the read only status unchanged, so as to avoid having to take another backup :-). If I could undo it, it would be easy.....!
July 26, 2011 at 8:48 am
Sounds like the only real solution is awesome (proper might not be enough) pre-planning and growth analysis.
Sounds like a lot of fun!
July 26, 2011 at 1:18 pm
Ah, partitioning involved .... Can you use Sliding window technique to offload ?
Problem will then be the data no longer resides in the partitioned table, pro = you can build an archive table residing on "slower but larger" media. Depends on your business case.
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 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply