January 14, 2002 at 2:34 am
Hi Andy
I have just read your article about restoring a database using sql-dmo
and I have some extra questions.
I have just started to learn about using sql-dmo with visual basic. I am
designing a sql-dmo application to backup and restore a database.
The sample code in this article works fines. The problems is if the
databse is in use by users then it can not be restored. So-
1. How to I find out programmatically using sql-dmo (if possible) the
number of user or processes using the database (if any).
2. How to I sent them a message to notify them that the database is
going to be restored.
3. How to I kill the users or processes using the database and make it
single user only so it can be restored and then turn it back into a
multi user database.
many thanks in anticipation
Chan
January 14, 2002 at 5:34 am
Thanks for posting! Have to my homework on this one, will get you an answer later today...unless one of our readers beats me to it:-)
Andy
January 14, 2002 at 5:42 pm
I dont see (though that doesnt mean there isn't) a way to determine the connected users via DMO directly. You can however submit a query using the server or db object execute method - just send select * from sysprocesses where db_name(dbid)='your db'. That will return a query results object that you can work through. For not much more effort you can connect using ADO and get the results back as a true recordset.
For notifications your choices are email and netsend. Email is ok if you can determine the email address from the connection information. Net send is immediate and works well.
To remove users, normally you will change the db to DBO only, then cursor through the list of connections for that db and issue the kill for each spid.
As you can tell we're not getting much of out DMO here. I'd say either use ADO or just build the identify/notify/kill functionality into a stored procedure. Both are valid approaches though I think having the proc on the server might come in handy in lots of cases. Might want to browse our script library or post a question in the TSQL discussion area.
In either case I'd recommend that you keep the restore functionality separate from the notify/kill part. You might also want to look at persisting who you notified to exit so you can send them a message telling them they can resume working.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply