How to close all open "handles" to a SQL Server 2005 express DB?

Hello all!
I'm trying to create an application which makes a copy of the database file it is using on application start and can restore it at any time, if needed (little project about SQL injection, so I need the copy ;-) ). This works fine, but as soon as I make my first connection to the db (connection_object.open()) and close it again I can not access the file anymore, because it is "in use by another process". How can I release all handles from the database, so that I can replace the DB file with it's backup copy?

Thank you

KoljaYou have to kill connection instead of just closing.

Shot two bulets in his head and call GC to finish him.

|||Ahm...
cal me a noob, but I disposed it and set the variable to null... doesn't work
What do you mean : Call GC?

How to clear all database connection using SQLDMO?

Hello guys! I'm using SQLDMO to restore a database backup. The problem is, the program cannot restore the backup if there are active connections using the database. Is there are way to clear all database connections using SQLDMO (or other libraries)? Thanks in advance

You could create a storedproc in master that kills any existing spids per db, then run that proc from DMO as part of the restore job.. something like this

CREATE procedure sp_KillOldspids

@.dbName varchar (30) = NULL
AS

set nocount on

declare @.currentspid int,@.cmdstring varchar(30)

dECLARE OPENSPIDS CURSOR FOR SELECT [SPID] from [MASTER].[DBO].[SYSPROCESSES] WHERE DBID = db_id (@.dbName)
OPEN OPENSPIDS

FETCH NEXT FROM OPENSPIDS INTO @.CURRENTSPID

WHILE @.@.FETCH_STATUS = 0
BEGIN

SELECT @.cmdString = 'kill ' + convert(varchar(10), @.CURRENTspid)
IF @.@.SPID <> @.CURRENTSPID
BEGIN
EXEC (@.cmdString)
END
FETCH NEXT FROM OPENSPIDS INTO @.CURRENTSPID

END

CLOSE CURRENTSPIDS
DEALLOCATE CURRENTSPIDS

hopes this helps.