Restore MSSQL Database with bak file

• ครั้งก่อนๆ เคยเขียนไว้เกี่ยวกับ backup mssql ผ่าน store procedure
• ส่วนครั้งนี้เป็นการ restore ไฟล์ bak ที่เราได้ทำการ backup ไว้คราวก่อนไง
• ผ่าน store procedure เหมือนเดิม
• รู้กันอยู่ใช่มั้ยเราสามารถนำคำสั่งใน store procedure ไปรันฝั่งโปรแกรม client ผ่าน sqlcommand ได้เลย ที่นี้ถ้าเราต้องการ restore เราควร เราไม่ควรสร้าง connection string ไปเรียก db ที่เรากำลังจะทำการ restore ผ่านโปรแกรมของเราเอง เช่น ควรสร้าง connection string เรียก master เป็นต้น
• ส่วนการ backup ก็ใช้ connection string ปกติที่เราใช้ connect นั่นแหละ
• คือ ที่นี่ เค้าบอกมาอ่ะนะ

-------------------- Example ---------------------------
print 'Start example...'

RESTORE DATABASE [dbname]
FROM DISK = 'd:\databases\db.bak'

print 'done.'

-------------------- Example 1 ---------------------------
print 'Start example 1...'
ALTER DATABASE [dbname] SET SINGLE_USER

RESTORE DATABASE [dbname]
FROM DISK = 'd:\databases\db.bak'

ALTER DATABASE [dbname] SET MULTI_USER
print 'done 1.'

-------------------- Example 2 ---------------------------
print 'Start example 2...'
ALTER DATABASE [dbname] SET SINGLE_USER

DECLARE @cmd nvarchar(2000)
SET @cmd = 'RESTORE DATABASE [dbname] FROM DISK = ''d:\databases\db.bak'''
exec (@cmd)

ALTER DATABASE [dbname] SET MULTI_USER
print 'done 2.'

-------------------- Example 3 ---------------------------
print 'Start example 3...'
declare @db_name varchar(50)
set @db_name = '[dbname]'
declare @db_path varchar(50) 
set @db_path = 'd:\databases\db.bak'

DECLARE @cmd_test varchar(2000)

SET @cmd_test = 'ALTER DATABASE ' + @db_name + ' SET SINGLE_USER '
+ 'RESTORE DATABASE ' + @db_name + ' FROM DISK = ''' + @db_path + ''''
+ 'ALTER DATABASE ' + @db_name + ' SET MULTI_USER'

exec (@cmd_test)
print 'done 3.'
-----------------------------------------------

Note

• ''' + @db_path + ''''
• '''' is single quote
• ALTER DATABASE [dbname] SET SINGLE_USER เพื่อป้องกันคนเรียกใช้ตอน restore
• ALTER DATABASE [dbname] SET MULTI_USER ตั้งค่าให้ user อื่นๆ ใช้ db นี้ได้เหมือนเดิม
• ที่นี่ เค้าบอกมาว่ากันไว้ก่อน
• การ backup นั้นส่วนมากจะมีปัญหาเรื่อง permission (vista กับ server 2008 มีแน่) การเขียน bak
• ประมาณด้านล่างเนี๋ยเลย อันนี้พยายาม backup ไปที่ d: เลยใน vista อ่ะนะ
• เปลี่ยน path หรือ เพิ่มสิทธิ์ก็น่าจะหาย
  

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'D:\db.bak'. Operating system error 5(error not found).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Related

• http://juuier.blogspot.com/2009/01/backup-all-ms-sql-server-databases.html

Reference

• http://www.expert2you.com/article1/3675/index.htm?page_no=1
• http://weblogs.sqlteam.com/tarad/archive/2005/11/08/8262.aspx
• http://www.sqlservercentral.com/Forums/Topic551363-357-1.aspx
• http://www.devx.com/tips/Tip/17499
• http://www.codeproject.com/KB/database/mft_jet_restore.aspx
• http://itknowledgeexchange.techtarget.com/itanswers/passing-a-database-name-as-a-stored-procedure-parameter/
• http://www.siusic.com/wphchen/the-best-way-to-backup-sql-server-120.html
• http://www.simple-talk.com/sql/backup-and-recovery/sql-server-2005-backups/

ทิป
- สังเกตปะ เวลาเราเรียก store procedure เราสั่ง exec sp_name หรือ execute sp_name
- แต่ถ้าเราต้องการ exec varchar ให้เราใช้ประมาณ exec(@param) เช่น

declare @param varchar(max)
set @param = 'select * from table'
exec(@param)

สรุปสั้นได้ใจความเรื่อง backup และ restore mssql

-------------------- Easier -------------------
จริงแล้ว backup และ restore สั้นๆ แค่นี้ก็ได้้ถ้าไม่ได้สนใจเรื่องอื่นๆ นะ

Backup Database [DatabaseName] to DISK='c:\BackupFile'

Restore Database [DatabaseName] from DISK='c:\BackupFile'

- การ backup ถ้ามีไฟล์นั้นอยู่แล้ว backup ครั้งนั้นจะเป็นการ append ข้อมูลโดยอัตโนมัติ
ถ้าจะเปลี่ยนเป็น overwrite เนี๋ยไม่รุ้อ่ะนะว่าต้องเติม option อาไรอีก with init สิ

- ส่วนการ restore ถ้าไฟล์ bak มีการ backup แบบ append การ restore จะเลือกเอา ชุดแรกของไฟล์ backup เสมอ แต่ก็น่าจะมี option เลือกได้อ่ะนะว่าจะเอาชุดใหน แล้ว option ไรหว่า
ก็ with file = ตัวเลขชุด เช่นมีการ append 3 ชุด ปกติถ้าไม่มี option อาไรจะเลือก ชุด 1 คือ backup ครั้งแรก มา restore ถ้าต้องการเอาชุด 2 มา restore ก็ with file = 2

ตัวอย่างการ backup แบบ overwrite และ restore เลือกชุด backup

Backup Database [DatabaseName] to DISK='c:\BackupFile' with init

Restore Database [DatabaseName] from DISK='c:\BackupFile' with file = 2

-------------------- End easier ---------------