sql-server - unico - sql server change single user mode to multi user mode

¿Cómo obtener acceso exclusivo a la base de datos de SQL Server 2005 para restaurar?

Question

Cada vez que restauro una copia de seguridad de mi base de datos en SQL Server, me aparece el siguiente error:

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Por lo general, para evitar esto, simplemente reinicio el servidor. Esto estuvo bien cuando estábamos desarrollando nuestra instancia local en nuestras máquinas de desarrollo. Pero tenemos algunos programadores que necesitan acceder a la base de datos, y la logística de hacer que todos escriban sus cambios y colocarlos en Subversion se estaba convirtiendo en una pesadilla. A pesar de que nuestra solución simple era ponerlo en un servidor compartido en la oficina y hacer una copia de seguridad del servidor ocasionalmente en caso de que alguien estropeara los datos.

Bueno, arruiné los datos y necesitaba restaurar. Lamentablemente, tengo otro compañero de trabajo en la oficina que está trabajando en otro proyecto y está utilizando el mismo servidor de base de datos para el desarrollo. Para ser amable, me gustaría restaurar sin reiniciar SQL Server y posiblemente interrumpir su trabajo.

¿Hay alguna forma de script en T-SQL para poder tener acceso exclusivo o eliminar todas las conexiones?

Answer 1

@mattlant - eso es lo que estaba buscando. Lo traigo aquí, así que está en el hilo.

Use Master
Go

Declare @dbname sysname

Set @dbname = ''name of database you want to drop connections from''

Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
        Execute (''Kill '' + @spid)
        Select @spid = min(spid) from master.dbo.sysprocesses
        where dbid = db_id(@dbname) and spid > @spid
End

Answer 2

Hasta ahora esto funcionó para mí. Hice clic derecho en la base de datos> Tareas> Separar ...

Apareció una pantalla que le permite ver todas las conexiones activas. Luego puede pasar y desconectar cada conexión. Cuando tocas bien, has separado la base de datos y necesitas Adjuntar la base de datos. Haga clic derecho en Bases de datos y elija adjuntar, elija su archivo mdf y se adjunta el archivo db. En este punto, debe tener acceso exclusivo para restaurar.

Nota: Probé esto conectándome a una de sus bases de datos desde mi máquina local y desde el servidor eliminé las conexiones a mi base de datos y no perdí mi conexión a su base de datos.

Answer 3

Encuentro esto mucho más rápido y generalmente mejor que desconectarme. Lea sobre esto en MSDN para que comprenda las advertencias. Si usa estadísticas aysnc, también debe desactivarlas.

-- set single user, terminate connections
ALTER DATABASE [target] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE ...
ALTER DATABASE [target] SET MULTI_USER

El "con reversión inmediata" es la cláusula esencial de "terminación". Dejarlo afuera espera por siempre. Una versión más agradable de lo anterior da a los usuarios transacciones durante unos segundos para finalizar.

ALTER DATABASE [target] SET SINGLE_USER WITH ROLLBACK AFTER 5

Sin conexión es una buena idea si desea copiar los archivos de la base de datos, un escenario que puede ser útil en las ediciones de escritorio de SQL. Demasiado pesado para este escenario. Si no está conectado, sería preferible. SQL se está alejando de sp_dboption .

ALTER DATABASE [target] SET OFFLINE WITH ROLLBACK AFTER 5

Answer 4

Bueno, puedes matar procesos SQL y sesiones con KILL .

Pero si solo sueltas todas sus conexiones actuales, ¿no las volverá a abrir?

Probablemente solo tengas que decirle que vas a restaurar desde una copia de seguridad, así que deja de conectarse un poco.

Answer 5

Primero, no puede restaurar una base de datos a menos que sea la única persona actualmente conectada y tenga derechos de administrador. Primero debe decirle a su compañero de trabajo que necesita restaurar y pedirle que se asegure de escribir en un script cualquier cambio que pueda no estar en los medios de respaldo. Esto es solo cortés y evita que los compañeros de trabajo te maten.

A continuación, establece la base de datos en modo de usuario único. Puede buscar cómo hacerlo en Libros en línea. Esto evita que alguien más se conecte mientras lo hace y le da la oportunidad de eliminar las conexiones existentes. Es importante ir al modo de usuario único, porque nadie más debería estar haciendo nada en la base de datos mientras restaura.

Luego ejecuta el proceso de restauración.

Answer 6

Sugeriría hablar con su compañero de trabajo y pedirle que se vaya de la base de datos. (Y avísele sobre el problema, porque podría perder los cambios que haya hecho cuando restaure).

Eso es mucho mejor que abandonar sus conexiones o establecer un acceso exclusivo que podría causarle algunos inconvenientes.

Answer 7

Puede forzar la base de datos fuera de línea y desconectar conexiones con:

EXEC sp_dboption N''yourDatabase'', N''offline'', N''true''

O tu puedes

ALTER DATABASE [yourDatabase] SET OFFLINE WITH
ROLLBACK AFTER 60 SECONDS

Rollback especifica si algo se está ejecutando. Después de ese período, se revertirán. Por lo tanto, proporciona cierta protección.

Lo siento, no estaba pensando / leyendo bien. Podrías regresar y hacer una copia de seguridad. También hubo una publicación en en un fragmento de T-SQL para soltar todas las conexiones en lugar de binging offline primero: Características ocultas de SQL Server