SQL Distributed Management Objects (SQL-DMO) 提供程式化介面管理 SQL Server,涵蓋備份、還原、作業排程等功能。本文示範 ASP + VBScript 實作備份與驗證網頁應用。
global.asa 註冊 SQL-DMO 類型庫:
<!--METADATA TYPE="TypeLib"
NAME="Microsoft SQLDMO Object Library"
UUID="{10010001-E260-11CF-AE68-00AA004A34D5}"
VERSION="8.0"-->
<%
Dim srv
Set srv = Server.CreateObject("SQLDMO.SQLServer")
srv.LoginTimeout = 15
srv.Connect "servername", "username", "password"
' 整合驗證:srv.LoginSecure = True
%>
<SELECT name="fdatabase">
<%
For Each objDB In srv.Databases
If objDB.SystemObject = False Then
%>
<OPTION><%=objDB.Name%></OPTION>
<%
End If
Next
%>
</SELECT>
<%
Dim objDevice
For Each objDevice In srv.BackupDevices
Response.Write objDevice.Name & "<BR>"
Next
%>
<%
Dim objBackup
Set objBackup = Server.CreateObject("SQLDMO.Backup")
objBackup.BackupSetName = Request("fname")
objBackup.Database = Request("fdatabase")
objBackup.Action = Request("fAction") ' 0=完整, 1=差異, 2=記錄
objBackup.BackupSetDescription = Request("fdescription")
objBackup.Files = Request("fbackupfile") ' 檔案路徑,如 C:\pubs.bak
objBackup.Devices = Request("fdevice") ' 備份裝置名稱
objBackup.TruncateLog = Request("flog") ' 0=NoLog, 1=NoTruncate, 2=Truncate
objBackup.Initialize = Request("finit") ' True=覆寫媒體
objBackup.SQLBackup srv
srv.Disconnect
Set srv = Nothing
Set objBackup = Nothing
%>
ASP 無法使用事件驅動監控備份進度,改用 BackupDevice.ReadBackupHeader 讀取媒體標頭驗證。
<%
Dim objDevice, objResults, iCount, xCount
Set objDevice = Server.CreateObject("SQLDMO.BackupDevice")
For Each objDevice In srv.BackupDevices
If objDevice.Name = Request("fname") Then
Set objResults = objDevice.ReadBackupHeader
For iCount = 1 To objResults.Rows
For xCount = 1 To objResults.Columns
%>
<B><%=objResults.ColumnName(xCount)%></B>: <%=objResults.GetColumnString(iCount, xCount)%><br>
<%
Next
Response.Write "<HR>"
Next
End If
Next
srv.Disconnect
%>
ReadBackupHeader 回傳 QueryResults 物件,包含備份集名稱、備份時間、資料庫名、備份類型等欄位。
SQLDMO.Restore 物件,屬性與 Backup 對稱。完整原始碼可下載 (原文連結已失效),建議參考 SQL-DMO 線上文件擴充錯誤處理與日誌記錄。