SQL Server数据库的创建和文件的修改

create database newdb
on primary
(name='newdb',  filename='D:\MSSQLServer\newdb.mdf',  size=1GB,maxsize=10GB,filegrowth=1GB),
(name='newdb_1',filename='D:\MSSQLServer\newdb_1.ndf',size=1GB,maxsize=10GB,filegrowth=1GB),
(name='newdb_2',filename='D:\MSSQLServer\newdb_2.ndf',size=1GB,maxsize=10GB,filegrowth=1GB),
(name='newdb_3',filename='D:\MSSQLServer\newdb_3.ndf',size=1GB,maxsize=10GB,filegrowth=1GB),
(name='newdb_4',filename='D:\MSSQLServer\newdb_4.ndf',size=1GB,maxsize=10GB,filegrowth=1GB)
log on
(name='newdb_log',filename='D:\MSSQLServer\newdb_log.ldf',size=1GB,maxsize=10GB,filegrowth=1GB)
go

use newdb
go

select df.file_id,
    df.type,
    df.type_desc,
    df.name,
    df.physical_name,
    df.state,
    df.state_desc,
    df.size*8/1024/1024 as size_gb,
    df.max_size*8/1024/1024 as max_size_gb,
    df.growth,
    df.is_percent_growth,
    fg.name as filegroup_name,
    fg.is_default
from sys.database_files df 
inner join sys.filegroups fg
    on df.data_space_id=fg.data_space_id

View Code

select 
    DB_NAME(mf.database_id) as dbname,
    mf.file_id,
    mf.type as file_type,
    mf.type_desc,
    mf.data_space_id as file_group,
    mf.name as logical_file_name,
    mf.physical_name,
    mf.state_desc,
    mf.size*8/1024/1024 as size_gb,
    mf.max_size*8/1024/1024 as max_size_gb,
    mf.growth,
    mf.is_percent_growth
from sys.master_files mf 
--where database_id=db_id('newdb')

View Code

use master 
go 
alter database newdb
add filegroup fg_newdb1;

alter database newdb
add file
(name=fg_newdb1_1,filename ='d:\MSSQLServer\fg_newdb1_1.ndf',size=1GB,maxsize=10GB,filegrowth=1GB)
to filegroup fg_newdb1;

alter database newdb
add log file
(name=newdb_log_1,filename ='d:\MSSQLServer\newdb_log_1.ldf',size=1GB,maxsize=10GB,filegrowth=1GB);

MODIFY FILE (NAME =‘logical_file_name’, NEWNAME =‘new_logical_name’)

alter database newdb
modify file
(NAME ='fg_newdb1_1',newname='fg_newdb1_file1')

MODIFY FILE (NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )

alter database newdb
modify file
(name='fg_newdb1_file1',filename='F:\MSSQLServer\fg_newdb1_file1.ndf');

use master
go 
alter database newdb
set offline
with rollback immediate;

move D:\MSSQLServer\fg_newdb1_1.ndf F:\MSSQLServer\fg_newdb1_file1.ndf

use master
go 
alter database newdb
set online

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

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

USE [db_study]
GO
DBCC SHRINKFILE (N'db_study_log1' , EMPTYFILE)
GO

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

USE master;
GO
ALTER DATABASE db_study
REMOVE FILEGROUP fg_study1;
GO