December 17, 2002 at 7:59 pm
I have created a few files group for NDF file group and LDF file group because of not enough harddisk space. After upgraded the harddisk, I will like to combine back all the file group into one MDF file and one LDF file. Did anyone done this before?
Thanks in advance
December 17, 2002 at 8:48 pm
First, move all the objects back to your mdf file group.
Then, use
"ALTER DATABASE REMOVE FILE "
"ALTER DATABASE REMOVE FILE GROUP"
to drop the specified file & the fille group.
December 17, 2002 at 8:58 pm
You can set the mdf as the default and recompile stored procedure and views to move them.
For tables, create a clustered index on the mdf.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 17, 2002 at 9:02 pm
Hi, I have too many table's in the NDF file. Is there a faster way to move all the table from NDF to MDF file group?
December 17, 2002 at 9:53 pm
quote:
Hi, I have too many table's in the NDF file. Is there a faster way to move all the table from NDF to MDF file group?
You can find the tables which are located in the ndf files and put them into the cursor, and then , u can use sp_msforeachtable to do it automatically to move them to your mdf file.
December 18, 2002 at 4:33 am
ALso, are you seperating tables into individual filegroups or is it one table spanning multiple filegroups. If the later you just have to run DBCC SHRINKFILE with the EMPTYFILE option to push all the data from each file but I would fix each NDF so they cannot grow anymore and only the MDF wil accept the data. You can save yourself time by build your command using the sysfiles table liks so.
select 'DBCC SHRINKFILE (''' + RTRIM([name]) + ''', EMPTYFILE)' from sysfiles where [filename] like '%.ndf%'
You can also build the entire script of dropping the file with ALTER DATABASE REMOVE FILE NAME = '' and even ALTER DATABASE MODIFY FILE NAME='' FILEGROWTH = 0. Then you run the script you build this way.
If the other as long as you don't have the text/image data moved from the primary filegroup or the indexes (other then the clustered), you could script to ALTER the table to drop the clustered index and then build then clustered index on the primary filegorup. This will in effect move the table and data back. Again thou if you set the text/image or non-clustered indexes to another location the best method I have seen is EM to deal with that, maybe a DMO solution could do but I have not tried.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply