I want to programmatically restore a database from backup. But in order to do so, one must first break all existing connections to that database. The following queries list NEARLY all connections:
select spid, rtrim(loginame) from sys.sysprocesses where db_name(dbid)='MyDatabaseName'
Unfortunately, those queries do not always list every last connection. Once in a while, those queries miss a connection. After running one of the above queries, it is easy enough to kill all reported user connections. Unfortunately, this process does not always report and kill every connection. I need a query to show every last connection to a database so that I can kill those connections and then restore that database from backup.
Oddly enough, the following commands do not assist whatsoever:
alter database MyDatabaseName set offline with rollback immediate
alter database MyDatabaseName set online
alter database MyDatabaseName set single_user with rollback immediate