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.

Cant Restore SQL Server databases: 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,

you can go ahead and kill those process as they are no longer active.......those connections are inactive and shud not cause any harm to db ....

cool

|||

Thank you very much Deepak.

It worked.

Regards,

Oscar E.

Cant Restore SQL Server databases: 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,

you can go ahead and kill those process as they are no longer active.......those connections are inactive and shud not cause any harm to db ....

cool

|||

Thank you very much Deepak.

It worked.

Regards,

Oscar E.

Cant Restore SQL Server databases: 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,

you can go ahead and kill those process as they are no longer active.......those connections are inactive and shud not cause any harm to db ....

cool

|||

Thank you very much Deepak.

It worked.

Regards,

Oscar E.

Can't restore database

I'm trying to restore a I keep getting this message:
"exclusive access could not be obtained because the
database is in use. RESTORE DATABASE is terminating
abnormally."
How can I force a restore. I don't mind if I have to
drop the database and restore the data, but even if I get
the same message when I try to drop the database. Please
help.Try executing sp_who2 to identify the database connection(s) using the
database. You can kill these if needed. Also, check the database context
of your restore command to ensure it is not in the target database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Vic" <vduran@.specpro-inc.com> wrote in message
news:1373e01c3c1ed$e621a380$a601280a@.phx.gbl...
> I'm trying to restore a I keep getting this message:
> "exclusive access could not be obtained because the
> database is in use. RESTORE DATABASE is terminating
> abnormally."
> How can I force a restore. I don't mind if I have to
> drop the database and restore the data, but even if I get
> the same message when I try to drop the database. Please
> help.|||Hi ,
Please schedule the below commands in your job , so as all the process
connetced to your database will be killed and then restore starts.
use master
go
declare @.x varchar(255)
set @.x=''
select @.x = @.x + ' kill ' + convert(varchar(5), spid)
from master.dbo.sysprocesses
where db_name(dbid) = 'pubs'
exec (@.x)
go
restore database <dbname> from disk='c:\mssql\backup\dbname.bak'
(Once the restore starts no user is allowed to access the database.)
otherwise
1. Use the ALter Database command to start the database in single user mode
(Alter database dbname set SINGLE_USER)
2. Try Restoring
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ujsoKFfwDHA.3116@.tk2msftngp13.phx.gbl...
> Try executing sp_who2 to identify the database connection(s) using the
> database. You can kill these if needed. Also, check the database context
> of your restore command to ensure it is not in the target database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
> "Vic" <vduran@.specpro-inc.com> wrote in message
> news:1373e01c3c1ed$e621a380$a601280a@.phx.gbl...
> > I'm trying to restore a I keep getting this message:
> >
> > "exclusive access could not be obtained because the
> > database is in use. RESTORE DATABASE is terminating
> > abnormally."
> >
> > How can I force a restore. I don't mind if I have to
> > drop the database and restore the data, but even if I get
> > the same message when I try to drop the database. Please
> > help.
>