最簡單步驟備份 SQL Server 資料庫到本地：ASP + ADO + BACKUP DATABASE

經典 ASP 頁面一鍵備份下載：適合輕量級託管環境無管理工具權限時

利用 `ADODB.Connection` 執行 `BACKUP DATABASE ... TO DISK` 將 `.bak` 寫至伺服器本地路徑，再用 `ADODB.Stream` 二進位輸出供下載。

核心程式碼 (VBScript/ASP)

<%
Response.Charset = "utf-8"
Session.Codepage = 65001
Response.Buffer = True
Response.Clear

' 連線字串 (依實際修改)
conStr = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=endiva;Password=endiva;Data Source=211.160.78.6"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open conStr

' 備份檔名含時間戳
bkfname = Server.MapPath(FormatDateTime(Now(), 5) & "database.bak")  ' 如 20240115103000database.bak
sqlStr = "BACKUP DATABASE fang TO DISK='" & bkfname & "' WITH INIT"
conn.Execute sqlStr
conn.Close

Response.Write "database [fang] backup success!
"
Response.Write "click download"

' 直接串流下載 (註解區塊，啟用即可)
' Set fn = Server.CreateObject("ADODB.Stream")
' fn.Mode = 3 ' adModeReadWrite
' fn.Type = 1 ' adTypeBinary
' fn.Open
' fn.LoadFromFile bkfname
' Response.ContentType = "application/octet-stream"
' Response.AddHeader "Content-Disposition", "attachment; filename=database.bak"
' Response.BinaryWrite fn.Read
' fn.Close
%>

關鍵注意事項

現代化替代 (強烈建議)

• Ola Hallengren Scripts + Agent 作業**：排程備份、壓縮、加密、驗證、清理、異地複製。
• PowerShell + dbatools**：`Backup-DbaDatabase -SqlInstance SRV -Database fang -BackupDirectory \\NAS\Backup -Compress -Encrypt`。
• Azure Blob / S3 直接備份**：`BACKUP DATABASE fang TO URL = 'https://account.blob.core.windows.net/container/fang_20240115.bak' WITH CREDENTIAL = '...', COMPRESSION, ENCRYPTION`。

結語

ASP + ADO 備份屬於 **「無工具權限、小資料庫、臨時救急」** 的末招。**生產環境請用標準化備份策略**，確保可恢復性 (RPO/RTO)、安全性、可審計性。