Backup and Restore MSSQL Database by Command Line

• ปัญหามีอยู่ว่า เราสร้าง job ไว้ backup database mssql โดยใช้ agent เป็นตัวทำงาน
• แต่เพื่อนเราไป restart database อ่ะดิ คือ พอ restart mssql service แล้ว sql agent จะไม่เริ่มทำงานให้เลยเราต้องไป start อ่ะดิ เพื่อนเราก็ลืม start ให้ด้วยทำให้ database ไม่ได้ backup ไปหลายวันเลย
• เคยใช้ sqlcmd มาได้สักระยะเลยคิดว่าน่าจะทำ batch file backup ได้เหมือน mysqldump อ่ะ
• ลองสั่ง sqlcmd /? ดู options ต่างๆ ก็ได้เรื่อง
• มีน่าจะลองอยู่ 2 option q กับ Q ผลปรากฏว่าใช้ได้จริงๆ แฮะ ^^' เลยได้ batch file backup mssql แล้วเรา ไม่ต้องกลัวเรื่องการ restart service sql server แsละ
• ใช้ batch file กับ task shcedule แทน sql agent job ปลอดภัยกว่า(ปลอดภัยจากเพื่อนอ่ะนะ)
  

Microsoft (R) SQL Server Command Line Tool
Version 9.00.1399.06 NT INTEL X86
Copyright (c) Microsoft Corporation.  All rights reserved.

usage: Sqlcmd            [-U login id]          [-P password]
[-S server]            [-H hostname]          [-E trusted connection]
[-d use database name] [-l login timeout]     [-t query timeout]
[-h headers]           [-s colseparator]      [-w screen width]
[-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
[-c cmdend]            [-L[c] list servers[clean output]]
[-q "cmdline query"]   [-Q "cmdline query" and exit]
[-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
[-u unicode output]    [-r[0|1] msgs to stderr]
[-i inputfile]         [-o outputfile]        [-z new password]
[-f  | i:[,o:]] [-Z new password and exit]
[-k[1|2] remove[replace] control characters]
[-y variable length type display width]
[-Y fixed length type display width]
[-p[1] print statistics[colon format]]
[-R use client regional setting]
[-b On error batch abort]
[-v var = "value"...]  [-A dedicated admin connection]
[-X[1] disable commands, startup script, enviroment variables [and exit]]
[-x disable variable substitution]
[-? show syntax summary]

-- สำหรับ backup database
sqlcmd -S localhost\sqlexpress -Q "backup database dbname to disk='D:\A\dbname.bak'"

-- อันนี้สร้างไฟล์ bak ระบุวันที่ให้ชื่อไฟล์ด้วย น่าจะโอกว่านะ
-- ไฟล์ที่ได้ก็จะประมาณ yyyy-mm-dd_dbname.bak เช่น 2009-07-07_dbname.bak
sqlcmd -S localhost\sqlexpress -Q "backup database dbname
to disk='D:\A\%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%_dbname.bak'"

-- สำหรับ restore database
sqlcmd -S localhost\sqlexpress -Q "backup database dbname to disk='D:\A\dbname.bak'"

sqlcmd -S localhost\sqlexpress -Q "restore database dbname
from disk='D:\A\%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%_dbname.bak'"

อันนี่สคริปฝั่ง client เผื่อกันเหนียว

@echo off
set scriptfile=getfile.txt
set logfile=ftplog.txt
set dbname=qnet
echo open hostnameorip> %scriptfile%
echo yourusername>> %scriptfile%
echo yourpassword>> %scriptfile%
echo ascii>> %scriptfile%
echo cd dir1>> %scriptfile%
echo cd dir2>> %scriptfile%
echo cd dir3>> %scriptfile%
echo get %DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%_%dbname%.bak>> %scriptfile%
echo quit>> %scriptfile%
echo =========%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%_%dbname%.bak==========>> %logfile%
ftp -s:%scriptfile% >> %logfile%
7za a %DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%_%dbname%.7z %DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%_%dbname%.bak
del %DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%_%dbname%.bak

ทิป

• จากปัญหาพบว่าถ้าเรา restart service sqlserver แล้วตัว sql agent จะไม่ start ให้เราหว่า
• เราต้อง Start sql agent เองจริงๆ หรอ หรือว่าจะต้องตั้งค่าอาไรป่าวหว่า เพื่อที่จะให้ restart sqlserver แล้ว sql agent start ใหม่ด้วย
• คำสั่งด้านบนใช้ windows authen อ่ะถ้าจะให้ sql authen ต้องใส่ option เพิ่มอ่ะนะ
• %DATE:~-4%-%DATE:~4,2%-%DATE:~7,2% คือ command ตัวหนึ่งของ dos ก็ไม่รู้อาไรเหมือนกัีนลอกเค้ามาอีกที จาก การใช้ 7zip command line version อ่ะ
• อีกเรื่อง backup ลงที่ D:\ ตรงๆ ไม่ได้หว่าติด permission อาไรของเค้าก็ไม่รู้ เลยสร้าง folder อีกอันขึ้นมาเก็บ bak แทน ^^'
• เรื่องเขียน batch file ตอนเราประกาศตัวแปรเราควร set param=value คือ param เครื่องหมาย = และ value ควรเขียนติดกันจะดีกว่า
• จะให้ง่ายขึ้นไปอีก อยากทำอาไรก็สร้าง store procedure แล้วเรียกผ่าน command line เลยเท่ดี ก็หลัง option Q อ่ะใส่ query อาไรก็ได้เราก็ใส่ exec sp_name ไงใช่ปะ

บทความที่เกี่ยวข้อง

• http://juuier.blogspot.com/2009/06/sqlcmd-utility-in-sql-server.html
  
• http://juuier.blogspot.com/2009/03/backup-and-restore-mysql-database-by.html
• http://juuier.blogspot.com/2009/06/sql-server-2005-backups.html
• http://juuier.blogspot.com/2009/05/restore-mssql-database-with-bak-file.html
• http://juuier.blogspot.com/2009/01/backup-all-ms-sql-server-databases.html
• http://juuier.blogspot.com/2008/07/db-back-up-and-restore-database-sql.html

Reference

• http://www.google.co.th/
• http://stackoverflow.com/
• http://support.microsoft.com/kb/241397
• http://blog.lavablast.com/