Question regarding getting exclusive access to SqlServer database for restoring

Question

Here's my problem: I have an application which uses sql server express 2005, and it is installed under default sql server express instance, SQLExpress Under this instance there are several other databases, used by other applications (web based or desktop) If I need to restore the database, sometimes it works, but sometimes it fails, telling that I need exclusive access to database to do this. I understand that, and of course when I try to restore, the application using the db is closed, but probably it was just closed minutes before so sql server still keeps some connections open, for caching purpose. And in this case I have two options:

• restart the sql server instance - that's easiest to do from user point of view(right click on server connection in Enterprise Manager and select restart (but worst since this stops all databases)

• Detach my db, attach again then restore (this works because detach have option to close all connections). But this is harder to do (takes more time) since I have to browse through the folders to find where db is located to attach back (I keep all db's in a custom location, not under default sql server database location)

My question is, is there any other easier way to do this?

Thanks

Answer 1

In SQL server Management Studio (not sure about Express, I'll check) you can use the Activity Monitor under Management to locate any connections still open to the database. Right-click the connection and choose 'Kill'

This way you can clear any outstanding connections.

[EDIT] I've checked a SQL server Express, and it also contains the Activity Monitor. Alternatively you can use a script to close all connections. See here for an example.