签到成功

知道了

CNDBA社区CNDBA社区

SQL Server文件管理示例

2018-12-28 10:38 1885 0 转载 SQL Server
作者： Expect-乐
原文链接：https://docs.microsoft.com/zh-cn/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options?view=sql-server-2017

语法

ALTER DATABASE database_name   
{  
    <add_or_modify_files>  
  | <add_or_modify_filegroups>  
}  
[;]  

<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' | '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>::=  
{  
    | ADD FILEGROUP filegroup_name   
        [ CONTAINS FILESTREAM | CONTAINS MEMORY_OPTIMIZED_DATA ]  
    | REMOVE FILEGROUP filegroup_name   
    | MODIFY FILEGROUP filegroup_name  
        { <filegroup_updatability_option>  
        | DEFAULT  
        | NAME = new_filegroup_name   
        | { AUTOGROW_SINGLE_FILE | AUTOGROW_ALL_FILES }  
        }  
}  
<filegroup_updatability_option>::=  
{  
    { READONLY | READWRITE }   
    | { READ_ONLY | READ_WRITE }  
}

A. 向数据库中添加文件

以下示例将一个 5 MB 的数据文件添加到 AdventureWorks2012 数据库。

USE master;  
GO  
ALTER DATABASE AdventureWorks2012   
ADD FILE   
(  
    NAME = Test1dat2,  
    FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL13.MSSQLSERVER/MSSQL/DATA/t1dat2.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
);  
GO

B. 向数据库中添加由两个文件组成的文件组

以下示例在 AdventureWorks2012 数据库中创建文件组 Test1FG1，然后将两个 5 MB 的文件添加到该文件组。

USE master  
GO  
ALTER DATABASE AdventureWorks2012  
ADD FILEGROUP Test1FG1;  
GO  
ALTER DATABASE AdventureWorks2012   
ADD FILE   
(  
    NAME = test1dat3,  
    FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL13.MSSQLSERVER/MSSQL/DATA/t1dat3.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
),  
(  
    NAME = test1dat4,  
    FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL13.MSSQLSERVER/MSSQL/DATA/t1dat4.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
)  
TO FILEGROUP Test1FG1;  
GO

C. 向数据库中添加两个日志文件

下面的示例向 AdventureWorks2012 数据库中添加两个 5 MB 的日志文件。

http://www.cndba.cn/Expect-le/article/3206
http://www.cndba.cn/Expect-le/article/3206

USE master;  
GO  
ALTER DATABASE AdventureWorks2012   
ADD LOG FILE   
(  
    NAME = test1log2,  
    FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL13.MSSQLSERVER/MSSQL/DATA/test2log.ldf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
),  
(  
    NAME = test1log3,  
    FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL10_50.MSSQLSERVER/MSSQL/DATA/test3log.ldf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
);  
GO

D. 从数据库中删除文件

以下示例删除示例 B 中添加的一个文件。

USE master;  
GO  
ALTER DATABASE AdventureWorks2012  
REMOVE FILE test1dat4;  
GO

E. 修改文件

以下示例增加示例 B 中添加的一个文件的大小。

  • 带有 MODIFY FILE 命令的 ALTER DATABASE 只能增大文件大小，因此，如果需要缩小文件大小，则需使用 DBCC SHRINKFILE。
USE master;  
GO

ALTER DATABASE AdventureWorks2012   
MODIFY FILE  
(NAME = test1dat3,  
SIZE = 200MB);  
GO
  • 此示例将数据文件的大小缩小为 100 MB，然后指定该数量的大小。
USE AdventureWorks2012;
GO

DBCC SHRINKFILE (AdventureWorks2012_data, 100);
GO

USE master;  
GO

ALTER DATABASE AdventureWorks2012   
MODIFY FILE  
(NAME = test1dat3,  
SIZE = 200MB);  
GO

F. 将文件移至新位置

以下示例将在示例 A 中创建的 Test1dat2 文件移至新目录中。

http://www.cndba.cn/Expect-le/article/3206

备注http://www.cndba.cn/Expect-le/article/3206http://www.cndba.cn/Expect-le/article/3206

必须先将该文件实际移至新目录中，然后才能运行此示例。 然后，停止和启动 SQL Server 的实例，或使 AdventureWorks2012 数据库 OFFLINE 再 ONLINE，以实施更改。http://www.cndba.cn/Expect-le/article/3206

USE master;  
GO  
ALTER DATABASE AdventureWorks2012  
MODIFY FILE  
(  
    NAME = Test1dat2,  
    FILENAME = N'c:/t1dat2.ndf'  
);  
GO

G. 将 tempdb 移至新位置

以下示例将 tempdb 从其在磁盘上的当前位置移至另一个磁盘位置。 由于每次启动 MSSQLSERVER 服务时都会重新创建 tempdb，因此您不必实际移动数据和日志文件。 在步骤 3 中重新启动服务时，将创建这些文件。 在重新启动该服务之前，tempdb 将继续在现有位置发挥作用。

  • 确定 tempdb 数据库的逻辑文件名称以及这些文件在磁盘上的当前位置。
SELECT name, physical_name  
FROM sys.master_files  
WHERE database_id = DB_ID('tempdb');  
GO
  • 使用 ALTER DATABASE更改每个文件的位置。
USE master;  
GO  
ALTER DATABASE tempdb   
MODIFY FILE (NAME = tempdev, FILENAME = 'E:/SQLData/tempdb.mdf');  
GO  
ALTER DATABASE  tempdb   
MODIFY FILE (NAME = templog, FILENAME = 'E:/SQLData/templog.ldf');  
GO

