萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP 程式設計

ASP 備份/還原 SQL Server 資料庫:SQL-DMO 物件模型實作範例

前置需求

  • 伺服器安裝 **SQL Server Client Tools** (含 SQL-DMO: `sqldmo.dll`)。
  • IIS 應用程式集區身分需具備 SQL Server `sysadmin` 或 `db_backupoperator` / `dbcreator` 權限。
  • **注意**:SQL-DMO 在 SQL Server 2012 後**已移除**,新版請改用 **SMO (SQL Server Management Objects)** 或 `BACKUP DATABASE` T-SQL 指令。

完整 ASP 程式碼 (backup_restore.asp)

<%@ Language=VBScript CodePage=65001 %>
<% Option Explicit %>
<html><head><meta charset="utf-8"><title>資料庫備份/還原</title></head><body>

<form method="post">
<%
Dim action
action = LCase(Request("action"))

If action = "restore" Then
    Response.Write "準備還原資料庫..."
ElseIf action = "backup" Then
    Response.Write "準備備份資料庫..."
Else
%>
  操作類型:
  
  
  
<% End If %> 資料庫名:
備份檔路徑: (伺服器端絕對路徑或相對網站根目錄)
</form> <% ' === 參數設定 === Dim sqlServer, sqlUser, sqlPwd, loginTimeout, dbName, bakFile, act sqlServer = "localhost" ' SQL Server 執行個體 sqlUser = "sa" ' 登入帳號 sqlPwd = "your_strong_password" ' 登入密碼 loginTimeout = 30 ' 連線逾時(秒) dbName = Trim(Request("databasename")) bakFile = Trim(Request("bak_file")) ' 轉換為伺服器端實體路徑 (建議放在非網站目錄,如 D:\SQLBackup\) bakFile = Server.MapPath("backup/") & bakFile act = LCase(Request("action")) If dbName = "" Then Response.Write "請輸入資料庫名稱" Else If act = "backup" Then Call BackupDatabase() ElseIf act = "restore" Then Call RestoreDatabase() Else Response.Write "請選擇操作類型" End If End If Sub BackupDatabase() On Error Resume Next Dim srv, bak Set srv = Server.CreateObject("SQLDMO.SQLServer") srv.LoginTimeout = loginTimeout srv.Connect sqlServer, sqlUser, sqlPwd Set bak = Server.CreateObject("SQLDMO.Backup") bak.Database = dbName bak.Devices = "File" ' 備份到檔案裝置 bak.Files = bakFile ' 檔案路徑 bak.Action = 0 ' SQLDMOBackup_Database (完整備份) bak.Initialize = True ' 覆寫現有備份集 ' bak.ReplaceDatabase = True ' 還原時用 bak.SQLBackup srv If Err.Number <> 0 Then Response.Write "備份失敗: " & Err.Number & "
" & Err.Description & "
" Err.Clear Else Response.Write "備份成功! 檔案: " & bakFile & "" End If srv.DisConnect Set bak = Nothing Set srv = Nothing On Error GoTo 0 End Sub Sub RestoreDatabase() On Error Resume Next Dim srv, rest Set srv = Server.CreateObject("SQLDMO.SQLServer") srv.LoginTimeout = loginTimeout srv.Connect sqlServer, sqlUser, sqlPwd Set rest = Server.CreateObject("SQLDMO.Restore") rest.Action = 0 ' SQLDMORestore_Database (完整還原) rest.Database = dbName rest.Devices = "File" rest.Files = bakFile rest.ReplaceDatabase = True ' 強制覆蓋現有資料庫 (含連線中斷) rest.SQLRestore srv If Err.Number <> 0 Then Response.Write "還原失敗: " & Err.Number & "
" & Err.Description & "
" Err.Clear Else Response.Write "還原成功!" End If srv.DisConnect Set rest = Nothing Set srv = Nothing On Error GoTo 0 End Sub %> </body></html>

關鍵參數說明

物件/屬性值/常數說明
Backup.Action0 (SQLDMOBackup_Database)完整資料庫備份
Backup.InitializeTrue/FalseTrue=覆寫備份檔,False=附加
Restore.Action0 (SQLDMORestore_Database)完整資料庫還原
Restore.ReplaceDatabaseTrue強制還原,斷開現有連線
Devices = "File"備份裝置類型:檔案 (可選 Tape、Pipe)

現代替代方案:T-SQL BACKUP/RESTORE (推薦)

不依賴 SQL-DMO,直接執行 T-SQL,相容所有 SQL Server 版本:

備份

BACKUP DATABASE [MyDatabase] 
TO DISK = 'D:\SQLBackup\MyDatabase_20240115.bak'
WITH INIT, COMPRESSION, STATS = 10;

還原

-- 先設為單用戶模式斷開連線
ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE [MyDatabase] 
FROM DISK = 'D:\SQLBackup\MyDatabase_20240115.bak'
WITH REPLACE, RECOVERY, STATS = 10;

-- 還原後設回多用戶
ALTER DATABASE [MyDatabase] SET MULTI_USER;

ASP 執行 T-SQL 範例

Sub ExecuteSql(sql)
    Dim conn
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open "Provider=SQLOLEDB;Server=localhost;Database=master;Uid=sa;Pwd=pass;"
    conn.CommandTimeout = 300 ' 5 分鐘
    On Error Resume Next
    conn.Execute sql
    If Err.Number <> 0 Then
        Response.Write "錯誤: " & Err.Description & ""
    Else
        Response.Write "執行成功"
    End If
    On Error GoTo 0
    conn.Close
    Set conn = Nothing
End Sub

' 呼叫
ExecuteSql "BACKUP DATABASE [MyDB] TO DISK='D:\Backup\MyDB.bak' WITH INIT, COMPRESSION"

排程自動化 (生產環境標準作法)

  1. SQL Server Agent 作業:建立維護計畫,排程每日完整備份、每小時記錄備份。
  2. 維護計畫精靈**:GUI 設定備份、清理舊備份、完整性檢查、索引重組。
  3. PowerShell + SQLServer 模組**:`Backup-SqlDatabase` / `Restore-SqlDatabase` Cmdlet,彈性腳本化。
  4. 第三方工具**:Ola Hallengren Maintenance Solution (免費、業界標準)。

總結

SQL-DMO 提供物件導向介面操作備份/還原,但已**淘汰**。現代專案應直接使用 **T-SQL BACKUP/RESTORE** 指令搭配 **SQL Server Agent 排程**,或 **SMO (Microsoft.SqlServer.Management.Smo)** 程式庫進行程式化控制。ASP 頁面僅適合臨時手動操作介面,不可作為生產備份策略主力。

copyright © 萬盛學電腦網 all rights reserved