您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

IIS 日誌匯入 SQL Server 資料庫完整指南

編輯：關於SqlServer

前言

在管理 IIS 伺服器時，將海量的存取日誌匯入 SQL Server 進行分析是常見需求。本文詳細記錄從建立資料表、使用 BCP 工具大量匯入，到撰寫 C# 程式預處理日誌檔案的完整流程，助你高效完成資料導入。

一、建立對應的資料表結構

首先依據 IIS 日誌預設欄位（W3C 格式）建立資料表。因日誌量大，建議依月份分表儲存（如 web_201007）。所有欄位採用 varchar/nvarchar 並預留足夠長度，避免匯入時發生字串截斷錯誤。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[web_201007](
    [date] [varchar](255) NULL,
    [time] [varchar](255) NULL,
    [s-ip] [varchar](255) NULL,
    [cs-method] [varchar](255) NULL,
    [cs-uri-stem] [nvarchar](255) NULL,
    [cs-uri-query] [nvarchar](1000) NULL,
    [s-port] [varchar](255) NULL,
    [cs-username] [varchar](255) NULL,
    [c-ip] [varchar](255) NULL,
    [cs(User-Agent)] [nvarchar](1000) NULL,
    [cs(Referer)] [nvarchar](1000) NULL,
    [sc-status] [varchar](255) NULL,
    [sc-substatus] [varchar](255) NULL,
    [sc-win32-status] [varchar](255) NULL,
    [time-taken] [varchar](255) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

注意： 實際欄位須依 IIS 設定的「記錄欄位」調整；若啟用完整記錄，欄位數可能超過 15 個。

二、使用 BCP 快速匯入資料

BCP (Bulk Copy Program) 是 SQL Server 內建的高效大量匯入工具。關鍵參數說明：

-t"*"：欄位分隔符（IIS 日誌預設為空白，建議先以程式替換為 * 等特殊字元）
-r"\r"：列分隔符（IIS 日誌為 CR 換行）
-w：使用 Unicode 編碼（含中文時必須；純英文可用 -c 節省空間）
-U/-P/-S：登入帳號、密碼、伺服器實例名

bcp [database].dbo.[web_201007] in "g:\IISlog\log.txt" -w -t"*" -r"\r" -U"sa" -P"123456" -S"UKEASSERVER\SQLEXPRESS"

避坑指南：

日誌檔若含中文，儲存編碼必須為 Unicode (UTF-16 LE)，不可為 UTF-8，否則 BCP 會產生亂碼。
若報錯「字串資料右邊會被截斷」，請檢查對應欄位長度是否足夠（如 User-Agent、Referer 建議 1000 以上）。
IIS 日誌為 *.log 且每日一檔，BCP 不支援直接讀取多檔，需先合併預處理。

三、C# 程式預處理：合併、清洗、轉編碼

編寫專用工具 FormatLog 類別，功能包含：

依檔名規則（u_exYYMMDD.log）篩選特定月份檔案
跳過註解行（以 # 開頭）
將空白分隔符替換為 *
驗證欄位數是否符合預期（15 欄），不符者寫入錯誤檔供排查
以 Encoding.Unicode 輸出合併後的 log.txt

核心程式碼片段：

public void Execute() {
    Regex reg = new Regex(@"u_ex(\d{6})\.log");
    string[] files = Directory.GetFiles(inputDirectory);
    var targetFiles = files.Where(f => reg.Match(f).Groups[1].Value.StartsWith("1011")).ToArray();

    using (FileStream fs = new FileStream(outputFileName, FileMode.Create))
    using (StreamWriter sw = new StreamWriter(fs, Encoding.Unicode)) {
        foreach (string f in targetFiles) {
            foreach (string line in GetRecordLineForFile(f)) {
                sw.Write(line);
                sw.Write(rowSeparator); // "\r"
            }
        }
    }
}

private List<string> GetRecordLineForFile(string fileName) {
    var list = new List<string>();
    using (FileStream fs = new FileStream(fileName, FileMode.Open))
    using (StreamReader sr = new StreamReader(fs, Encoding.UTF8)) {
        string line;
        while ((line = sr.ReadLine()) != null) {
            if (!line.StartsWith("#")) {
                line = line.Replace(" ", fieldSeparator); // " "
                if (line.Split(fieldSeparator.ToCharArray()).Length == fieldLength) // 15
                    list.Add(line);
            }
        }
    }
    return list;
}

呼叫範例：

string inputDir = @"g:\IISlog\logs";
string outputFile = @"g:\IISlog\log.txt";
string errorFile = @"g:\IISlog\error.txt";

FormatLog log = new FormatLog(inputDir, outputFile, errorFile);
log.Execute();
// log.WriteInvalidFields(); // 選擇性：輸出欄位長度超標記錄