https://msdn.microsoft.com/en-us/library/bb522469.aspx
*建議使用IE開啟
簡易說明如下圖：
- { }：大刮號裡面的內容，是必寫，但大刮號本身不用寫
- [ ]：中刮號裡面的內容，是可寫可不寫，但中刮號本身不用寫
- < >：大於、小於裡面的內容，是下面還會有其他補充資料，所以要繼續複製要下面的內容上來，但大於、小於本身不用寫
- ｜：直線表示多選一，不能都選，但直線本身不用寫
- ( )：小刮號表示可多選多，小刮號本身用寫
- 小寫字：是我們要改的內容
- 大寫字：SQL SERVER本身的保留字
因此，上述內容就變成
如果是要修改Sales database中的Sales_sec.ndf變更路徑到 M:\data\ 下面
則變成
- 先把要用的貼一貼，再刪
ALTER DATABASEdatabase_nameSales
{
<add_or_modify_files>::= --這邊是從下面複製上來的
{
ADD FILE <filespec> [ ,...n ]
[ TO FILEGROUP { filegroup_name } ]
| ADD LOG FILE <filespec> [ ,...n ]
| REMOVE FILE logical_file_name
| MODIFY FILE <filespec>::= --這邊也是從下面複製上來的
(
NAME = logical_file_name
[ , NEWNAME = new_logical_name ]
[ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ]
[ , SIZE = size [ KB | MB | GB | TB ] ]
[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED }
[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
[ , OFFLINE ]
)
}
| <add_or_modify_filegroups>}[;]
- database_name=Sales
2.慢慢修正內容，先改 add_or_modify_files
ALTER DATABASE Sales
{
ADD FILE <filespec> [ ,...n ]
[ TO FILEGROUP { filegroup_name } ]
| ADD LOG FILE <filespec> [ ,...n ]
| REMOVE FILE logical_file_name
|MODIFY FILE
<filespec>::= ( NAME = logical_file_name[ , NEWNAME = new_logical_name ][ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ][ , SIZE = size [ KB | MB | GB | TB ] ][ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ][ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ][ , OFFLINE ])
}
就變成
ALTER DATABASE SalesMODIFY FILE
<filespec>::= ( NAME = logical_file_name
[ , NEWNAME = new_logical_name ]
[ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ]
[ , SIZE = size [ KB | MB | GB | TB ] ]
[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
[ , OFFLINE ]
)
3.再來改filespec
ALTER DATABASE Sales
MODIFY FILE
<filespec>::=
(
NAME =logical_file_nameSales_sec
[ , NEWNAME = new_logical_name ]
[ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ]
[ , SIZE = size [ KB | MB | GB | TB ] ]
[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
[ , OFFLINE ]
)
變成
ALTER DATABASE Sales
MODIFY FILE (
NAME =Sales_sec
[ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ]
)
4.因為我們是要變更位置，所以只用到'os_file_name'
ALTER DATABASE Sales
MODIFY FILE (
NAME =Sales_sec
[, FILENAME ={'os_file_name'| 'filestream_path' | 'memory_optimized_data_path' } ]
)
所以變成
- os_file_name＝M:\data\ Sales_sec.ndf
ALTER DATABASE Sales
MODIFY FILE (
NAME =Sales_sec
, FILENAME = 'M:\data\ Sales_sec.ndf'
)
