| Author |
Topic |
|
lee.jenkins
Starting Member
5 Posts |
 Posted - 2009-04-29 : 01:13:59
|
| Recently, I am moving a database from one server to another server dueto the storage issue. The existing database is about 30G, all tablesare in one filegroup and one primary data file.I want to create separated filegroups and data files to re-organizethe whole database. My question is what is the best way to do that?One way I can restore the database still to one filegroup and one datafile, then I add filegroups and data files to the restored database,then alter tables to move to different filegroups. (I have a sidequestion about this too, I know I can Alter table to drop indexes withMove to option, and then recreate indexes to move tables to otherfilegroup, but I do have one table don't have any primary key, don'thave any index on it, how could I move that table then?)I am thinking about is that possible I can create database withfilegroups and data files first and then restore the database, duringthe restore process, can I specify the filegroups each table shoulduse?Or if there is any better way to do that?Thanks a lot! |
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2009-04-29 : 06:12:04
|
quote:
Originally posted by lee.jenkins
Recently, I am moving a database from one server to another server dueto the storage issue. The existing database is about 30G, all tablesare in one filegroup and one primary data file.I want to create separated filegroups and data files to re-organizethe whole database. My question is what is the best way to do that?One way I can restore the database still to one filegroup and one datafile, then I add filegroups and data files to the restored database,then alter tables to move to different filegroups. (I have a sidequestion about this too, I know I can Alter table to drop indexes withMove to option, and then recreate indexes to move tables to otherfilegroup, but I do have one table don't have any primary key, don'thave any index on it, how could I move that table then?)I am thinking about is that possible I can create database withfilegroups and data files first and then restore the database, duringthe restore process, can I specify the filegroups each table shoulduse?Or if there is any better way to do that?Thanks a lot!
you can use copy database option in SSMS to move the database If the servers are in a network.As per my knowledge u can't specify file groups for tables during restore.After restoring add secondary file group and move the tables either thru SSIS or by altering index.Regards,Ahmad Osama |
 |
|
|
lee.jenkins
Starting Member
5 Posts |
Posted - 2009-04-29 : 15:57:36
|
| Thanks a lot for the help. I did a little bit research, but did not find any useful guide on how to move table to other filegroup in SSIS, could you please give me some sample if you have. Also for the altering method, I am not sure if there is no index and primary key at all for some tables, can I still use alter statement to move table? Thanks for the help. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-04-29 : 18:26:33
|
| NO. you need to have clustered index created in table so that you can move it to other Filegroup. Create clustered index and move it. Spreading data files accross FG will be useful when separating accross multiple disks. |
|
|
|
lee.jenkins
Starting Member
5 Posts |
Posted - 2009-04-29 : 18:53:47
|
quote:
Originally posted by sodeep
NO. you need to have clustered index created in table so that you can move it to other Filegroup. Create clustered index and move it. Spreading data files accross FG will be useful when separating accross multiple disks.
Thanks, so I have to create clustered index for those tables which don't have it to move those tables to new FG, then remove the index. It will take a while to generate indexes for those tables. Another question, for non-clustered indexes, is it better to put them on separated logic drives other than the logic drive cluster index and table itself on?Thanks a lot! |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-04-29 : 21:43:43
|
| Depending on number of PHYSICAL drive ,you can partition your index data,data files,Tempdb files and log files ,backup files in separate drives to maximize performance. |
|
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2009-04-30 : 01:44:45
|
quote:
Originally posted by sodeep
Depending on number of PHYSICAL drive ,you can partition your index data,data files,Tempdb files and log files ,backup files in separate drives to maximize performance.
I wud rather say that depending on your database size u shud arrange for storage devices and place files as desired...Regards,Ahmad Osama |
|
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2009-04-30 : 01:51:19
|
quote:
Originally posted by lee.jenkins
quote:
Originally posted by sodeep
NO. you need to have clustered index created in table so that you can move it to other Filegroup. Create clustered index and move it. Spreading data files accross FG will be useful when separating accross multiple disks.
Thanks, so I have to create clustered index for those tables which don't have it to move those tables to new FG, then remove the index. It will take a while to generate indexes for those tables. Another question, for non-clustered indexes, is it better to put them on separated logic drives other than the logic drive cluster index and table itself on?Thanks a lot!
for table with out clustered index.....let's say that u have a table tbl_Customers and u want to change the file group1) Create a temp table #temptbl_Customers2) insert data into #temptbl_Customers from #tbl_Customers3) Drop and Create table tbl_customers with On 'Filegroup' option specifying the file group u want to transfer to OR make the secondary filegroup the default filegroup4) insert the data into #tbl_Customers from #temptbl_Customersu can wrap this up in an SSIS package....also in for each loop container u can select the tables with out clustered index and pass them to the procedureIn this way u will have ur task fully automatedRegards,Ahmad Osama |
|
|
|
lee.jenkins
Starting Member
5 Posts |
Posted - 2009-04-30 : 15:04:40
|
| Thanks guys, it really help me a lot. |
|
|
|
|
Question about restore database and create new fil