[SQL][PowerShell]如何在 SQL Express 的環境下可以定時進行資料庫備份

雖然 SQL Express 沒有 SQL Agent 可以使用,但我們依然可以透過 Task Schedule + PowerShell ,很土砲的方式效果

SQL Server Express 一值是一些系統的好工具,像是小型的 POS 或者是刷卡系統,可以再免費的狀況下提供 RDBMS 的功能,且又不像 Access 的 mdb 檔案那麼容易損壞,後續如果有需要也可以很方便的升級到 SQL Server 上面。

但 SQL Server Express 還是會有一些使用上的限制,像是資料庫最大只能10G,只能使用到 1 核心和 1GB 的記憶體,另外也沒有 SQL Agent 可以來排程來進行一些維護,因此要備份資料庫就稍微比較麻煩一點。

最近剛好有朋友有需要這樣的功能,就找個時間把之前的語法整理出來,順便改寫成 PowerShell 的方式來執行,因此就整理一下相關指令如下


param(  
    $DBName,
    $Path,
    $Type
)

$File = "{0}\{1}_{2}_{3}.bak" -f $Path,$DBName,(Get-Date -Format yyyyMMddHHmm),$Type 
$Zip  = "{0}.zip" -f $File

# Cretae Folder
If(!(Test-Path $Path))
{
    New-Item -ItemType directory -Path $Path
}

# Delete Old Files
Get-ChildItem –Path $Path -Recurse | Where-Object {($_.LastWriteTime -lt (Get-Date).AddDays(-30))} | Remove-Item


# Backup
if     ( $Type -eq "Full" ) { Backup-SqlDatabase -ServerInstance ".\SQLEXPRESS" -Database $DBName -BackupFile $File }
elseif ( $Type -eq "Log"  ) { Backup-SqlDatabase -ServerInstance ".\SQLEXPRESS" -Database $DBName -BackupFile $File -BackupAction Log }
elseif ( $Type -eq "Diff" ) { Backup-SqlDatabase -ServerInstance ".\SQLEXPRESS" -Database $DBName -BackupFile $File -Incremental }

# Ccmpress
Compress-Archive -LiteralPath $File -CompressionLevel Optimal -DestinationPath $Zip
Remove-Item $File

這個 Script 主要會用到 PowerShell 裡面的幾個主要指令,像是 Backup-Database , Compress-Archive , 其他就都只是一些簡單的處理。因此在完成 Script 之後,接著我們就可以利用 Windows 的 Task Schedule 來進行排程執行。

假設您需要每天建立一個完整備份,那麼可以在 Task Schedule 中選擇「建立工作」,在第一頁上除了要設定名稱,更要記得這個工作要設定為「不管使用者登入與否均執行

接著進入第二個頁籤「觸發程序」內設定要執行的時候,這裡我們就設定每天凌晨 12:00 的時候來觸發程序進行備份

而在第三個「動作」的頁籤下,我們選擇要透過 PowerShell 來進行,因此在程式或指令碼的地方,我們就輸入「powershell.exe」,並且在新增引數的地方,輸入「-ExecutionPolicy Bypass D:\SQLBackup.ps1 myDB D:\Backup Full」。這裡就要看您的 Script 檔案放在哪裡,像我的 Script 是放在 D:\SQLBackup.ps1 內,因此指定檔案後,後面要加入三個參數,分別是資料庫名稱、存放備份的目錄和備份類型

這樣設定好之後,那麼應該就可以正常執行了,如果您想更複雜一點,要做到交易紀錄備份或差異備份,那麼在最後一個參數的部分,就可以對應改成 Log 或 Diff ,這樣就可以透過 Windows Task Schedule 和 PowerShell ,來取代原本想要用 SQL Agent 搭配維護計畫的備份管理了。