Solved database is in use when try restore database

• จากบทความก่อนหน้านี้ ทดสอบ restore Db โดยไม่ต้อง restart sql service เลย
• สิ่งที่ชอบทำก่อน restore คือ restart service sqlexpress เพราะ ถ้าไม่ทำจะขึ้น error ประมาณ db is in use อ่ะ
• แต่ พอ restart service แล้ว ถ้า sql นั้นมี db เราอยู่ก้อนเดียวก็ไม่มีปํญหาอาไรหรอก แต่ปัญหาจะมี ถ้ามี db หลายก้อนซึ่งก้อนอื่นๆ เค้าก็ยังใช้งานอยู่
• เมื่อเรา restart service ทำให้ connection ของ Db ทุกก้อนบน sql service นั้นหลุดหมดสิครับพี่น้อง
• ซึ่งเป็นสิ่งที่เราไม่สมควรทำอย่างยิ่ง ยิ่งถ้า service นั้นเป็น process บน service ด้วยแหละ
• เลยหาข้อมูลมาเก็บไว้ที่บทความก่อนหน้านี้แต่ ยังไม่ได้ทดสอบอาไรสักอย่าง
• บทความนี้สรุปจากผลการทดลองของตัวเองโดย restore ผ่าน Microsoft SQL Server Management Studio
  

-- เลือกใช้ db ก้อนอื่น อาไรก็ได้ที่ไม่ใช่ก้อนที่เราจะทำการ restore
-- ถ้าเราไม่สั่งให้ดู db ที่จะถูกเรียกใช้ทางซ้ายบน
use master;

-- เปลี่ยน mode ให้ Db นั้นให้สามารถเข้าถึงได้คนเดียวก่อน
-- เพื่อป้องกันขณะเรา restore ไม่ให้เรียกใช้ db ก้อนนี้ได้
-- อันนี้เอาไว้ใช้ก่อน resotore สิครับ แล้วเราจะวางที่นี้ไมหว่า ^^'
Alter Database yourdbname SET SINGLE_USER With ROLLBACK IMMEDIATE

-- ทำการประการ และ กำหนดชื่อ db ที่จะกู้ให้ตัวแปร
DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'yourdbname'
--SET @DatabaseName = DB_NAME()
-- db_name() เป็น function ที่ให้ค่า db ที่เราทำการ รัน command อยู่นั่นแหละ

-- สร้างตัวแปร สำหรับเก็บประโยค ทำลาย process ที่ติดต่อกับ db
DECLARE @SQL varchar(max)
SET @SQL = ''

-- สร้างประโยคสำหรับทำลาย process
SELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

-- @@spid จะให้ค่า process id ของ sql command ที่เรากำลังทำงานอยู่นี่อ่ะ
-- คำสั่งฆ่า prcess แค่ KILL SPID แค่นี้อ่ะ จาก ประโยค select ที่กำหนดค่าให้ @sql
-- คือ ถ้ามี prcess หลายค่าที่ connect กับ db ก้อน @DatabaseName
-- ประโยคก็จะเป็นประมาณ kill 1;kill 2;kill 3;...
-- อาไรประมาณนั้นนะคิดว่า เพราะ @sql = @sql + ... อ่ะนะ น่าจะนะ ไม่แน่ใจ ลองปริ้นดู
-- print 'Hello' + cast(@sql as varchar)

-- ทำการเริ่ม execute varchar ที่เป็นประโยค ทำลาย process
EXEC(@SQL)

-- เปลี่ยนโหมดกลับคืนให้ db สามารถเข้าถึงได้หลายๆ คน
-- อันนี้เอาไว้ใช้หลัง resotore สิครับ แล้วเราจะวางที่นี้ไมหว่า ^^'
ALTER DATABASE yourdbname SET MULTI_USER WITH ROLLBACK IMMEDIATE

• สั่งคำสั่งด้านบนเนี๋ย หลังจากนั้นทำการ restore ได้เลย

Note:

• ตอนเราทำการ restore เราไม่ต้องเปลี่ยน mode เลยก็ได้ ถ้าเราแน่ใจว่า ตอน restore จะไม่มีใครมายุ่ง กับ Db ที่จะ restore
• ถ้าเราทำลาย process แล้ว restore ไม่ได้เหมือนเดิม ให้สังเกต available database (ด้านซ้ายบน) ว่า select ที่ db ก้อนที่เราจะทำการ restore อยู่หรือไม่
• ถ้า select อยู่ที่ db ที่เราจะ restore ให้เลือกไปก้อนอื่นๆ หรือ master ไปเลยก็ได้ เพราะ นี่ก็เป็น process id connection ตัวหนึ่งเหมือนกัน จำไว้ให้ดี

สรุปคือ

• ถ้าขึ้นประมาณ database is in use วิธีแก้ให้เราทำลาย connection ของ Db ก้อนนั้นก่อนค่อยดำเนินการต่อนะ
• แนะนำใช้ Activity Monitor (วิธีแรกของบทความเนี๋ย) ในการทำลายการติดต่อง่ายที่สุดโดยคลิกขวาที่ โพรเซสที่ต้องการทำลายเลือก Kill Process และ กด Refresh หน่อย connection นั้นก็จะหายไปทำให้เรา restore ได้ไม่มีปัญหา database in use เหมือนกัน ง่ายมากๆ
  

อ้างอิง

• http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/aad41cbb-10cb-4109-9e55-aab048bbeb9d
• http://www.sqlmag.com/Articles/Index.cfm?ArticleID=93877&DisplayTab=Article

ทิป

• ถ้าเป็น sql server เวอร์ชั่นเต็ม toolkit ที่ใช้จัดการ db จะมีตัว activity monitor อยู่ เราสามารถ kill process ได้จากที่นั้นเลย แล้วค่อยทำการ restore
• ส่วน sql express toolkit จะไม่มี activity monitor อ่ะนะ เราเลย kill process ผ่าน interface งามๆ ไม่ได้เลยต้อง kill ผ่าน sql command แบบนี้อ่ะ
• เราสามารถสั่ง EXEC SP_WHO หรือ EXEC SP_WHO2 เพื่อ monitor process ต่างๆ ได้ดวยนะ

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

• http://juuier.blogspot.com/2009/06/ow-to-kill-all-processes-that-have-open.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/2009/03/backup-and-restore-mysql-database-by.html
• http://juuier.blogspot.com/2008/07/db-back-up-and-restore-database-sql.html

อันนี้ explain โดยเว็บนอก

โดยคุณ smuong , June 13, 2007 8:13 PM

Error 3101
Severity Level 16
Message Text
Exclusive access could not be obtained because the database is in use.

Explanation
This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action
Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.


MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

- If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

• ถ้าเราทำการ kill spid แล้ว restore ขึ้น error ด้านล่างงี้

• ให้เราไปที่ option แล้วติ๊กที่หน้า overwrite

• โอเคได้เลยไม่มีปัญหา

• ปัญหาเรื่อง database is in use ก็จะไม่เป็นปัญหากับเราอีกแหละ