<%@ 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.Action | 0 (SQLDMOBackup_Database) | 完整資料庫備份 |
| Backup.Initialize | True/False | True=覆寫備份檔,False=附加 |
| Restore.Action | 0 (SQLDMORestore_Database) | 完整資料庫還原 |
| Restore.ReplaceDatabase | True | 強制還原,斷開現有連線 |
| Devices = "File" | 備份裝置類型:檔案 (可選 Tape、Pipe) |
不依賴 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;
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"
SQL-DMO 提供物件導向介面操作備份/還原,但已**淘汰**。現代專案應直接使用 **T-SQL BACKUP/RESTORE** 指令搭配 **SQL Server Agent 排程**,或 **SMO (Microsoft.SqlServer.Management.Smo)** 程式庫進行程式化控制。ASP 頁面僅適合臨時手動操作介面,不可作為生產備份策略主力。