Cant restore the database backup. Exclusive access could not be obtained

Hi everyone,

Hope somebody can help me on this.

I did a full BACKUP for two SQL databases using SQL Server Managament Studio. When trying to RESTORE the DBs, I get the following error:

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

There are NO users using the application. These are DBs for the Portfolio Server 2007 application.

After exploring a little bit, I found that there are several "SLEEPING" processes with an "AWATING COMMAND" flag. The processes come from the Portfolio Server Application, maybe from previous sessions.

Can they be deleted? if so, would there be any serious consequences?

Thank you for your help on this.

Oscar E.

Hi Oscar,

Take a look at this article, I think it will help.

Killing ProcessIDs using SMO
http://sqlblogcasts.com/blogs/seanprice/archive/2007/07/11/Killing-ProcessIDs-using-SMO.aspx

Mark.
|||Forcibly terminate all user processes as step 1 of your scheduled task - here's a stored proc that I got from somewhere that does this, for a named db:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--Type procedure
--author mak mak_999@.yahoo.com
--date written 4/19/2000
--project maintenance
--objective procedure to kill process for a given database

ALTER procedure usp_killprocess @.dbname varchar(128) as
set nocount on
set quoted_identifier off
declare @.kill_id int
declare @.query varchar(320)
declare killprocess_cursor cursor for
select a.spid from sysprocesses a join
sysdatabases b on a.dbid=b.dbid where b.name=@.dbname

open killprocess_cursor
fetch next from killprocess_cursor into @.kill_id
while(@.@.fetch_status =0)
begin
set @.query = 'kill '+ convert(varchar,@.kill_id)
exec (@.query)
fetch next from killprocess_cursor into @.kill_id
end
close killprocess_cursor
deallocate killprocess_cursor

--usage
--exec usp_killprocess "mydatabasename"

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

I use this as part of a scheduled backup live/restore to development (kill users before performing the restore) task and it works fine.