修改Database File

DATABASE database_name { <add_or_modify_files> } [;] <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> } <filespec>::= ( NAME = logical_file_name [ , NEWNAME = new_logical_name ] [ , FILENAME = {‘os_file_name‘ | ‘filestream_path‘ } ] [ , SIZE = size [ KB | MB | GB | TB ] ] [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ] [ , OFFLINE ] )

REMOVE FILE logical_file_name                        

Removes the logical file description from an instance of SQL Server and deletes the physical file. The file cannot be removed unless it is empty.

logical_file_name                                                           

Is the logical name used in SQL Server when referencing the file.

一，移除数据库的File

Step1, Add a data File

use master
go 

alter database db_study
add file
(
name=db_study_file1
,FILENAME =‘D:\db_study_file1.ndf‘
,SIZE =10MB
);

Step2, Empty a data file

A file must be empty before it can be deleted

USE [db_study]
GO
DBCC SHRINKFILE (N‘db_study_file1‘ , EMPTYFILE)
GO

Step3, Remove a file from a database

USE master;
GO
ALTER DATABASE db_study
REMOVE FILE db_study_file1;
GO

Step4，Verify the file change

select mf.file_id,
    mf.type_desc,
    mf.name,
    mf.physical_name,
    mf.state_desc,
    mf.size,
    mf.max_size,
    mf.growth,
    mf.is_percent_growth,
    mf.data_space_id
from sys.master_files mf 
where database_id=db_id(‘db_study‘)

 

二，移动数据库的File的位置

The following example moves db_study from its current location on the disk to another disk location.

Step0，Add a file

use master
go 

--add file
alter database db_study
add file
(
name=db_study_file1
,FILENAME =‘D:\db_study_file1.ndf‘
,SIZE =10MB
);

Step1，Determine the logical file names of the db_study database and their current location on disk.

select mf.file_id,
    mf.type_desc,
    mf.name,
    mf.physical_name,
    mf.state_desc,
    mf.size,
    mf.max_size,
    mf.growth,
    mf.is_percent_growth,
    mf.data_space_id
from sys.master_files mf 
where database_id=db_id(‘db_study‘)

Step2，Change the location of each file

use master
go 
--Update FileName Option
alter database db_study
modify file
(
name=db_study_file1
,FILENAME =‘D:\SqlDataFolder\db_study_file1.ndf‘
);

The file "db_study_file1" has been modified in the system catalog. The new path will be used the next time the database is started.

Step3，Stop the instance of SQL Server, Copy db_study_file1 from orignal location to the target location,  then restart the instance of SQL Server.

Step4，Verify the file change.

select mf.file_id,
    mf.type_desc,
    mf.name,
    mf.physical_name,
    mf.state_desc,
    mf.size,
    mf.max_size,
    mf.growth,
    mf.is_percent_growth,
    mf.data_space_id
from sys.master_files mf 
where database_id=db_id(‘db_study‘)

 

参考doc：

ALTER DATABASE File and Filegroup Options

修改Database File

标签：