June 25, 2009 at 4:07 am
hi all,
i have created DATABASE using
create database d1
on primary
(Name='d1',filename='D:\New folder\d1.mdf',size=10Mb,maxsize=20Mb,filegrowth=5Mb),
filegroup dfg
(Name='dfg1',filename='D:\New folder\dfg1.ndf',size=5Mb,maxsize=10Mb,filegrowth=2Mb),
(Name='dfg2',filename='D:\New folder\dfg2.ndf',size=5Mb,maxsize=10Mb,filegrowth=2Mb)
log on
(Name='dog',filename='D:\New folder\dog.ldf',size=10Mb,maxsize=20Mb,filegrowth=5Mb)
MY problem is i want to transfer data from NORTHWIND database to above filegroup.How can i transfer it?by default will it go to mdf or to ndf file?
and My dfg1 is full what will happen?
Thanks,
Neerav
June 25, 2009 at 5:14 am
SQL Server works on the principle of proportional fill .The data will go in all the 3 files in such a way that all the files get filled at the same time and then will grow further .
You can use the export import wizard to move the data .
But if you need to move the data to a certain filegroup then that has to be mentioned during the time you create the table .
What you can do in case you want to create table in a particular file>> filegroup is :
create a database script from the existing databse >>create a new database using that script but modifying it as per your need by creating new filegroups and adding files to it >> export the data form the existing database to the new database .
Other option could be replication .But there might be some options that you will have to check .
HTH
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 25, 2009 at 6:02 am
hi_abhay78 (6/25/2009)
SQL Server works on the principle of proportional fill .The data will go in all the 3 files in such a way that all the files get filled at the same time and then will grow further .
Not quite. That's true if all three files were in one filegroup which is not true in this case.
Neerav:
You can specify which filegroup a table must go on using the WITH clause. If it's not specified it'll go on the primary filegroup by default. In your example, the only file in Primary is the mdf, so if you specify a table ON PRIMARY,
-- this one goes on the primary filegroup, ie, in the mdf file
CREATE TABLE tbl1 (
id int
)
ON Primary
-- this one goes on the second filegroup (dfg) and will be spread across the two .ndf files that you specified were part of that filegroup.
CREATE TABLE tbl2(
id int
)
ON dfg
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply