July 22, 2008 at 10:24 am
How can I move Primary filegroup to different drive. Right now, Primary filegroup is on I drive and have no space and I wanted to move it to K drive.
July 22, 2008 at 10:40 am
set the database offline and move the disk file(s) to the new location(s) then run
alter database dbname modify file
(name = logical filename,
filename = driveletter\path\filename.extension)
for each file you move.
Bring the database back online and new file location will be used
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 22, 2008 at 11:23 am
Perry.. That works great.
Thanks a lot.
One more question.
As we know it's good to have a Primary filegroup for system tables but right now all the data is storing in primary filegroup.
I would like to create a different filegoup and move all the tables from primary filegroup to new filegroup except system tables. Can I don that.
Thanks again.
July 22, 2008 at 11:29 am
balbirsinghsodhi (7/22/2008)
Perry.. That works great.I would like to create a different filegoup and move all the tables from primary filegroup to new filegroup except system tables. Can I don that.
Sure. Steps as following:
1. Create filegroup(s)
2. CREATE CLUSERED INDEX for every table you want to move to the newly created filegroup
Optional:
3. if you do not want the clustered index in some particular tables, then DROP INDEX....
DONE.:D
July 22, 2008 at 11:36 am
depending on your disk subsystem set up really, extra filegroups are only worth it if they will be placed on separate disks\arrays.
you create the filegroup using the alter database. Add the filegroup first then add a new file to the new filegroup. You can of course use SSMS or via T-SQL
alter database mydata
add filegroup newone;
then add the new file
alter database mydata
add file (name = logical name, filename = 'drive\path\filename.ndf',
size = 500MB) to filegroup newone
Once created move whatever objects you desire to the new filegroup
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 22, 2008 at 11:40 am
Thanks again.
But where is the command to move tables to new filegroups
July 22, 2008 at 11:47 am
as wildcat mentioned above, to move a table create a clustered index on that table into the new filegroup with drop existing on the tablename. Logically when you create the clustered index the table data and index move together as they are one of the same. The table must exist where the index exists.
Edit:
in fact alter table has a new option "MOVE TO" which you use with the DROP option
ALTER TABLE TABLE1 DROP CONSTRAINT PK_TABLE1 WITH (MOVE TO NEWONE)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 22, 2008 at 1:01 pm
Thanks again.
You guys rock.
Everything working fine.
July 22, 2008 at 1:06 pm
hey, happy to help 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 22, 2008 at 1:13 pm
Hi Balbir,
Filegroups cann't be moved they are logical, only database files which are phyiscal can be moved.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply