Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

.mdf question Expand / Collapse
Author
Message
Posted Wednesday, June 24, 2009 4:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 29, 2009 6:31 AM
Points: 39, Visits: 132
hi all,
In my database i want to create new *.mdf file and i want to transfer selected tables frmo old mdf to new one.Is this possible?

If yes how to create and how to move data from old mdf to new mdf?


Thanks,
Neerav
Post #740896
Posted Wednesday, June 24, 2009 5:52 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 13, 2014 12:27 PM
Points: 267, Visits: 386
create a new database and import the tables form the old database .

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
Post #740935
Posted Wednesday, June 24, 2009 5:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 10:03 AM
Points: 1,096, Visits: 1,334
neerav.saini (6/24/2009)
hi all,
In my database i want to create new *.mdf file and i want to transfer selected tables frmo old mdf to new one.Is this possible?

If yes how to create and how to move data from old mdf to new mdf?


Thanks,
Neerav


If i understand you, think you are talking about adding aditional datafiles (segment/partitions). This can be done and relatively eaasily. Why do you want to do this though?

Or have i misunderstood. Either way look at BOL under 'Data partition'.


Adam Zacks

-------------------------------------------

Be Nice, Or Leave
Post #740938
Posted Wednesday, June 24, 2009 6:04 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 11:21 PM
Points: 646, Visits: 1,303
You can add data file using alter database
ALTER DATABASE Database_name ADD FILE
(NAME='Logical_name',
FILENAME='Physical_name')

You can move data using
DBCC SHRINKFILE('logical_name','EMPTYFILE')

But I dont know wether it works completely for primary filegroup with system meta data

If you want to move the the file why dont you detach move and attach?


-- Roshan Joe
*******************************************
There are two types of DBAs. Those who has skills and those who have permissions
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #740946
Posted Wednesday, June 24, 2009 6:22 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 9,928, Visits: 11,196
My guess (FWIW) is that the question is about moving a table to a secondary filegroup (typically named *.NDF)

If so, the following MSDN article explains files and filegroups and links to the SQL DDL statements required:

http://msdn.microsoft.com/en-us/library/ms189563(SQL.90).aspx




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #740966
Posted Wednesday, June 24, 2009 7:20 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:42 AM
Points: 646, Visits: 732
so you want to create a secondary Datafile (.ndf) and create new table(s) to ndf.
that is easy. But it would a good practise to add a filegroup and then add the ndf to the new filegroup.. this will help in database maintenance tasks, as backup just the primary filegroup etc... but it depends in your scenario: and the most important part is SQL Server will never know to place any new objects on the secondary file, but you can create the object with the ON clause in the new Filegroup... correct me if i am wrong.

and regarding moving the Table.. you cannot as per my understanding, you can only specify create table with on clause to create it in the new filegroup.

Alter Database DBNAME
ADD FILE
(
NAME = NewFileName,
FILENAME = 'PathToFile.ndf',
SIZE = xxx,
FILEGROWTH = xxx
)



Maninder
www.dbanation.com
Post #741013
Posted Wednesday, June 24, 2009 7:31 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 9,928, Visits: 11,196
Mani,

You can move a table from one file-group to another by rebuilding the clustered index with the ON clause.
Non-clustered indexes work the same way but would have to be done separately.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #741020
Posted Wednesday, June 24, 2009 7:42 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:42 AM
Points: 646, Visits: 732
paul is correct, i completely forgot about that.. i should stop thinking about my girlfriends all the time.

Maninder
www.dbanation.com
Post #741030
Posted Wednesday, June 24, 2009 8:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:05 PM
Points: 2,834, Visits: 8,544
Mani Singh (6/24/2009)
paul is correct, i completely forgot about that.. i should stop thinking about my girlfriends all the time.


How many do you have ? (To have such problems)



Post #741053
Posted Wednesday, June 24, 2009 8:17 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 9,928, Visits: 11,196
homebrew01 (6/24/2009)
Mani Singh (6/24/2009)
paul is correct, i completely forgot about that.. i should stop thinking about my girlfriends all the time.

How many do you have ? (To have such problems)

I really hope we don't go too far down this particular track




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #741064
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse