Restore the database

• maya-210951

  SSC Enthusiast

  Points: 113

  More actions

  February 25, 2005 at 1:13 am

  #110106

  Hi,

  I would like to restore the database from the Enterprise Manager (by clicking the database name, and then take it off line and restoring).

  Before restoring i would like to disconnect all existing connections.

   

  I though about using:

   

  alter database <dbname>

  set single_user

  with rollback immediate

   

  But this command will kill all connections to the <dbname> database except for the connection that I am currently having to the database.

  And I will not be able to from the Enterprise Manager.

   

  I also thought about killing all the connection by executing KILL command but the problem is that when there are large number of clients connected some may connect back while killing all the other.

   

   

  I would like to know a way to kill all connection beside the enterprise manager.

   

  Thanks,

  Maya.

   

• N J

  Right there with Babe

  Points: 732

  More actions

  February 25, 2005 at 8:01 am

  #544164

  Maya,

  For killing the connections u need to run the script using the sysprocesses system table for active users and moving the databse to single-user mode.

  You can not use EM and QA in single user mode.

  For new connections u can pause the sql server through OS service manager for the duration kill script runs. But this may affect the working of other databases and users.

  IF everything goes fine database can be restored through Query Analyzer.

  anie