停止再重新启动 SQL Server的实例。

http://www.cndba.cn/Expect-le/article/3206
http://www.cndba.cn/Expect-le/article/3206

  • 验证文件更改。
SELECT name, physical_name  
FROM sys.master_files  
WHERE database_id = DB_ID('tempdb');

将 tempdb.mdf 和 templog.ldf 文件从其原始位置中删除。

http://www.cndba.cn/Expect-le/article/3206
http://www.cndba.cn/Expect-le/article/3206

H. 使文件组成为默认文件组

以下示例使示例 B 中创建的 Test1FG1 文件组成为默认文件组。 然后，默认文件组被重置为 PRIMARY 文件组。 请注意，必须使用括号或引号分隔 PRIMARY。

USE master;  
GO  
ALTER DATABASE AdventureWorks2012   
MODIFY FILEGROUP Test1FG1 DEFAULT;  
GO  
ALTER DATABASE AdventureWorks2012   
MODIFY FILEGROUP [PRIMARY] DEFAULT;  
GO

I. 使用 ALTER DATABASE 添加文件组

以下示例将一个包含 FILEGROUP 子句的 FILESTREAM 添加到 FileStreamPhotoDB 数据库。

--Create and add a FILEGROUP that CONTAINS the FILESTREAM clause.  
ALTER DATABASE FileStreamPhotoDB  
ADD FILEGROUP TodaysPhotoShoot  
CONTAINS FILESTREAM;  
GO  

--Add a file for storing database photos to FILEGROUP   
ALTER DATABASE FileStreamPhotoDB  
ADD FILE  
(  
    NAME= 'PhotoShoot1',  
    FILENAME = 'C:/Users/Administrator/Pictures/TodaysPhotoShoot.ndf'  
)  
TO FILEGROUP TodaysPhotoShoot;  
GO

以下示例将一个包含 FILEGROUP 子句的 MEMORY_OPTIMIZED_DATA 添加到 xtp_db 数据库。 该文件组存储内存优化数据。

--Create and add a FILEGROUP that CONTAINS the MEMORY_OPTIMIZED_DATA clause.
ALTER DATABASE xtp_db 
ADD FILEGROUP xtp_fg 
CONTAINS MEMORY_OPTIMIZED_DATA;  
GO  

--Add a file for storing memory optimized data to FILEGROUP
ALTER DATABASE xtp_db 
ADD FILE 
(
    NAME='xtp_mod', 
    FILENAME='d:/data/xtp_mod'
) 
TO FILEGROUP xtp_fg;  
GO

J. 更改文件组，以便当文件组中的某个文件达到自动增长阈值时，文件组中的所有文件都会增长

以下示例会生成所需的 ALTER DATABASE 语句，以使用 AUTOGROW_ALL_FILES 设置修改读写文件组。

--Generate ALTER DATABASE ... MODIFY FILEGROUP statements  
--so that all read-write filegroups grow at the same time.  
SET NOCOUNT ON;

DROP TABLE IF EXISTS #tmpdbs
CREATE TABLE #tmpdbs (id int IDENTITY(1,1), [dbid] int, [dbname] sysname, isdone bit);

DROP TABLE IF EXISTS #tmpfgs
CREATE TABLE #tmpfgs (id int IDENTITY(1,1), [dbid] int, [dbname] sysname, fgname sysname, isdone bit);

INSERT INTO #tmpdbs ([dbid], [dbname], [isdone])
SELECT database_id, name, 0 FROM master.sys.databases (NOLOCK) WHERE is_read_only = 0 AND state = 0;

DECLARE @dbid int, @query VARCHAR(1000), @dbname sysname, @fgname sysname

WHILE (SELECT COUNT(id) FROM #tmpdbs WHERE isdone = 0) > 0
BEGIN
    SELECT TOP 1 @dbname = [dbname], @dbid = [dbid] FROM #tmpdbs WHERE isdone = 0

    SET @query = 'SELECT ' + CAST(@dbid AS NVARCHAR) + ', ''' + @dbname + ''', [name], 0 FROM [' + @dbname + '].sys.filegroups WHERE [type] = ''FG'' AND is_read_only = 0;'
    INSERT INTO #tmpfgs
    EXEC (@query)

    UPDATE #tmpdbs
    SET isdone = 1
    WHERE [dbid] = @dbid
END;

IF (SELECT COUNT(ID) FROM #tmpfgs) > 0
BEGIN
    WHILE (SELECT COUNT(id) FROM #tmpfgs WHERE isdone = 0) > 0
    BEGIN
        SELECT TOP 1 @dbname = [dbname], @dbid = [dbid], @fgname = fgname FROM #tmpfgs WHERE isdone = 0

        SET @query = 'ALTER DATABASE [' + @dbname + '] MODIFY FILEGROUP [' + @fgname + '] AUTOGROW_ALL_FILES;'

        PRINT @query

        UPDATE #tmpfgs
        SET isdone = 1
        WHERE [dbid] = @dbid AND fgname = @fgname
    END
END; 
GO

文件管理SQL语句

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
Expect-乐

Expect-乐

关注

Without the continuous bitter cold, there can be no fragrant plum blossom

  • 336
    原创
  • 6
    翻译
  • 100
    转载
  • 41
    评论
  • 访问：1547640次
  • 积分：1957
  • 等级：核心会员
  • 排名：第4名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