sybase ase 11.9.2 load database

[Guest_imported]

I'm trying to load a database from dump file (on disk)
using the following command

load database xyz from /path/abc.dmp

(where xyz is the name of the database)

this fails with error " exclusive access is needed to the database". so my guess is i need to kick off all users connected to the database.

how can i kick off all users unconditionally?

 

[qyllr]

hello,

try the following:

1. run - sp_who and identify all the users that are logged in...
2. bounce the server to disconnect the users or kill the connections based on the spid's reported by the sp_who...
shutdown
go
or
kill {spid}
go
3. lock the logins by running the following:
sp_locklogin loginName, "lock"
go
4. perform your load...
5. unlock the logins by running the following:
sp_locklogin loginName, "unlock"

 

[JMCraig]

Actually, if you can get to the DB before anyone else is logged in and start the load, you don't need to lock the logins: once the load is in process, no one will be able to get to the database until the load is finished and you've executed the

online <dbname>

command. As an SA, I prefer my users to get a message saying the DB is in the process of loading or is off line rather than that their logins have been locked--and if you have a large number of logins, that can be a real pain.

One thing you can consider doing is using sp_who to determine what ID's are part of a basic system (the processes that are just part of Sybase's normal operation) and then you can create a stored procedure that will kill all others. The difficulty with this is that Sybase doesn't provide a simple way to find out a process's own spid (a real limitation, I think) so you can end up killing your own spid if you can't use the login to distinguish it--but then you shouldn't have other folks logging in as SA anyhow). So, if you have only that rudementary level of security (and I only mention it because I've seen a startling number of installations running with every user logging in as SA--sheesh!), you can create a proc that looks at master.dbo.sysprocesses and systematically kills any process that doesn't belong to the SA login.

HTH,

JMC J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net