MS SQL Express:ตั้งเวลาBackup DatabaseและลบBackupเก่าอัตโนมัติ

MS SQL Expressจะไม่มีSQL Server Agentซึ่งสามารถตั้งScheduleเพื่อทำการBackup SQL Server ในบทความนี้จะเสนอเทคนิคการใช้PowershellของWindowsในการสร้างไฟล์BackupและลบBackupเก่าของMS SQL Expressโดยให้Windos Task Scheduleสั่งงานอัตโนมัติครับ

สร้างไฟล์Powershellนามสกุลps1ด้วยNotepad เช่นชื่อไฟล์ SQL_Express_Job.ps1 (ระวังอย่าลืมบันทึกแบบ All File ดังรูป เนื่องจากถ้าไม่เลือกไฟล์จะถูกบันทึกเป็นนามสกุล.txt)

เนื้อหาของไฟล์คือ

$serverName = "localhost"
$backupDirectory = "D:\backupSQL"
$daysToStoreBackups = 0

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
 
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $serverName

$dbs = $server.Databases

Get-ChildItem "$backupDirectory\*.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToStoreBackups)} |% {Remove-Item $_ -force }
"removed all previous backups older than $daysToStoreBackups days"

foreach ($database in $dbs | where { $_.IsSystemObject -eq $False})
{
           $dbName = $database.Name      
        
            $timestamp = Get-Date -format yyyy-MM-dd-HHmmss
            $targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + ".bak"
 
            $smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
            $smoBackup.Action = "Database"
            $smoBackup.BackupSetDescription = "Full Backup of " + $dbName
            $smoBackup.BackupSetName = $dbName + " Backup"
            $smoBackup.Database = $dbName
            $smoBackup.MediaDescription = "Disk"
            $smoBackup.Devices.AddDevice($targetPath, "File")
            $smoBackup.SqlBackup($server) 
            "backed up $dbName ($serverName) to $targetPath"
               
}

 

โดยจะต้องกำหนดพารามิเตอร์ดังต่อไปนี้ให้ตรงกับที่ต้องการ

$serverName : ชื่อ SQL Server เช่น localhost หรือ (local)\sqlexpress แล้วแต่ที่ตั้งชื่อไว้(ดูในManagement Studio Expressได้)

$backupDirectory : พาธไดเรกทอรี่ที่เก็บไฟล์ Backup

$daysToStoreBackups : จำนวนวันที่ต้องการเก็บไฟล์ Backup

โค้ดข้างต้นจะตรวจสอบไฟล์ .BAK ว่าเก่ากว่าระยะเวลา(วัน)ที่กำหนดในพารามิเตอร์ $daysToStoreBackups หรือไม่ ถ้าใช่ก็จะลบทิ้งแล้ววนลูปสร้างBackupขึ้นมาในทุกDatabaseที่มีอยู่

 

สร้างTask Schedule

ให้สร้างBasic Taskขึ้นมา

ดำเนินการตามวิซาร์ดและกำหนดระยะเวลาเช่น Monthly จนถึงขั้นตอนเลือกActionที่จะให้Taskทำงานให้เลือก “Start a program”

ใส่พาธของโปรแกรมPowershellเช่นอยู่ใน C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe และใส่พาธของไฟล์ps1ที่สร้างไว้ในช่องAdd arguments (optional):

คลิ้ก Next และ Finish

เป็นอันเสร็จสิ้น