經典 ASP (VBScript) 環境下,透過 ADO 執行 BACKUP DATABASE 指令將備份檔寫入伺服器磁碟,再透過 ADODB.Stream 二進位輸出提供下載。
<%@ Language=VBScript CodePage=65001 %>
<%
Response.Charset = "utf-8"
Session.CodePage = 65001
Response.Buffer = True
Response.Clear
' === 連線字串 (請修改) ===
connStr = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=backup_user;Password=Str0ngP@ss;Data Source=192.168.1.100;Initial Catalog=master"
Set conn = Server.CreateObject("ADODB.Connection")
On Error Resume Next
conn.Open connStr
If Err.Number <> 0 Then
Response.Write "連線失敗: " & Err.Description
Response.End
End If
On Error GoTo 0
' === 備份檔路徑 (伺服器本地,需 SQL Server 服務帳號寫入權限) ===
bakName = "backup_" & Year(Now) & Right("0"&Month(Now),2) & Right("0"&Day(Now),2) & "_" & Replace(Time, ":", "") & ".bak"
bakPath = "D:\SQLBackup\" & bakName ' 確保目錄存在且 SQL 服務帳號有寫入權限
' === 執行備份 ===
sql = "BACKUP DATABASE [TargetDB] TO DISK = '" & bakPath & "' WITH INIT, COMPRESSION, STATS = 10"
conn.Execute sql
If Err.Number <> 0 Then
Response.Write "備份失敗: " & Err.Description
conn.Close
Response.End
End If
conn.Close
Response.Write "資料庫 [TargetDB] 備份成功!檔案: " & bakName & "
"
' === 直接輸出下載 (可選) ===
' 注意:大檔案會佔用記憶體,建議改用 X-Sendfile / IIS 靜態檔案下載
'
' Set stream = Server.CreateObject("ADODB.Stream")
' stream.Type = 1 ' adTypeBinary
' stream.Open
' stream.LoadFromFile bakPath
' Response.ContentType = "application/octet-stream"
' Response.AddHeader "Content-Disposition", "attachment; filename=" & bakName
' Response.BinaryWrite stream.Read
' stream.Close
' Response.Flush
' Response.End
%>
BACKUP DATABASE 需 db_backupoperator 角色或 sysadmin。建議建立專用低權限 SQL 登入。DISK = 'D:\SQLBackup\xxx.bak' 是 **SQL Server 服務帳號** 視角的本地路徑,非 Web 伺服器路徑。兩者若非同機需共享資料夾或備份至網路路徑 (\\fileserver\backup\)。WITH COMPRESSION (SQL 2008 Enterprise+ / 2014 Standard+) 大幅縮減檔案體積與 I/O 時間。ADODB.Stream 會將整個檔案讀入記憶體,大檔案易導致 Out of memory。改用 IIS X-Sendfile (需模組) 或直接對應虛擬目錄提供下載連結。Backup-DbaDatabase -SqlInstance sql01 -Database TargetDB -Path D:\Backup -CompressBackup。BACKUP DATABASE ... TO URL = 'https://storage.blob.core.windows.net/container/backup.bak' WITH CREDENTIAL = 'cred'。WITH ENCRYPTION)。RESTORE VERIFYONLY FROM DISK = '...'。