Los Books Online de SQL Server son una excelente referencia y uno de los primeros lugares donde solemos acudir a buscar información sobre SQL Server. Esta documentación en línea es la documentación oficial del producto y, por tanto, se centran en los aspectos «soportados» por el producto. A poco que seamos curiosos encontraremos que disponemos de una enorme cantidad de procedimientos de sistema en nuestras bases de datos que carecen de documentación en BOL. El motivo para carecer de documentación suele ser que no son funcionalidades previstas para ser utilizadas directamente por el usuario. Por otra parte el acceso a tablas de sistema se restringe para evitar que el temerario usuario pueda «romper» demasiadas cosas.

Personalmente considero que no deben añadirse limitaciones artificiales en este sentido y que el usuario tiene todo el derecho de tocar, romper o intentar reparar por si mismo lo que crea conveniente. De la misma forma comprendo que Microsoft requiera cierto control sobre lo que podemos cambiar en nuestro servidor de base de datos para ofrecer un soporte sobre el producto. Para el usuario disponer de una documentación más completa que incluya el acceso «a bajo nivel» proporcionará una mayor riqueza en sus conocimientos permitiendo administrar mejor su servidor y solucionar los problemas sin requerir pasar por el soporte técnico. ¿Será evitar este último punto el motivo real que justifica que parte de SQL Server esté sin documentar o limitado? 😉

Una vez expuesto todo esto, voy a centrarme en un escenario en el que según BOL poco podríamos hacer por recuperar nuestros datos. Este artículo va dedicado a todos aquellos que no se resignan nunca J ¡Va por vosotros!

Escenario

Disponemos de una base de datos distribuida en diferentes filegroups y sus ficheros respectivos. Debido a un problema de comunicación (una rotura de una fibra, controladora, etc.) alguno de nuestros ficheros deja de estar disponible de forma puntual. Siguiendo el procedimiento recomendado en estos casos procederemos a:

  • Realizar un backup de nuestra base de datos y del log. Debemos asegurarnos que tenemos la cadena completa de backups que nos permitan recuperar nuestro fichero de forma online.
  • Marcar el fichero de la base de datos afectado como OFFLINE para poder proceder a su recuperación mientras el resto de la base de datos sigue online.
  • Realizar un restore del fichero desde nuestro conjunto de backups en el orden adecuado.

Problema

Durante la aplicación de los pasos anteriores y tras haber marcado el fichero como offline nos encontramos con que el dispositivo de backup ha sufrido algún error y no podemos restaurar el fichero. Desgraciadamente la información es muy valiosa y aunque conseguimos recuperar el fichero perdido (sustitución de la fibra, conexión del disco a otra controladora, etc.) el fichero permanece en estado OFFLINE hasta que lo restauremos un backup. No existe forma de volver a poner el fichero en modo ONLINE ni de realizar un backup de la base de datos que incluya el fichero que sufrió el problema. Esto está explicado claramente en los BOL:

OFFLINE

Sets the file offline and makes all objects in the filegroup inaccessible.

Caution:
Use this option only when the file is corrupted and can be restored. A file set to OFFLINE can only be set online by restoring the file from backup. For more information about restoring a single file, see RESTORE (Transact-SQL).

 

Disponemos de un fichero que se encuentra en perfecto estado pero no nos es posible acceder a la información de ninguna forma. Intentamos a la desesperada adjuntar el fichero a otra base de datos, crear otra base de datos y sustituir el fichero original por la copia, poner la base de datos en modo emergencia, intentar reparar con CHECKDB tras sustituir el fichero forzosamente, añadirlo como otro filegroup, etc. Nada parece funcionar. Cualquiera que ha pasado por una situación similar comprenderá perfectamente el grado de impotencia que se experimenta en esos momentos.

Análisis y solución

Una vez que nos convencemos que el fichero del que disponemos es perfectamente válido y que el único problema es que no podemos volver a marcar el fichero como ONLINE para la base de datos buscamos la forma de cambiar el estado de forma forzosa ya que mediante el comando ALTER DATABASE no es posible cambiar a ONLINE un fichero. Afortunadamente la cabezonería consigue obrar milagros algunas veces J

¿Cómo podemos saber el estado de nuestros ficheros en nuestra base de datos? Tenemos una vista de sistema (sys.database_files) que nos ofrece dicha información. Si obtenemos la definición de dicha vista vemos que el valor del estado se calcula a partir de la columna filestate de sys.sysdbfiles. No existe documentación alguna en BOL sobre sys.sysdbfiles por lo que imaginamos que es algún objeto de sistema no documentado. Si intentamos lanzar una consulta directamente contra sys.sysdbfiles se nos dice que no existe tal objeto.

