SQL Server Performance Forum – Threads Archive

How can i change physical folder

Hello, How can i change physical file location on my database (data, log and indexes) ?
For example, i have :
database 1
– data : ‘d:[…]myData.mdf’
– log : ‘d:[…]myData.ldf’
– index : i don’t know
and i want :
database 1
– data : ‘d:[…]myData.mdf’
– log : ‘e:[…]myData.ldf’
– index : ‘e:[…]myData.idf’ Thanks and sorry for my poor english language

---

For existing database you can usehttp://support.microsoft.com/default.aspx?scid=kb;EN-US;314546
For new databases right-click in EM on the server in question, Properties, Database Settings -> New database default location.
—
Frank Kalis
SQL Server MVP
http://www.insidesql.de

---

1.De-attach the Databse
2. Move to physical file location
3. Attach it again
For more info click ,
http://www.databasejournal.com/features/mssql/article.php/2224361

---

There’s nothing wrong with your English! You will need to detach the database, then move the files, and finally re-attach the database with the files in the new location. The database will be unavailable during this procedure …

---

hi dinesh the idea is good but what if we want the log file in another partition and
mdf file in another is it possible while reattaching.

---

Look at the explanation of sp_attach in BOL. You can specify the physical location of both data and log file(s). —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

---

Thanks for your answers. I have a second question.
Where can i find indexes files and how can i change it location ? Thanks

---

You can recreate the index on the seperate filegroup by using CREATE INDEX… WITH DROP EXISTING and ensur that filegroup is created on the drive where you want to locate. Refer to the books online for more information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

To locate your indexes this one might help
USE PUBS
GO
SELECT
CAST(OBJECT_NAME(sysind.id) AS CHAR(20)) AS TableName
, CAST(sysind.name AS CHAR(30)) AS IdxName
, CAST(sysfg.groupname AS CHAR(10)) AS GroupName
, FILENAME
FROM
sysindexes sysind
INNER JOIN
sysfilegroups sysfg
ON
sysind.groupid = sysfg.groupid
INNER JOIN
sysobjects sysobj
ON
sysind.id = sysobj.id
INNER JOIN
SYSFILEGROUPS sfilegrp
ON
sysind.groupid = sfilegrp.groupid
INNER JOIN
SYSFILES sfiles
ON
sfilegrp.groupid = sfiles.groupid
WHERE
sysobj.xtype <> ‘S’
AND
sysind.name NOT LIKE ‘_WA%’
ORDER BY
sysind.TableName
—
Frank Kalis
SQL Server MVP
http://www.insidesql.de

---

Oops, copied and pasted… My mistake.
This should also work:
USE PUBS
GO
SELECT
CAST(OBJECT_NAME(sysind.id) AS CHAR(20)) AS TableName
, CAST(sysind.name AS CHAR(30)) AS IdxName
, CAST(sysfg.groupname AS CHAR(10)) AS GroupName
, FILENAME
FROM
sysindexes sysind
INNER JOIN
sysfilegroups sysfg
ON
sysind.groupid = sysfg.groupid
INNER JOIN
sysobjects sysobj
ON
sysind.id = sysobj.id
INNER JOIN
SYSFILES sfiles
ON
sysfg.groupid = sfiles.groupid
WHERE
sysobj.xtype <> ‘S’
AND
sysind.name NOT LIKE ‘_WA%’
ORDER BY
sysind.TableName —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

---

Sorry but i don’t understand.
If i want to separate data and indexes, i will create a filegroup and re-create index with this ? Thanks

---

You need to make a distinction here. Case A Clustered Index (CI):
The Clustered Index *is* actually the data. So, whenever you talk about a CI, you actually talk about the data in the table. It’s obviuous that you cannot separate a CI from the data. Case B Nonclustered Indexes (NI):
You can have up to 249 NI per base table. NI’s are separated ondisk structures. Those kind of indexes youo can place in a different filegroup on a different array. As satya already mentioned, you use the CREATE INDEX statement to specify the filegroup on which such an index should be created. —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

---

ok, i understand well now.
The last question, i have create a filegroup in database properties "group of files".
How can i make location about my new filegroup. I have :
Group of files ———————–
Name——Files——Read only—–Default
PRIMARY—– 1—— False—– True
MyOWN —– 0—— False—– True Where can i define filegroup location ? Thanks

---

When you right-click in Enterprise Manager on Properties-> Data Files you can add a new physical file. To specify the location just click on the … button when you are about to create the file. You then can choose the location and the file name. Alternatively you can directly type this into the Location field. —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

---

Thanks you and thanks for all this communauty.
I enjoy to see you.

---

You’re welcome! —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

---

]]>