Uso del log para recuperar una base de datos de SQL Server

14/05/2012

Uno de los elementos más importantes y críticos de las bases de datos es el log. Tanto SQL Server, DB2, Oracle, mySQL utilizan el log constantemente como herramienta de recuperación. En SQL Server tiene 2 funciones:

– Almacenar el histórico de todas las transacciones de la base de datos, principalmente para que los cambios de dichas transacciones se hagan en memoria y no en disco y funcione mucho más rápido (hay un proceso posterior llamado “Ahead Logging” que se encarga de guardar las páginas de memoria “sucias”, osea las que han cambiado a través de una transacción a fichero de datos físico de la base de datos.

– Servir como recuperación de la base de datos hasta el momento justo en que se cayó la base de datos.

En este post, vamos a ver el segundo supuesto de cómo SQL Server usa el log para recuperar la base de datos.

Para que la recuperación sea efectiva, el fichero de log debe estar en un disco duro a parte del fichero de datos de la base de datos. Eso hace que si falla el disco de datos, el fichero de log aún sea accesible, además de mejorar el rendimiento I/O general de la base de datos ya que no compiten el log y el fichero de datos por el acceso al mismo disco.

Para saber cómo separar físicamente en otro disco duro el log del fichero de datos, podemos consultar estos dos post

https://miblogtecnico.wordpress.com/2011/11/18/filegroup-y-tablespaces-separacion-logica-de-la-base-de-datos/

https://miblogtecnico.wordpress.com/2011/11/18/mejorar-el-rendimiento-de-nuestra-base-de-datos/

Imaginemos que hemos hecho la copia de seguridad a las 3:00 de la mañana de nuestra base de datos con una sentencia similar a esta:

backup database contabilidad to disk = ‘c:\backup\contabilidad.bak’ with init

Esta sentencia nos permite hacer una copia de seguridad de la base de datos contabilidad al fichero contabilidad.bak. El parámetro with init, simplemente indica que sino existe el fichero físico, lo cree de nuevo.

Ahora imaginemos que la base de datos está activa al día siguiente, la gente trabajando y justo antes de finalizar la jornada laboral, a las 17:00 de la tarde,  el disco de datos del servidor falla y por tanto perdemos la base de datos. Vaya marrón….

Somos previsores, tenemos discos de repuestos y en media hora tenemos el servidor online otra vez, pero al arrancar el sql server, la base de datos de contabilidad no se pone en marcha… (lógico sino tenemos el fichero de datos).

Base de datos de SQL Server Inaccesible

Base de datos de SQL Server Inaccesible

Vemos que el fichero de datos se ha perdido con lo que no puede arrancar. Lo primero que pensamos es ir y restaurar la copia de seguridad de la noche anterior (la de las 3 de la mañana que tenemos automatizada). No sería una mala opción, pero pensemos que si hacemos eso, perdemos todo lo que hemos hecho durante ese día. Pensemos que se han metido ese día 1000 facturas… vaya marrón volverlas a meter todas…. Pensemos que hay entornos críticos que eso es inadmisible…

Una solución a este problema es utilizar el fichero de log. Si este fichero está en otro disco duro físico, entonces no habrá fallado y por tanto será accesible. Además este fichero, tendrá almacenados todo el histórico de todas las transacciones que se han hecho durante ese día, justo hasta el momento en que se cayó el sistema. Con SQL Server, podemos recuperar dicho log con esta sentencia:

backup log contabilidad to disk = ‘c:\backup\final.bak’ with init, no_truncate;

Fijémonos que aquí estamos haciendo copia del fichero log, no del de los datos y sobre todo, fijémonos en el parámetro no_truncate. Este parámetro le dice a SQL Server que no trunque el log y que por tanto copie la parte final del log desde la última copia de seguridad, osea desde las 3:00 de la mañana, hasta las 17:00 horas del día siguiente. Esto es lo que se denomina en SQL Server el “Tail log”, osea la parte final del log que contiene el histórico de transacciones desde la última copia de seguridad que se hizo (o desde la última copia de log si ha habido otras intermedias).

Una vez tenemos la parte final del log salvada, ya podemos hacer el restore de la base datos, osea la recuperación de la copia de la noche anterior con esta sentencia:

restore database contabilidad from disk = ‘c:\backup\contabilidad.bak’ with NORECOVERY;

Fijémonos que esto permitirá recuperar la base de datos hasta las 3 de la mañana que es cuando se hizo la última copia de seguridad. Fijémonos aún más importante el parámetro NORECOVERY. Este parámetro le dice a SQL Server que restaure los ficheros desde la copia de seguridad (el de datos y el log) pero que no haga la recuperación, osea que los deje tal y como están. De esta forma, le estamos diciendo a SQL Server que deje intacto el log, porque vamos a seguir restaurando parte de log, para volver hasta el momento en el que se cayó el sistema.

Base de datos de SQL Server en modo NO RECOVERY

Base de datos de SQL Server en modo NO RECOVERY

Finalmente nos faltará restaurar la parte final del log (Tail-log) que es la que contiene el histórico de transacciones desde las 3:00 de la mañana hasta las 17:00 horas que es cuando cayó el sistema. Usaremos una sentencia parecida a esta:

restore log contabilidad from disk = ‘c:\backup\final.bak’ with RECOVERY;

Fijémonos que en esta sentencia, estamos recuperando el log y no el fichero de datos y además al final le indicamos con el parámetro RECOVERY, que precisamente haga la recuperación. Osea que vuelva a rehacer todas las transacciones que tiene en el log (desde las 3:00 de la mañana hasta las 17:00 de la tarde) y que deje la base de datos online.

Base de datos de SQL Server recuperada y online

Base de datos de SQL Server recuperada y online

De esta forma, el marrón, se vuelve un marroncito pequeño, nadie pierde nada y volvemos a tener la base de datos online sin perder nada de información.

4 comentarios to “Uso del log para recuperar una base de datos de SQL Server”

  1. Robby Says:

    I’m impressed, I have to admit. Seldom do I encounter a blog
    that’s both equally educative and interesting, and without a doubt, you have hit the nail on the
    head. The problem is an issue that too few people are speaking intelligently about.
    Now i’m very happy I found this during my search for something relating to this.

  2. Wladimir Says:

    Tengo una consulta, en mi caso yo tengo los servidores virtualizados. Hago una copia de los servidores virtuales cada media noche. A parte de eso tengo activado Log shipping con un segundo servidor.
    Podría restaurar el servidor virtual desde el backup y luego usar los Log del segundo servidor donde se estaban copiando los cambios mediante log shipping?
    Saludos.


Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: