Automatización de Backups y Restores

Por Alberto López Grande

Descargar ejemplos de este artículo (12 KB)  .

Contenido

 Introducción
 La realización del backup
 La restauración
 La copia al servidor de destino
 La restauración
 La programación
 Otro uso: realización de backup bajo demanda
 Conclusión

Introducción

Este artículo expone un método sencillo para automatizar la realización de copias de seguridad y su restauración en un segundo servidor. Las aplicaciones que esta automatización puede tener van desde la mera realización de las copias y su verificación, hasta el mantenimiento de un servidor de hot StandBy mediante log shipping.

El método se basa en un conjunto de procedimientos almacenados que encapsulan cada problemática, como la realización del backup, la copia de un servidor a otro, la verificación de la existencia de dicho backup y de su disponibilidad en ese momento, y la restauración. Los procedimientos almacenados los podemos construir de forma de darles la versatilidad suficiente como para poder utilizarlos con otros muchos fines. Aquí se cubre una serie de características generales, pero pueden ser usados como un punto de partida para la realización de procedimientos más complejos. Estos procedimientos almacenados deben ser programados en trabajos para darle la periodicidad que sea necesaria.

 

La realización del backup

El primero de los procedimientos es el que realiza la copia. Lo que realiza es un “backup [database/log] [database_name] to disk=[ruta]”, en el que mediante los diferentes parámetros se definen las características del backup. Se usa el mismo procedimiento almacenado para la realización de los backups del log y los backups completos. No se definen backups diferenciales, aunque su inclusión sería bastante sencilla.

sp_AW_BK_Database.sql

La cabecera es la siguiente:

"CREATE procedure sp_AW_BK_Database
@pDatabaseName varchar(255),
@pBackUpPath varchar(255) = 'DEFAULT',
@pFullBackup bit,
@pShrinkLog int = 0, 
@pForSnapshot bit = 0,
@pTruncateLog bit = 1
as

Comúnmente, los backups son alojados en una misma ruta, lo que aquí viene definido por la ruta por defecto (@pBackUpPath). El nombre se construye a partir del nombre de la base de datos, al que se le añaden los prefijos y sufijos que se estime oportuno. En este caso se ha optado por usar un sufijo que identifique si el backup es completo o no, y que además incluya la hora en que se hizo dicho backup. En este caso, el backup del log de las 10 de la mañana para la base de datos Northwind se llamaría Northwind_LogBackup10.bak. Aunque el nombre es lo de menos, lo importante es saber que el backup se llamará de una determinada forma y estará en un determinado lugar, para que después sea restaurado de forma automática.

Este es el único paso del que debe ocuparse el servidor de origen, que por lo general será el servidor de producción.

 

La restauración

El resto de los pasos corren por cuenta del segundo servidor. Son 2 pasos, aunque cada uno de ellos utiliza procedimientos almacenados que encapsulan algunas de las funcionalidades que son necesarias para completar la restauración.

 

La copia al servidor de destino

El primero de los 2 pasos consiste en llevar el backup de un servidor a otro. Para ello se utiliza un procedimiento almacenado que lanza un comando copy con xp_cmdshell.

sp_AW_Din_CopyBk.sql

La cabecera es la siguiente:

CREATE proc sp_AW_Din_CopyBk (
@pDatabase_Name varchar(255),
@pFull_Backup bit,
@pSource_Path varchar(255) = 'DEFAULT',
@pDest_Path varchar(255) = 'DEFAULT',
@pForSnapshot bit = 0) as

Al igual que los backups, que se restauran en una ruta por defecto, el backup se copia y se pega en un directorio por defecto, aunque configurable dentro del propio procedimiento.

Antes de llevar a cabo la copia, se comprueba que el backup esté ya disponible, con un procedimiento almacenado diferente, sp_AW_Check_BK, que se basa en lanzar un restore headeronly. Si el backup ya está listo, se procede a la copia. Si no lo está aún, tanto si se debe a que el backup todavía no ha finalizado o bien si aún no ha comenzado, el procedimiento de copia se queda “esperando” a que el backup finalice.

sp_AW_Check_BK.sql

La cabecera es la siguiente:

CREATE proc sp_AW_Check_BK (@pBackup_File varchar(255), @pFinishDate datetime output) as

Se le pasa la ruta completa y se recoge la fecha y la hora en que terminó y el backup. Esa fecha se compara con la actual, para asegurarnos de que el backup está hecho, que se hizo a una determinada hora (es decir, que no es el de ayer) y que, al menos una sentencia restore headeronly funciona correctamente, con lo que a priori el backup es bueno.

 

La restauración

La restauración es la operación que más versatilidad debe ofrecernos, ya que es la que más opciones nos presenta. Además de la ruta y la base de datos que se debe restaurar, contamos con la posibilidad de que la base de datos sea restaurada con un nombre diferente a la base de datos de origen. También es posible configurar el modo en que dejamos la base de datos, pudiendo optar por dejarla en modo “recovery”, “norecovery” o “standby”, por si se considerara necesario dejar la base de datos en modo “sólo lectura”, permitiendo a la vez que la base de datos pueda recibir más backups del log.

Dentro del procedimiento almacenado, se comprueba que el backup esté disponible y en la ubicación deseada, recurriendo nuevamente al procedimiento almacenado sp_AW_CheckBK. Después se realiza la restauración.

sp_AW_RestoreDatabaseFromBackup.sql

Los parámetros de este procedimiento:

CREATE procedure dbo.sp_AW_RestoreDatabaseFromBackup
@pDatabase_Name varchar(255),
@pBackup_Path varchar (255) = 'DEFAULT',
@pDB_Data_Path varchar(255) = 'DEFAULT',
@pDB_Log_Path varchar (255) = 'DEFAULT',
@pFullRestore bit,
@pRecovery varchar(30) = 'RECOVERY',
@pProd_DBName varchar(255) = @pDatabase_Name,
@pWithFixingTask bit = 0,
@pOnErrorFullRestore bit = 1,
@pForSnapshot bit = 0,
@pRestoreFromTime datetime = null
As

Dado que es probable que la restauración no pueda realizarse, el procedimiento mismo posee cierta tolerancia a fallos. En caso de error, cabe la posibilidad de configurar el procedimiento almacenado para que, automáticamente, localice el último backup completo en el directorio donde se alojan todos los backups, se restaure éste y después todos los backups del log posteriores de forma consecutiva. Esta parte la realiza un procedimiento almacenado diferente, sp_AW_SerialRestore. Así podemos utilizar este procedimiento para realizar una restauración hasta un punto determinado, utilizando la hora inicial y final que ofrece este procedimiento almacenado como parámetros.

sp_AW_SerialRestore.sql

 

La programación

Este conjunto de procedimientos almacenados nos pueden facilitar la realización de copias de seguridad, pero combinados todos ellos en 2 jobs puede conseguirse el mantenimiento de un entorno de hot StandBy, por ejemplo. Así, tendríamos:

• Un job en el servidor de origen que realizará un backup completo diario y que se ejecutará cada noche a las 12h05’.

• Un job en el servidor de origen que realizará un backup del log cada hora, entre las 6h00’ y las 23h00’.

• Un job en el servidor de destino que primero copie el backup completo y luego lo restaure y que se ejecutará cada noche a las 12h40’.

• Un job en el servidor de destino que primero copie el backup del log y luego lo restaure, entre las 6h10’ y las 23h10’.

Si es necesario respaldar más de una base de datos en el servidor, cada job puede llevar más pasos en los que incluir las copias de más bases de datos o se pueden también crear más trabajos que se encarguen de realizar.

Jobs script.sql

Aunque las necesidades del negocio van a marcar la periodicidad y el tipo de la realización de backups, aquí se propone independizar la realización de los backups completos de la realización de backups del log. Así, por un lado tendremos un job para realizar el backup del log cada hora y otro job que realizará el backup completo con una periodicidad diaria. Paralelamente, se programarán 2 jobs en el servidor de destino que nos realizarán el copiado y la restauración. Conseguido. Tenemos respaldada nuestra base, ya que tenemos backup y los tenemos comprobados.

 

Otro uso: realización de backup bajo demanda

Es posible que en un determinado momento sea necesario que el servidor en el que se mantiene el entorno de Hot StandBy deba asumir la producción (porque en producción hay que instalar un service pack, por ejemplo). Para ello los procedimientos almacenados poseen un parámetro que permite realizar un backup con sufijo “99”. Igualmente, los procesos de restauración nos ofrecen la posibilidad de tomar ese backup y restaurarlo, dejando en línea la base de datos. Lo aconsejable en estos casos es combinarlo en un job que deshabilite los otros jobs de mantenimiento de las restauraciones.

 

Conclusión

La automatización de las copias de seguridad y verificación de las mismas es una más dentro del conjunto de tareas implicadas en la alta disponibilidad. Contar con un conjunto de procedimientos almacenados que nos faciliten esta tarea no requiere de una gran complejidad. Programados dentro de jobs, nos facilitan además el seguimiento y la subsanación de errores.

Alberto López Grande posee 6 años de experiencia trabajando con diversos ámbitos de SQL Server en Madrid. Cuenta con la certificación MCP de SQL Server 2000, tanto en administración como en diseño.