วิธีตั้ง Schedule สำหรับ Backup SQL Server Express

ข้อเสียอย่างหนึ่งของ SQL Express ก็คือ มันไม่มี Maintenance plan และไม่มี SQL Server Agent ดังนั้น คำถามที่จะพบบ่อย ก็คือ จะสามารถตั้ง Schedule สำหรับ Backup Server Express หรือทำ Automatic Backup SQL Server Express ได้อย่างไร

โชคดีที่ Windows ยังมี Scheduled Tasks มาให้ สำหรับใช้ตั้งเวลาให้โปรแกรมทำงาน ดังนั้น หากเราสามารถเขียน Script ที่ใช้ในการ Backup ได้ แล้ว Add เข้าไปใน Scheduled Tasks และ ตั้งเวลา เราก็สามารถ Backup SQL Server Express แบบอัตโนมัติได้ เช่นกัน

วิธีที่ได้ทดลองใช้ ก็คือ ใช้สั่ง Backup ผ่าน batch file ซึ่งคนที่เคยใช้ DOS คงจะรู้จักกันดี โดยมีรายละเอียดของ batch file ดังนี้

BackupDB.BAT

@echo off
set bkfolder=d:\backup
@echo Backup database is working ....
REM ************* Backup database DB 01***************
set dbname=DB01
for /F "tokens=1,2,3 delims=/ " %%i in ('date/t') do @set d=%%k%%j%%i
for /F "tokens=1,2,3 delims=:. " %%i in ('time/t') do @set t=%%i%%j%%k
sqlcmd -E -S.\SQLEXPRESS -o"%bkfolder%\backup_db_%dbname%_%d%%t%.log" -Q"backup database %dbname% to disk='%bkfolder%\%dbname%_%d%%t%.bak' "

REM ************* Backup database DB 02***************
set dbname=DB02
for /F "tokens=1,2,3 delims=/ " %%i in ('date/t') do @set d=%%k%%j%%i
for /F "tokens=1,2,3 delims=:. " %%i in ('time/t') do @set t=%%i%%j%%k
sqlcmd -E -S.\SQLEXPRESS -o"%bkfolder%\backup_db_%dbname%_%d%%t%.log" -Q"backup database %dbname% to disk='%bkfolder%\%dbname%_%d%%t%.bak' "

REM ************* Delete old files ***************
@echo Deleting files older than 3 days ...
mkdir %bkfolder%\trash
robocopy %bkfolder% %bkfolder%\trash *.* /MOV /MINAGE:3 /NFL /NDL /NJH /NJS
del %bkfolder%\trash\*.* /q
rmdir %bkfolder%\trash

คำอธิบายการทำงาน
- เครื่องที่ทดสอบ ตั้ง Date format ใน Regional Options ไว้เป็น dd/mm/yyyy
- บรรทัดที่ 2 กำหนด path ที่จะเก็บไฟล์ backup เอาไว้ที่ d:\backup
- บรรทัดที่ 5 กำหนดชื่อ DB ที่จะ backup ให้เป็น DB01
- บรรทัดที่ 6 ใช้คำสั่ง date/t เพื่อแยกค่า วัน/เดือน/ปี สลับตำแหน่งเก็บใส่ตัวแปร d ในรูปแบบ yyyymmdd
- บรรทัดที่ 7 ใช้คำสั่ง time/t เพื่อเก็บเวลาใส่ ตัวแปร t
- บรรทัดที่ 8 ใช้ sqlcmd เพื่อสั่ง ให้ run คำสั่ง backup โดย Connect เข้าที่ ./SQLEXPRESS แบบ trusted connection โดยจะ backup database DB01 ไปเก็บไว้ใน d:\backup ชื่อ DB01_yyyymmddhhmmAM.bak และสร้างไฟล์ log ชื่อ backup_db_DB01_yyyymmddhhmmAM.log ไว้ให้
- บรรทัดที่ 10-14 เหมือนกับ 4-8 แต่เป็น DB02
- บรรทัดที่ 16-21 เป็นการใช้ robocopy ซึ่งอยู่ใน Windows Server 2003 resource kit ในการช่วยลบไฟล์ที่เก่ากว่า 3 วันทิ้งไปจาก d:\backup ซึ่งหากต้องการแก้ไขจำนวนวัน ก็กำหนดได้ที่ /MINAGE:3

(ตอนตรวจเลขบรรทัด อาจจะต้อง copy code ไปไว้ใน notepad ก่อน แล้วจึงค่อยเทียบบรรทัด เนื่องจากอาจจะเกิดการ wrap text ขึ้นบรรทัดใหม่ของข้อความใน blog)

จากนั้น ก็ตั้งเวลาใน Scehduled Tasks ให้ไปเรียก BackupDB.bat ให้ run ตามเวลาที่ต้องการ ซึ่งหากจะเพิ่ม Database ที่จะทำการ backup หรือ จะเพิ่มการ Backup log ก็สามารถเพิ่มเข้าไปใน batch file ได้


นอกจากวิธีนี้แล้ว เท่าที่เคย Search ดู ผมพบว่ามีอยู่ 2 บทความที่น่าสนใจ ก็คือ
- Automating Database maintenance in SQL 2005 Express Edition ของคุณ Jasper Smith ซึ่งมีอยู่ 2 ตอน
- Scheduling Backups for SQL Server 2005 Express ของ Edgewood Solutions Engineers

ซึ่งทั้ง 2 บทความ จะใช้วิธีสร้าง Stored Procedure ขึ้นมา แล้วเรียกใช้เพื่อสั่งให้ Backup โดยตั้งเวลาให้เรียกทำงานผ่าน Scheduled Tasks ของ Windows เช่นกัน

( ทดลองกับ SQL 2005 Express Edition บน Windows XP )