En SQL 2005 disponemos de una nueva funcionalidad denominada DAC (conexión dedicada de administración). Para conectarnos a dicha conexión especial de administración deberemos conectar al motor de la base de datos utilizando como nombre de servidor «ADMIN:» seguido por el nombre de la instancia. Por defecto esta funcionalidad no dispone de acceso remoto por lo que debemos conectar desde la propia máquina local bien mediante sqlcmd o mediante management studio. Si deseamos activar el acceso remoto podemos hacerlo activando la opción «remote admin connections» bien con el SAC (Surface Area Configurator) o con el siguiente script

sp_configure ‘remote admin connections’, 1;

GO

RECONFIGURE;

GO

 

En ocasiones podemos tener algún problema para conectar con la conexión DAC y conviene asegurarse de algunos puntos:

  • Asegurarnos que añadimos el flag –T 7806 a los parámetros de inicio del servidor
  • Conviene que tengamos nuestro SQL Browser en marcha si queremos que nos detecte el puerto dinámico que utiliza DAC para escuchar conexiones.
  • Podemos también indicar el puerto DAC explícitamente al conectar (aparece en el ERRORLOG como «Dedicated admin connection support was established for listening remotely on port XXXX.»)
  • Si tenemos algún problema al conectar por la resolución del nombre de la máquina, utilicemos la IP en vez del nombre de la máquina
  • No intentar conectar el object explorer con esta conexión. Únicamente podemos conectar bien vía sqlcmd o bien conectando directamente con SSMS al crear una nueva consulta contra el motor de base de datos.

Una vez que hemos conseguido conectar con el servidor mediante DAC intentamos lanzar de nuevo la consulta que nos estaba «prohibida» desde la conexión habitual vemos que si tenemos acceso a la tabla. ¡EUREKA! J Comparando los estados de los ficheros ONLINE con el fichero OFFLINE encontramos que el valor que debería tener el estado del fichero debería ser 0 para que fuese considerado online por SQL Server. Esta consulta la podemos lanzar bien desde la base de datos master, bien desde la base de datos en la que estamos teniendo el problema. La diferencia sería que veríamos bien el estado de todos los ficheros de la instancia o solo los de la base de datos a la que estemos conectados.

Ilusionados, procedemos pues a la actualización de dicho estado en la tabla de sistema desde la conexión DAC con un update sencillito:

use miBD — Cambiar a la base de datos que tiene el fichero online

 

update sys.sysdbfiles

set filestate=0

where dbid=0 and fileid=3 — Indicar aquí el valor correspondiente a nuestro fichero

 

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

L No podemos realizar el update pues la actualización de las tablas del sistema no está habilitada. Para habilitarla, en teoría, debemos activar otra opción con el siguiente script:

sp_configure ‘Allow updates’, 1;

GO

RECONFIGURE;

GO

Tras ejecutar este script vemos que seguimos obteniendo un mensaje de error:

Msg 5808, Level 16, State 1, Line 1

Ad hoc update to system catalogs is not supported.

 

Como podemos ver, este está siendo un camino difícil hasta la victoria. Seguimos investigando y encontramos que para ser capaces de realizar actualizaciones al catálogo, además de usar la DAC, necesitamos arrancar nuestra instancia en modo «single-user». Para ello debemos añadir el flag «-m» a los parámetros de arranque del servidor. Una vez hecho esto y reiniciado el servicio de nuevo podremos cambiar la opción «Allow updates» y hacer el update en nuestra BD:

sp_configure ‘Allow updates’, 1;

GO

RECONFIGURE;

GO

Una vez hecho esto comprobaremos que, mágicamente, aunque nos sigue apareciendo el mensaje de error al activar la opción ‘Allow updates’, la opción se activa «de verdad de la buena» y podemos lanzar el update que antes nos daba error:

update sys.sysdbfiles

set filestate=0

where dbid=0 and fileid=3 — Indicar aquí el valor correspondiente a nuestro fichero

Warning: System table ID 76 has been updated directly in database ID 9 and cache coherence may not have been maintained. SQL Server should be restarted.

 

(1 row(s) affected)

Como podemos ver, estamos haciendo algo a buen bajo nivel y debemos mantener la coherencia con la BD master realizando el update correspondiente en la base de datos master. Para ello haremos el siguiente update en master:

update sys.sysdbfiles

set filestate=0

where dbid=DB_ID(‘miBD’) and fileid=3 — Indicar aquí el valor correspondiente a nuestro fichero

Warning: System table ID 76 has been updated directly in database ID 1 and cache coherence may not have been maintained. SQL Server should be restarted.

 

(1 row(s) affected)

 

Una vez hecho todo esto, tras reiniciar el servicio de SQL Server, vemos como el fichero está ONLINE de nuevo y podemos acceder a los datos que ya casi dábamos por perdidos.

¡MISIÓN CUMPLIDA!

 

Rubén Garrigós