Linux 控制 Windows SQL Server 自動化還原資料庫實戰 (SSH + isql + 排程)

前言

異地備援、測試環境每日重置、資料同步等場景常需「Linux 主控端」定時觸發「Windows SQL Server」還原資料庫。本文完整記錄三大關鍵技術鏈路：Linux SSH 免密登入 Windows (Copssh)、SQL Server 命令列工具 isql/sqlcmd 路徑與編碼處理、強制斷開連線並還原的預存程序設計，最後整合 Shell 腳本與 Crontab 實現全自動化。

環境拓撲

• 控制端：Linux (CentOS/Ubuntu) 192.168.0.68，用戶 restoredb
• 目標端：Windows Server 2003 + SQL Server 2000/2005/2008，IP 192.168.0.22，管理員 administrator

一、Windows 安裝 SSH 服務端

1. 下載 Copssh (含 Cygwin/OpenSSH 打包)：SourceForge，建議 Copssh_3.0.2 + icwbase-2.0.3-patch-100.zip (修正退格/方向鍵顯示異常)。
2. 安裝：一路 Next，完成後「開始 → Copssh → 01. Activate a user」啟用 administrator SSH 登入。
3. 驗證：Linux 端 ssh administrator@192.168.0.22 輸入密碼登入成功。

二、環境變數與 isql/sqlcmd 路徑

SSH 登入後預設 PATH 不含 SQL Server 工具目錄。編輯 Copssh 環境 /etc/profile：

export PATH=$PATH:/cygdrive/c/Program\ Files/Microsoft\ SQL\ Server/80/Tools/BINN
# SQL Server 2005/2008 通常在
# /cygdrive/c/Program\ Files/Microsoft\ SQL\ Server/90/Tools/BINN
# /cygdrive/c/Program\ Files/Microsoft\ SQL\ Server/100/Tools/BINN

重新登入 SSH，執行 isql -Usa -Ppassword 或 sqlcmd -S . -U sa -P password 驗證可用。

三、SSH 免密登入 (金鑰信任)

# Linux 控制端
su - restoredb
ssh-keygen -t rsa -b 2048  # 一路 Enter
ssh-copy-id administrator@192.168.0.22
# 或手動將 ~/.ssh/id_rsa.pub 內容追加至 Windows 端
# %ProgramFiles%\ICW\home\administrator\.ssh\authorized_keys

驗證：ssh administrator@192.168.0.22 "echo OK" 無需密碼即返回 OK。

四、SQL 腳本編碼轉換 (Linux UTF-8 → Windows ANSI)

Linux 編輯的 .sql 為 UTF-8，Windows isql/sqlcmd 預設讀取 ANSI (CP936/CP1252)，直接執行會報語法錯誤。轉換命令：

cat restore.sql | iconv -f UTF-8 -t CP936 -o restore_win.sql
# 或 Windows-1252 (視 SQL Server 版本/地區設定)
# cat restore.sql | iconv -f UTF-8 -t WINDOWS-1252 -o restore_win.sql

將 restore_win.sql 上傳至 Windows (SCP/SFTP/共用資料夾) 再執行。

五、主機存活檢測 (SSH 快速探活)

避免 ping 不測試埠、telnet 超時過久。使用 SSH 連線測試：

ssh -p 22 -o StrictHostKeyChecking=no \
    -o ConnectTimeout=2 \
    -o ServerAliveCountMax=5 \
    -o ServerAliveInterval=1 \
    administrator@192.168.0.22 "exit;" 2>&1 \
| grep -c "192.168.0.22.*timeout"
# 返回 0 = 存活；1 = 不可達

六、資料庫強制還原核心邏輯

痛點：資料庫若有使用中連線，RESTORE DATABASE ... WITH REPLACE 會失敗 (獨佔鎖衝突)。

解法：建立預存程序 dbo.KillSpid 於 master，還原前先殺掉目標資料庫所有 SPID。

1. 建立 KillSpid 預存程序

USE master;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'KillSpid' AND type = 'P')
    DROP PROCEDURE dbo.KillSpid;
GO
CREATE PROCEDURE dbo.KillSpid @DbName SYSNAME
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Sql NVARCHAR(MAX) = N'';
    SELECT @Sql = @Sql + N'KILL ' + CAST(spid AS VARCHAR(10)) + N'; '
    FROM sys.sysprocesses
    WHERE dbid = DB_ID(@DbName) AND spid <> @@SPID;
    IF @Sql <> N''
        EXEC sp_executesql @Sql;
END;
GO

2. 還原腳本範例 (restore_win.sql)

USE master;
GO
EXEC dbo.KillSpid 'EsunnyJS';
GO
RESTORE DATABASE EsunnyJS
FROM DISK = 'D:\Progra~1\ICW\home\Administrator\esunny\EsunnyJS.bak'
WITH REPLACE, STATS = 10;
GO

七、Linux 整合 Shell 腳本

#!/bin/bash
# /opt/scripts/sql_restore.sh
set -euo pipefail

TARGET_IP="192.168.0.22"
TARGET_USER="administrator"
DB_NAME="EsunnyJS"
BAK_PATH="/cygdrive/d/Progra~1/ICW/home/Administrator/esunny/EsunnyJS.bak"
SQL_SCRIPT_LOCAL="/opt/scripts/restore.sql"
SQL_SCRIPT_REMOTE="restore_win.sql"

# 1. 主機存活檢測
if ssh -o ConnectTimeout=3 -o BatchMode=yes ${TARGET_USER}@${TARGET_IP} "exit" 2>&1 | grep -q "timeout"; then
    echo "$(date) ERROR: Target host ${TARGET_IP} unreachable" | tee -a /var/log/sql_restore.log
    exit 1
fi

# 2. 產生 Windows 版 SQL 腳本 (含編碼轉換)
cat > "${SQL_SCRIPT_LOCAL}" <

# 每日凌晨 02:30 執行
30 2 * * * /opt/scripts/sql_restore.sh >> /var/log/sql_restore_cron.log 2>&1