Mdf to Ndf - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Mdf to Ndf

Author

Topic

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-05 : 02:41:59

Hi Friends

I want to move 3 tables from .Mdf to .Ndf
I have created secondary .Ndf in database
now how to move these tables.

In Love... With Me!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-05 : 03:49:23

see here:
http://www.sqlservercentral.com/Forums/Topic1061602-146-1.aspx#bm1061605

No, you're never too old to Yak'n'Roll if you're too young to die.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-05 : 10:40:13

@webfred, if table is heap then ???

--------------------------
http://connectsql.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-05 : 10:53:24

... build a clustered index - why not?

No, you're never too old to Yak'n'Roll if you're too young to die.

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-05-05 : 10:59:19

you have to create filegroup then you assign that .ndf
file to filegroup witch you created and change the filegroup of that 3 tables

Manju

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-05 : 11:01:47

manju, why you are posting confusing answer, While a perfect answer is already been posted by webfred

--------------------------
http://connectsql.blogspot.com/

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-05-06 : 07:14:57

If this primary file of a database then it will not move all contents and you can't delete this primary file ortherwise you will use this

USE [your database]
GO
DBCC SHRINKFILE (N'database file name' , EMPTYFILE)
GO

I assumed that you have added a new .ndf file in the same file group
this query will transfer all data from 1 file to another file which one in the same file group

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-06 : 07:20:54

The question wasn't how to move database. IMHO the question was how to move particular tables.

No, you're never too old to Yak'n'Roll if you're too young to die.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-11 : 01:58:02

Thanks webfred and all
See solution how I tried
-----------------
USE MASTER
GO
IF EXISTS(SELECT 1 FROM SYS.SYSDATABASES WHERE NAME='TEST')--TEST FOR DATABASE EXISTS
BEGIN
DROP DATABASE TEST--IF EXISTS DROPS
END
CREATE DATABASE Test--CREATES TEST DATABASE
Go
USE Test--CONNECTION TO TEST
GO
Create Table TestMDF(ID INT,FNAME NVARCHAR(23),LNAME NVARCHAR(23))--CREATES TABLE ON TEST.MDF
CREATE CLUSTERED INDEX TestMDF_Fname ON TestMDF(FNAME)--CREATE CLUSTERED INDEX ON TABLE TestMDF
INSERT INTO TestMDF
SELECT 1,'KHTAN','KHTAN' UNION ALL
SELECT 2,'TKIZER','TKIZER' UNION ALL
SELECT 3,'WEBFRED','WEBFRED'
GO
CREATE TABLE TestNDF(ID INT,FNAME NVARCHAR(23),LNAME NVARCHAR(23))--CREATES TABLE ON TEST.MDF
CREATE CLUSTERED INDEX TestNDF_Lname ON TestNDF(LNAME)--CREATE CLUSTERED INDEX ON TABLE TestNDF
INSERT INTO TestMDF
SELECT 1,'KHTAN','KHTAN' UNION ALL
SELECT 2,'TKIZER','TKIZER' UNION ALL
SELECT 3,'WEBFRED','WEBFRED'
GO
--CHECK THE FileGroupName COLUMN WHERE TABLE ARE LOCATED
SELECT OBJECT_NAME( i."id" ) AS TableName ,
i."Name" AS IndexName ,
FILEGROUP_NAME( i.groupid ) AS FileGroupName
FROM sysindexes AS i
WHERE ( i.indid IN ( 0 , 1 ) Or i.indid < 255 ) And -- Tables & indexes only
OBJECTPROPERTY( i."id" , 'IsUserTable' ) = 1 And -- User tables only
OBJECTPROPERTY( i."id" , 'IsMSShipped' ) = 0 And -- No system tables
COALESCE( INDEXPROPERTY( i."id" , i."Name" , 'IsStatistics' ) , 0 ) = 0 And -- No Statistics / Auto-Create stats
COALESCE( INDEXPROPERTY( i."id" , i."Name" , 'IsHypothetical' ) , 0 ) = 0 -- No Hypothetical statistics
ORDER BY FileGroupName , TableName , IndexName
GO
-- NOW I HAVE TWO TABLE IN .MDF FILE I AM MOVING ONE FILE TestNDF TO TEST.NDF
--ALTER DATABASE TEST REMOVE FILEGROUP DBTestNDF /*ALTER DATABASE TEST AND DROP TEST1FG1 */
ALTER DATABASE Test ADD FILEGROUP DBTestNDF--ALTERS DATABASE TEST AND ADD FILEGROUP
GO
ALTER DATABASE Test--ALTER DATABASE TEST
ADD FILE --ADD FILE
( NAME = DBTest,
FILENAME = 'C:\Test.NDF',--GIVE PATH WHERE YOU WNAT TO MOVE YOUR .NDF FILE
SIZE = 20MB,
FILEGROWTH = 1MB)
TO FILEGROUP DBTestNDF
GO
CREATE CLUSTERED INDEX TestNDF_Lname--CREATE CLUSTERED INDEX WITH DROP ON FILEGROUP NAME IT WILL MOVE YOUR TABLE TO DBTestNDF FIELGROUP
ON dbo.TestNDF(LNAME)
WITH DROP_EXISTING
ON [DBTestNDF]
G0
--CHECK THE FileGroupName COLUMN WHERE TABLE ARE LOCATED
SELECT OBJECT_NAME( i."id" ) AS TableName ,
i."Name" AS IndexName ,
FILEGROUP_NAME( i.groupid ) AS FileGroupName
FROM sysindexes AS i
WHERE ( i.indid IN ( 0 , 1 ) Or i.indid < 255 ) And -- Tables & indexes only
OBJECTPROPERTY( i."id" , 'IsUserTable' ) = 1 And -- User tables only
OBJECTPROPERTY( i."id" , 'IsMSShipped' ) = 0 And -- No system tables
COALESCE( INDEXPROPERTY( i."id" , i."Name" , 'IsStatistics' ) , 0 ) = 0 And -- No Statistics / Auto-Create stats
COALESCE( INDEXPROPERTY( i."id" , i."Name" , 'IsHypothetical' ) , 0 ) = 0 -- No Hypothetical statistics
ORDER BY FileGroupName , TableName , IndexName
GO
--NOW SEE ERROR WHEN YOU TRY TO REMOVE FILEGROUP
ALTER DATABASE TEST REMOVE FILEGROUP DBTestNDF

In Love... With Me!

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-11 : 02:15:27

Thanks to Manju also
Your solution is using GUI
Assuming That file group is already created
Then
I need to expand my Test Database in SQL Management Studio
Right Click on table (Note:The table Which I want to move to .NDF)
Click on Design
next press F4 button(from keyboard)
It opens property windows
Expand data specification
In that change Filegroup or Partition Schema name to your Filegroup

In Love... With Me!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources