Hacer backups programados y con retención

Una de las cosas que no están disponibles con SQL Server es el Agente SQL. El agente nos permite programar tareas que se ejecutarán sobre las bases de datos cuando nosotros queramos. Esta carencia dificulta un poco, por ejemplo, la realización de copias de seguridad, especialmente si queremos mantener un periodo de retención concreto (por ejemplo, las copias de los últimos 7 días).

Por suerte esta carencia en concreto es muy fácil de solucionar, y en este artículo voy a explicar cómo lograrlo de manera sencilla.

Lo primero que tenemos que saber es que todas las ediciones de SQL Server incluyen una utilidad de línea de comandos que nos permite ejecutar instrucciones T-SQL arbitrarias contra cualquier base de datos. Se trata de SQLCMD.exe, generalmente ubicada en esta ruta:

"C:Program FilesMicrosoft SQL Server110ToolsBinnSQLCMD.EXE"

en el caso de SQL Server 2012 Express.

Esta utilidad tiene muchos parámetros que nos permiten controlar su forma de trabajar. Dos que nos interesan especialmente son:

• -S: nos permite especificar contra qué servidor/instancia se ejecutarán las sentencias T-SQL.
• -i: permite especificar una ruta a un archivo (normalmente con extensión .sql) que contiene las instrucciones T-SQL que queremos ejecutar contra el servidor. Así podemos incluir scripts más complejos que una simple línea.

Sabiendo la existencia de esta herramienta, conseguir backups gracias a ella es muy sencillo.

1. Construir las instrucciones T-SQL base para hacer el backup

Lo primero es conseguir las comandos T-SQl para hacer un backup. Lo más sencillo es usar las herramientas integradas en el Microsoft SQL Server Management Studio (MSSMS). Ábrelo, busca la base de datos que te interesa copiar en el explorador de objetos y pulsa el botón derecho del ratón sobre ella. En el menú contextual elige la opción de "Tareas·backup…":

Esto abre una nueva ventana desde la que podemos definir cómo queremos realizar el backup:

Desde esta ventana elegimos la base de datos a copiar y la ruta en la que queremos guardar dicha copia de seguridad (normalmente le damos como extensión al archivo .bak, pero puede ser cualquiera o incluso no tener extensión).

Además si pulsamos en la página "Opciones" en el lateral podemos configurar algunas cosas más, como por ejemplo (muy recomendable) que se verifique el backup al terminar de hacerlo:

OJO: la edición Express no soporta la compresión de los backups, así que si seleccionamos esta opción en la lista desplegable de la parte inferior de la figura anterior, se producirá un error al realizar el backup.

Una vez que tengamos seleccionadas todas las opciones que necesitemos, podemos obtener el código necesario para realizar el backup usando el botón "Script" de la parte superior de la ventana anterior. Por defecto nos copiará el código generado a una ventana del MSSMS, así:

Con esto obtendríamos una base de datos que se sobrescribiría en cada nueva copia, Lo interesante de las copias de seguridad es tener copias con una retención de varios días, para poder comprobar datos anteriores o restaurar los datos a un estado anterior

2. Retocar el script para darle una semana de retención

Supongamos que queremos hacer una copia de seguridad diaria y que queremos mantener las copias durante 7 días, de modo que podamos recuperar los datos desde cualquier copia de seguridad de la última semana. Para ello vamos a retocar el script anterior de modo que cada día le cambie el nombre al archivo de copia de seguridad. Para ello vamos a declarar una variable que servirá para guardar la ruta y el nombre del archivo de copia de seguridad, cambiándolo en función, en este caso, del día de la semana en el que nos encontremos. En este caso sería así:

DECLARE @dest nvarchar(255)

SET @dest = 'C:BackupsBBDDSELF_' + CAST(DATEPART(weekday, GETDATE()) AS nvarchar(1)) + '.bak'

La función DATEPART con el valor weekday para el primer parámetro nos devuelve un número para cada día de la semana, empezando por el domingo (un 1)  hasta el sábado (un 7). Como le pasamos la fecha actual (GETDATE) como segundo parámetro lo que obtendremos en la variable @dest es cada día un nombre diferente para la base de datos, añadiéndole el número de día de la semana para obtener nombres estilo: SELF_1.bak, SELF_2.bak, SELF_3.bak y así sucesivamente.

En el script generado por el MSSMS bastará ahora por sustituir la ruta por el nombre de esta variable y ya lo tendremos listo (ojo: hay que susituirlo en dos sitios: en el backup y en la verificación del backup en la parte inferior).

Dado que en caso de que un archivo exista de backup se sobrescribirá, en la práctica con este script lo que conseguimos es que siempre haya 7 copias como máximo en el histórico.

3. Crear un bat para realizar el backup

Ahora que ya tenemos el código necesario para crear las copias de seguridad lo que debemos hacer es crear un archivo .bat que nos permita ejecutar este código T-SQL cuando queramos. Para ello usaremos SQLCMD.EXE, escribiendo esta instrucción:

"C:Program FilesMicrosoft SQL Server110ToolsBinnSQLCMD.EXE" -S SERVIDORINSTANCIA -i "C:BackupsBBDDBackupSELF.sql" >> log.txt

Debemos sustituir SERVIDORINSTANCIA por el nombre de nuestro servidor y la instancia de SQL Server sobre la que queremos trabajar. En el parámetro -i debemos indicar la ruta al archivo .sql con las instrucciones para la copia de seguridad que acabamos de crear.

La última instrucción ">> log.txt" nos permite guardar el resultado de la ejecución en un archivo de texto que podemos consultar para ver cuándo se ha realizado cada copia, cuánto ha tardado y cualquier otro mensaje que se derive de la ejecución del script. E spor eso que me gusta colocarle al principio del script una instrucción más como esta:

PRINT CAST(GETDATE() AS nvarchar) + ' - COPIA DE SEGURIDAD INICIADA AL ARCHIVO: ' + @dest

De este modo aparecerá en el archivo Log.txt un mensaje al principio de cada copia de seguridad indicando la fecha de creación y el nombre del archivo. Podemos incluir del mismo modo cualquier otra información que consideremos relevante.

4. Programar la tarea

Ahora que ya tenemos un script para hacer la copia de seguridad, y además hemos creado un .bat para ejecutarlo, lo único que nos falta es crear una tarea programada para poder lanzarla con la periodicidad que nos convenga (en principio cada día).

Para ello abrimos el administrador de tareas programadas del sistema y creamos una nueva tarea. Lo único que tendremos que hacer es indicar que queremos ejecutar el archivo .bat del paso anterior así como a qué hora del día lo vamos a hacer:

Con esto habremos conseguido que todos los días a las 2:00 de la mañana se realice una copia de seguridad de la base de datos, con una retención de 7 días:

Cada uno de esos archivos se corresponde con la copia de seguridad del domingo (1), lunes (2), martes 83), etc…

Si quisiésemos un periodo de retención de un mes, por ejemplo, sería tan fácil como cambiar el parámetro de DATEPART por "day" de modo que se pusiera el número de día del mes. Podemos jugar con los distintos valores del primer parámetro de DATEPART para conseguir otros periodos, como por ejemplo, si hacemos más de una copia al día, añadirle la hora de modo que tengamos más de un archivo diario.

¡Espero que te resulte útil!