Modify Database File

Use ALTER DATABASE to add,modify,remove the data file and log file of the database.

ALTER DATABASEdatabase_name {<Add_or_modify_files>}[;]<Add_or_modify_files>::={    ADD FILE <Filespec> [,... N]         [To FILEGROUP {filegroup_name}]  | ADD LOG FILE <Filespec> [,... N]   |REMOVEFILElogical_file_name|MODIFYFILE <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 was removed unless it is empty.

Logical_file_name

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

One, remove the file from the database

STEP1, Add a data File

 Use Master Go Alter Database Db_study Add file (name=='D:\db_study_file1.ndf' )  =10MB);

STEP2, Empty a data file

A file must is 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  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_idfromwhere database_id=db_id(' db_study')

Second, the location of the file of the mobile database

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=='D:\db_study_file1.ndf' )  =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_idfromwhere database_id=db_id('db_study')

Step2,change the location of each file

 Use Master Go -- Update FileName Option Alter Database  file(name=='D:\SqlDataFolder\db_ STUDY_FILE1.NDF');

The file "Db_study_file1" has a been modified in the system catalog. The new path would be used the next time, the database is started.

Step3,stop the instance of SQL Server, Copy db_study_file1 from orignal to the target location, then re Start 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_idfromwhere database_id=db_id('db_study')

Reference doc:

ALTER DATABASE File and Filegroup Options

Modify Database File