SQL Server 2000 建立多檔案群資料庫與簡單復原模式實驗腳本

完整建庫腳本

USE master;
SET NOCOUNT ON;
GO

IF DB_ID('credit') IS NOT NULL
    DROP DATABASE credit;
GO

CREATE DATABASE [credit]
ON PRIMARY (
    NAME = N'credit_Data',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\credit_Data.MDF',
    SIZE = 50,
    FILEGROWTH = 10%
)
LOG ON (
    NAME = N'credit_Log',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\credit_Log.LDF',
    SIZE = 1,
    FILEGROWTH = 10%
);
GO

-- 新增檔案群
ALTER DATABASE credit ADD FILEGROUP CreditTablesFG;
ALTER DATABASE credit ADD FILEGROUP CreditIndexesFG;
GO

-- 資料檔案群
ALTER DATABASE credit ADD FILE (
    NAME = CreditTables,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\CreditTables.ndf',
    SIZE = 8MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 50MB
) TO FILEGROUP CreditTablesFG;
GO

-- 索引檔案群
ALTER DATABASE credit ADD FILE (
    NAME = CreditIndexes,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\CreditIndexes.ndf',
    SIZE = 8MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 50MB
) TO FILEGROUP CreditIndexesFG;
GO

-- 驗證
IF DB_ID('credit') IS NOT NULL
    PRINT 'CREATED DATABASE "credit"';
ELSE
    PRINT 'CREATE DATABASE "credit" FAILED';
GO

設定簡單復原模式

USE ClassNorthwind;
ALTER DATABASE ClassNorthwind SET RECOVERY SIMPLE;
EXEC sp_helpdb ClassNorthwind;
GO

重點解析

• 多檔案群：將使用者資料表與索引分離至不同檔案群，可分散 I/O、獨立備份/還原。
• 檔案成長策略：資料檔 10%、記錄檔 10%、NDF 檔固定 50MB，避免頻繁微小成長造成碎片。
• 簡單復原：適合唯讀/可重建資料庫，Checkpoint 自動截斷記錄檔。