Restoring of database using SQL-DMO part 2

Hi its me again, I posted a message some time ago concerning restoring of database.

http://www.dbforums.com/showthread.php?postid=3582736#post3582736

My restore module basically restores a backup of a database file.

I found that when I integrate my restore module with a login module, I am getting the same exclusive access error again. I also found out that the connection created for authenticating the user, remains sleeping even after it is closed, which in effect prevents my restore module to get exclusive access.

What am I doing wrong? Any ideas appreciated, thanks for reading!Run This before you restore database statement:
declare @.l_spid varchar(4)
,@.l_hostname varchar(20)

declare kill_cursor scroll cursor
for
select convert(varchar(4), spid), hostname from master..sysprocesses with (nolock)
where db_name(dbid) = 'Your Database Name'

open kill_cursor
select @.@.cursor_rows

fetch next from kill_cursor into
@.l_spid
,@.l_hostname
while (@.@.fetch_status = 0 )
begin
select @.l_hostname Killed
exec ( 'kill ' + @.l_spid)
fetch next from kill_cursor into
@.l_spid
,@.l_hostname
end
close kill_cursor
deallocate kill_cursor

YOUR RESTORE DATABASE STATEMENT|||Thanks suresh. I will try that.sql

Restoring Database using SQL-DMO gives me an error on another DB Server

Hi I am trying to restore a database using SQL-DMO. Restoring works on my development computer, but it does not work on my colleague's development computer. Although there are no users using the database in question, I get the following error message while attempting to restore the backup file:

Exclusive access could not be obtained. Database is in use.

Do I have to do anything in particular? Here is my snippet of code (VB.net). Thanks.

Dim my_srv As SQLDMO._SQLServer = New SQLDMO.SQLServerClass
my_srv.Connect(my_config.get_ServerName(), my_config.get_DBUserID(), my_config.get_Password)

'COPY FILE TO SPECIFIC DIRECTORY ON SERVER
Dim my_file As File
my_file.Copy(Me.txtFilePath.Text, "\\" + my_config.get_ServerName + my_config.get_backupfolder_string + "tempcas.bak")

Dim my_restore As SQLDMO.Restore = New SQLDMO.RestoreClass
'my_restore.Devices = my_restore.Files
my_restore.Action = SQLDMO_RESTORE_TYPE.SQLDMORestore_Database
my_restore.Files = "[" & my_config.get_backupdrive_string & my_config.get_backupfolder_string & "tempcas.bak]"
my_restore.Database = my_config.get_Database()
my_restore.ReplaceDatabase = True
my_restore.SQLRestore(my_srv)
MsgBox("Database restored successfully.", MsgBoxStyle.Information)

'DELETE TEMP FILE FROM SPECIFIC DIRECTORY ON SERVER
my_file.Delete("\\" + my_config.get_ServerName + my_config.get_backupfolder_string + "tempcas.bak")Make sure you are not using the same database which using the DMO script.
Try using MASTER database instead.|||Hi, sorry, I'm not following you. Could you elaborate more?

Thanks!|||When you connect, what is the database you are connecting to ?|||I'm connecting to a database named CAS. Anyway the database name would vary on what I have set in an xml file. As you can see from my code, I am getting the database name from a class.

I've found that it is because my query analyser is open and pointed to CAS, this causes a process to be created. I can see this process with a status RUNNABLE, dbName CAS and program name SQL Query Analyser when I run sp_who2. When I change query analyzer to point to another database, this process will disappear and I would be able to restore my CAS database.

Is this what you were trying to tell me? Thanks for your replies.

I am now concerned about what would happen if there are multiple users using the application.