July 17, 2010 at 6:02 am
I have a database with 20 ndf files, these are partitions based on year, now i would to make 2 different databases out of it each with 10yrs data, can i just dettach the database and attach it to 2 different database's with 10 ndf files each ?
July 17, 2010 at 7:34 am
Unlikely.
What you can do is make a copy of the DB (backup/restore) then delete the partitions and filegroups that you don't need from each database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 17, 2010 at 9:17 am
GilaMonster (7/17/2010)
Unlikely.
I'd say 'just about impossible'. Even if a piecemeal restore was done, and the unneeded file groups were marked defunct, there would be no way to remove the unavailable partitions, rebuilding indexes would be impossible, and queries against the table would raise an error warning of missing data due to the off-line file groups.
What you can do is make a copy of the DB (backup/restore) then delete the partitions and filegroups that you don't need from each database.
A much better idea.
July 17, 2010 at 10:06 am
I like Gail's idea. Attach back to two servers, remove the filegroups from each that you don't like.
July 17, 2010 at 11:32 am
OK i have the partitions made based on year and if i delete paritions older than 10 years from it, does it have only that data i need after 10 yrs, in that case what would be there in the mdf file ? does it also delete data from mdf file realted to 10 yrs old data.
July 17, 2010 at 11:42 am
You're going to have to explain in more detail what tables you have, how they are partitioned, where the partitions are and how the file/filegroups are set up for us to help out much more here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 17, 2010 at 11:51 am
Tara-1044200 (7/17/2010)
OK i have the partitions made based on year and if i delete paritions older than 10 years from it, does it have only that data i need after 10 yrs, in that case what would be there in the mdf file ? does it also delete data from mdf file realted to 10 yrs old data.
The traditional way to efficiently delete data from a partitioned table is to use ALTER TABLE SWITCH to move the data to a separate table, use TRUNCATE TABLE on that dummy table, and then ALTER PARTITION FUNCTION MERGE RANGE to remove the (now empty) redundant partition. Once that's all done you can remove any empty files or file groups, if you want to be tidy about it.
If you want more detail, you'll have to provide more information, as Gail said.
Can I ask whether you would consider yourself a beginner, intermediate or expert with partitioning? It would help to know, so I don't have to guess how technical to make my answers...
July 17, 2010 at 12:21 pm
If space is an issue, would it be an alternative to use the "sliding window" technique to slide out the top left or right side of your partitioned objects, define en new PS and PF that map to the filegroups, slide the filegroup tables back in and then migrate the new partitioned object using partial restores ?
edited:
Off course that wouldn't make sense, because I didn't alter the filegroup(s). So the data would still be in the same files as originally, ....
Still, if I find some time, I'll walk over a partial restore scenario to see if missing filegroups cannot be dropped (that would then be just a catalog operation).
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 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply