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

how to move table from .mdf to .ndf Expand / Collapse
Author
Message
Posted Wednesday, February 9, 2011 3:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 16, 2014 10:44 AM
Points: 153, Visits: 583
I have table of 150GB. I need to move it from E:\SQL\database.MDF location to k:\SQL\database.NDF.

can help me. Thanks in advance.

Post #1061602
Posted Wednesday, February 9, 2011 3:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
You have to assign the new file to a different logical filegroup in the database, otherwise you can't control it.

Once you have done that, you rebuild the clustered index in the new filegroup. You'll be making the table unavailable during this process.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1061605
Posted Wednesday, February 9, 2011 3:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 16, 2014 10:44 AM
Points: 153, Visits: 583
Can explain it a bit clear.. I am not get how to do it.

step by step process please.
Post #1061612
Posted Wednesday, February 9, 2011 4:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 16, 2014 10:44 AM
Points: 153, Visits: 583
If there is any script belonging to it. please post it.

Thanks in advance.
Post #1061617
Posted Wednesday, February 9, 2011 4:05 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 18,060, Visits: 16,094
Which part do you need a script for?

Do you have multiple files and filegroups already created for your database?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1061621
Posted Wednesday, February 9, 2011 7:40 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 8, 2014 10:51 AM
Points: 292, Visits: 439
Hello,

First create the new filegroup

USE master
GO
ALTER DATABASE Test1
ADD FILEGROUP Test1FG1
GO
ALTER DATABASE Test1
ADD FILE
( NAME = test1dat3,
FILENAME = 'k:\SQL\database.NDF',
SIZE = 200GB,
FILEGROWTH = 10GB)
TO FILEGROUP Test1FG1

Then rebuild the clustered index into the new filegroup created
CREATE CLUSTERED INDEX CIX_YourTable
ON dbo.YourTable(YourClusteringKeyFields)
WITH DROP_EXISTING
ON [Test1FG1]

And that's it. I hope this can clear things up.
Post #1061666
Posted Thursday, February 10, 2011 11:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 16, 2014 10:44 AM
Points: 153, Visits: 583
Thanks
Post #1062184
Posted Thursday, July 25, 2013 8:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 13, 2014 10:06 PM
Points: 16, Visits: 73
Hi All,

how we can find one table is located in mdf file or ndf file ...

Thanks in advance.
Post #1477531
Posted Thursday, July 25, 2013 8:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 1:47 AM
Points: 53, Visits: 292
Hi - Start a new thread to post your questions!
Post #1477551
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse