Error : Exclusive access could not be obtained because the database is in use

Posted By dotnetfish On 16 Oct 2010 10:10:47

Tag : Sql Servers

Digg del.icio.us Newsvine Facebook Google MySpace Reddit Twitter Windows Live YahooBuzz FriendFeed

No of Views	:	1258
Bookmarked	:	0 times
Votes	:	0 times

Introduction

I try to restore a new database in MSSQL 2005 and hit error Restore Failed For Server 'xxxx' (Microsoft.SqlServer.Smo)

Additional information:
System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)

Reason for Error

This is mostly caused by others user/session is using the database at the same time. To check whether got others user accessing the same database, execute SP_WHO will show who is using the database. Execute KILL to kill the process. In my case, i want to kill process with spid = 53

execute SP_WHO -- See which process in running

Execute KILL 53 -- 53 is the process id spid that i want to kill before restore.

In my case, i restore the database using script instead of using the SSMS.

Below is the script that i use to restore without any issue.

RESTORE DATABASE RateMyPosts
FROM DISK = '[PATH of Root]\RatePosts.bak' WITH REPLACE,
MOVE 'RatePosts' TO
'[PATH of Root]\Microsoft SQL Server\MSSQL.1\MSSQL\Data\RatePosts.mdf',
MOVE 'RatePosts_Log' TO
'[PATH of Root]\Microsoft SQL Server\MSSQL.1\MSSQL\Data\RatePosts_log.ldf'

Once you are run above script, you able to restore the database without any issues.its work fine. hopes this help.


Other popular Tips/Tricks
How to ALTER or ADD two columns in Sql Server Table using T-Sql In this tips, I will explain how to ALTER or ADD two or more columns in to table by t-sql script. Published Date : 25/Mar/2011 Tracking Change Data Capture in SQL Server 2008 A very important feature of SQL Server 2008 is that we can enable CDC(Change Data capture) on database or table.We can track the database had CDC enabled by querying IS_CDC_ENABLED column Published Date : 17/Jan/2011 Tracking Change Data Capture in SQL Server 2008 A very important feature of SQL Server 2008 is that we can enable CDC(Change Data capture) on database or table.We can track the database had CDC enabled by querying IS_CDC_ENABLED column Published Date : 17/Jan/2011 How to use DATEDIFF() function in Sql Server This is the third tips date related function in sql server. In this I going to show you how to use DATEDIFF() function in sql server. The DATEDIFF () is useful to get the specified date part between two dates Published Date : 16/Dec/2010 How to use DATEADD function in Sql Server The DATEADD function is a powerful built-in function sql server to add dates in different way and types. So I would like to explore in this tip how we can use the DATEADD function for different purpose. Published Date : 12/Dec/2010

Comments

By:Pravesh Singh	Date Of Posted:3/15/2012 6:15:02 AM
Hosting Crystal Report in Windows Form
I was reading your article and I would like to appreciate you for making it very simple and understandable. This article gives me a basic idea of Hosting Crystal Report in Windows Form. Check these articles too.... http://msdn.microsoft.com/en-us/library/aa665783%28v=vs.71%29.aspx http://www.mindstick.com/Articles/09e06499-6f64-4fc6-bc84-8cc40a72103e/?Hosting%20Crystal%20Report%20in%20Windows%20Form