Crear copias de seguridad diarias de la base de datos con nombres únicos en SQL Server

Introducción

Al trabajar con un gran número de bases de datos en varios servidores SQL, la creación de un plan de recuperación ante desastres imbatible puede ser un reto. Estrategias bien organizadas de copia de seguridad y restauración definitivamente ayudarán con esto. Con el fin de implementar estas estrategias con éxito en un entorno más amplio, establecer copias de seguridad y procesos de restauración automatizados es una necesidad. Algunos administradores de bases de datos utilizan los scripts de lotes o de PowerShell para la automatización, mientras que otros prefieren utilizar diversas soluciones de terceros. En ambos casos, es necesario dar formato a los nombres de las copias de seguridad de la base de datos correctamente. Los nombres de copia de seguridad correctamente formateados facilitan mucho el trabajo de organización y mantenimiento de los conjuntos de copias de seguridad. Los archivos de copia de seguridad antiguos suelen ser obsoletos y se pueden identificar y eliminar fácilmente de la unidad de forma manual o mediante un script.

Dependiendo de los requisitos del usuario, los nombres de respaldo pueden contener información como: nombre del servidor, nombre de la instancia de SQL Server, nombre de la base de datos, tipo de copia de seguridad, fecha y hora. Los datos se pueden escribir manualmente para cada copia de seguridad creada, pero hacerlo para las múltiples bases de datos en los servidores múltiples sería una tarea imposible. Hay varias otras opciones disponibles para crear copias de seguridad diarias de la base de datos con los parámetros mencionados:

Crear y usar el cronograma del agente de SQL Server
Crear y utilizar el plan de mantenimiento de SQL Server

Crear el cronograma del agente de SQL Server

Para utilizar esta opción, es necesario que el servicio Agente de SQL Server se instale y se inicie. Esta opción requeriría una comprensión básica de las secuencias de comandos dinámicas de T-SQL y funciones de fecha y hora, pero automatiza el proceso por completo y ofrece muchas posibilidades para la personalización del nombre de la copia de seguridad. Para crear el cronograma del Agente de SQL Server, realice los siguientes pasos:

Asegúrese de que el servicio Agente de SQL Server se esté ejecutando: abra el Object Explorer y compruebe el icono del Agente de SQL Server. Si aparece el mensaje «Agent XPs disabled», vaya a Panel de Control\Sistema y Seguridad\Herramientas Administrativas\ y ejecute el acceso directo de Servicios. Busque el servicio Agente de SQL Server para la instancia respectiva, haga clic con el botón derecho en él y elija Start. Si ya se está ejecutando el servicio del Agente de SQL Server, salte este paso.
Expanda el nodo SQL Server Agent, haga clic con el botón derecho en Jobs y seleccione la opción New Job.
Se abrirá la ventana New job. En la pestaña General, especifique el nombre y el propietario del trabajo. De forma opcional también puede proporcionar la categoría y la descripción para el trabajo.
En la pestaña Steps, seleccione el botón New…para abrir la ventana New Job Step. Escriba el nombre del paso y elija la base de datos que sería el objetivo del paso. Es común tener como objetivo a la base de datos master, ya que esta configuración permite la modificación de cualquier base de datos en el servidor. También es necesario proporcionar el script de T-SQL que se ejecutaría en el primer paso del trabajo. Dependiendo de las necesidades, utilice uno de los scripts del paso siguiente.
Se proporcionarán dos scripts en este paso. El primer script realiza la copia de seguridad de una base de datos única y específica, y el segundo realiza una copia de seguridad de todas las bases de datos que no son del sistema en el servidor. Ambos scripts utilizan varias variables tales como: año, mes, fecha, hora, minuto y segundo.

Copia de seguridad de la base de datos específica
```
--Script 1: Backup specific database

-- 1. Variable declaration

DECLARE @path VARCHAR(500)
DECLARE @name VARCHAR(500)
DECLARE @pathwithname VARCHAR(500)
DECLARE @time DATETIME
DECLARE @year VARCHAR(4)
DECLARE @month VARCHAR(2)
DECLARE @day VARCHAR(2)
DECLARE @hour VARCHAR(2)
DECLARE @minute VARCHAR(2)
DECLARE @second VARCHAR(2)

-- 2. Setting the backup path

SET @path = 'E:\Backup\'

-- 3. Getting the time values

SELECT @time   = GETDATE()
SELECT @year   = (SELECT CONVERT(VARCHAR(4), DATEPART(yy, @time)))
SELECT @month  = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mm,@time),'00')))
SELECT @day    = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(dd,@time),'00')))
SELECT @hour   = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(hh,@time),'00')))
SELECT @minute = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mi,@time),'00')))
SELECT @second = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(ss,@time),'00')))

-- 4. Defining the filename format

SELECT @name ='TestDatabase' + '_' + @year + @month + @day + @hour + @minute + @second

SET @pathwithname = @path + @namE + '.bak'

--5. Executing the backup command

BACKUP DATABASE [TestDatabase] 
TO DISK = @pathwithname WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10
```
En la primera parte de la consulta, se declaran todas las variables necesarias. No es necesario utilizar todas las variables indicadas, sólo las que deben incluirse en el nombre de archivo para la copia de seguridad. Elimine las variables que no necesiten ser utilizadas en el nombre de archivo, o simplemente solicite que no aparezcan agregando dos guiones (-) delante del comando DECLARE.

Establezca la ruta de la copia de seguridad en la segunda parte del script. Utilice cualquier ruta de copia de seguridad deseada en lugar de «E:\Backup\».

En la tercera parte, los valores específicos se asignan a cada variable utilizando las funciones GETDATE, CONVERT, FORMAT y DATEPART. La función GETDATE recupera la fecha y la hora actuales y la asigna a la variable @time. La función DATEPART extrae el valor específico (año, mes, fecha, hora, minuto y segundo) para cada variable. La función FORMAT formatea los valores de la función DATEPART a cadenas de dos dígitos. De esta manera, los meses, las fechas, las horas, los minutos y los segundos de un solo dígito obtienen un 0 adicional delante de ellos. Finalmente, la función CONVERT convierte todos los resultados en cadenas VARCHAR.

La cuarta parte del script es crucial. El formato de salida del nombre de archivo se define en este paso. La variable @name representa el nombre del archivo. Los elementos en el lado derecho de la ecuación son las cadenas que están concatenadas en el orden presentado. La primera cadena ‘TestDatabase’ es el nombre de la base de datos de respaldo en este caso, y no necesita coincidir con el nombre de la base de datos original. Cualquier otra cadena personalizada puede agregarse al nombre de archivo de la copia de seguridad en cualquier posición para hacerlo más descriptivo.

Si es necesario cambiar o modificar el nombre del archivo de la copia de seguridad, sólo cambie la secuencia de las variables en este paso según sus necesidades.

La quinta parte del script es el comando de copia de seguridad. Introduzca el nombre de la base de datos a la cual se le debe realizar una copia de seguridad en lugar de [TestDatabase].

Copia de seguridad de todas las bases de datos que no son del sistema

--Script 2: Backup all non-system databases

--1. Variable declaration

DECLARE @path VARCHAR(500)
DECLARE @name VARCHAR(500)
DECLARE @filename VARCHAR(256)
DECLARE @time DATETIME
DECLARE @year VARCHAR(4)
DECLARE @month VARCHAR(2)
DECLARE @day VARCHAR(2)
DECLARE @hour VARCHAR(2)
DECLARE @minute VARCHAR(2)
DECLARE @second VARCHAR(2)
 
-- 2. Setting the backup path

SET @path = 'E:\Backup\'  

 -- 3. Getting the time values

SELECT @time = GETDATE()
SELECT @year   = (SELECT CONVERT(VARCHAR(4), DATEPART(yy, @time)))
SELECT @month  = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mm,@time),'00')))
SELECT @day    = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(dd,@time),'00')))
SELECT @hour   = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(hh,@time),'00')))
SELECT @minute = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mi,@time),'00')))
SELECT @second = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(ss,@time),'00')))

-- 4. Defining cursor operations
 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- system databases are excluded

--5. Initializing cursor operations

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN

-- 6. Defining the filename format
   
       SET @fileName = @path + @name + '_' + @year + @month + @day + @hour + @minute + @second + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

 
       FETCH NEXT FROM db_cursor INTO @name   
END   
CLOSE db_cursor   
DEALLOCATE db_cursor

En este script, los tres primeros pasos son idénticos al script anterior: las mismas variables son declaradas, y el mismo método se utiliza para asignar valores a las variables. A partir del paso 4, la operación del cursor se utiliza para buscar automáticamente el nombre de cada base de datos y para respaldarlas una por una. El formato final del nombre de archivo de la copia de seguridad se realiza en la parte seis, de la misma manera que en el script anterior.

Al definir el primer paso de trabajo, es posible establecer los pasos de trabajo adicionales, si es necesario. Esto podría ser el caso si se utiliza el script del paso 5.1 y el primer paso de trabajo realiza la copia de seguridad de una sola base de datos. Para realizar una copia de seguridad de varias bases de datos específicas, es necesario definir varios pasos de trabajo. Para ello, repita los pasos 4 y 5 hasta que se enumeren todos los pasos necesarios.
Para establecer el cronograma para los pasos de trabajo definidos, seleccione la ficha Schedules y haga clic en el botón… New.
Establezca los parámetros preferidos en la ventana New Job Schedule. Sólo es necesario establecer el nombre y la frecuencia de la programación, pero también se pueden especificar muchos otros parámetros. Compruebe la descripción en el resumen en cualquier momento para ver si la configuración de cronograma coincide con la programación de copia de seguridad prevista. Haga clic en OK cuando haya terminado.
De forma opcional, establezca las alertas y la notificación por correo electrónico para el trabajo en las pestañas respectivas de la ventana New Job y haga clic en OK cuando haya finalizado. Se crea el agente de nuevo trabajo y se deben ejecutar todos los pasos de trabajo definidos siguiendo el cronograma del paso anterior.
Es una buena práctica probar el trabajo creado inmediatamente, independientemente del horario. Para ello, expanda los nodos SQL Server Agent y Jobs en el Object Explorer. Haga clic derecho en el trabajo creado y seleccione Start Job at Step… Seleccione el primer paso del trabajo y haga clic en Start. Al recibir el mensaje de «Éxito», compruebe las copias de seguridad recién creadas y asegúrese de que el formato de nombre cumpla con las expectativas. Si este no es el caso, siempre es posible modificar cualquiera de los parámetros seleccionados seleccionando las Properties para el trabajo.
Abra la carpeta de la ruta de la copia de seguridad indicada en la consulta y compruebe el formato de los nombres de archivo de copia de seguridad. La ejecución del agente de trabajo de este ejemplo produce los siguientes archivos de copia de seguridad:

Si se utiliza la consulta del paso 5.2 para el agente de trabajo, la carpeta de copia de seguridad contiene archivos de copia de seguridad para todas las bases de datos del usuario del servidor:

Crear el plan de mantenimiento de SQL Server

Esta opción no requiere el uso de scripts de T-SQL dinámicos, pero ofrece mucho menos en la forma de personalizar. El nombre de archivo de copia de seguridad en este caso contiene el nombre de la base de datos, así como la fecha y hora en que se crea el archivo de copia de seguridad. Desafortunadamente, la secuencia y el formato de estos elementos no se pueden cambiar.

Existen dos formas de crear los planes de mantenimiento de SQL Server – ya sea creando manualmente el nuevo plan o utilizando el Asistente para planes de mantenimiento.

Para crear el nuevo plan de mantenimiento manualmente:

Expanda el nodo Management en el explorador de servidores y haga clic con el botón derecho en Maintenance Plans. Seleccione la opción… New Maintenance Plan.
Introduzca el nombre del plan.
Aparecerá la pestaña Diseño del plan de copia de seguridad, así como la Caja de herramientas. Arrastre y suelte la Tarea Back Up Database Task de la base de datos de la caja de herramientas a la superficie de diseño de la pestaña diseño del plan de copia de seguridad. Haga doble clic en el elemento para editar la configuración de la tarea de copia de seguridad.
En la pestaña General de Tarea de copia de seguridad de base de datos, especifique el tipo de copia de seguridad que se utilizará para la tarea. También, haga clic en el botón de flecha hacia abajo cerca de la casilla Database(s) y seleccione todas las bases de datos que se deben incluir en el plan de copia de seguridad.
En la pestaña Destination, especifique el destino de los archivos de copia de seguridad. Es posible establecer tareas adicionales como cifrado de la copia de seguridad, vencimiento y verificación en la pestaña Options, pero eso no es necesario para la tarea actual. Haga clic en OK cuando haya terminado.
Para establecer el cronograma de las operaciones definidas, haga clic en el botón calendar en Backup Plan design.
Se abre la ventana New Job Schedule. Introduzca el nombre de la nueva programación y establezca la frecuencia a diario. Compruebe la descripción en el resumen y haga clic en OK si cumple los requisitos.
Haga clic derecho sobre el encabezado de la pestaña Backup Plan [Design]* y haga clic en la opción Save selected items.
Para probar el plan creado, expanda Maintenance Plans bajo el nodo Management. Haga clic con el botón derecho en created backup plan y seleccione Execute…
La ejecución del plan se iniciará automáticamente. El mensaje de éxito se mostrará al finalizar. Se producirán los siguientes archivos de copia de seguridad:

El nombre de archivo contiene toda la información necesaria, como: año, mes, día, hora, minuto y segundo. Por desgracia, este formato no se puede cambiar, y debe ser utilizado como tal para todas las tareas de copia de seguridad de base de datos.

Para obtener nombres de copia de seguridad totalmente personalizados para los planes de mantenimiento, es necesario utilizar Execute T-SQL Statement Task en el paso 3 con uno de los scripts de T-SQL del capítulo anterior.

Para crear el nuevo plan de mantenimiento utilizando el Maintenance Plan Wizard:

Expanda el nodo Management en el Server Explorer, haga clic con el botón derecho en Maintenance Plans y seleccione Maintenance Plan Wizard.
En el primer paso del asistente, introduzca el nombre y la descripción del plan de mantenimiento. También es necesario establecer el cronograma en este paso. Dado que el plan completo consta de unas cuantas tareas, no es necesario establecer un cronograma separado para cada tarea. Marque la casilla de opción delante de «Planificación única para todo el plan o sin cronograma» y haga clic en el botón Change… en la parte inferior derecha.
Proporcione nombre y frecuencia para el cronograma, como se describe en los capítulos anteriores. Para este ejemplo, se utilizará la programación diaria. Haga clic en OK cuando se establezca el cronograma y continúe con el siguiente paso del asistente de mantenimiento.
Marque las casillas delante de la tarea que debe ejecutarse. Se pueden seleccionar varias tareas. El cuadro de mensaje en la parte inferior de la ventana muestra una breve descripción de cada tarea seleccionada. Haga clic en Siguiente cuando haya terminado.
Establezca el orden para la ejecución de las tareas, si se seleccionaron varias tareas en el paso anterior. Continúe con el siguiente paso cuando haya terminado.
En la pestaña General del asistente, elija las bases de datos a las cuales se les realizará una copia de seguridad en la tarea. Establezca la ruta de copia de seguridad en el asistente Destination. De forma opcional, especifique la configuración de vencimiento, verificación y cifrado en la pestaña Options. Continúe con el paso siguiente.
Seleccione las opciones de informe para el maintenance plan. Establezca la ruta preferida para los archivos de informe y proporcione una dirección de correo electrónico para las notificaciones por correo electrónico. De forma alternativa, deje ambas casillas sin marcar si no hay necesidad de informes o notificaciones.
Revise todos los ajustes definidos en el paso final del asistente. Si todo está bien, haga clic en Finalizar para completar el asistente.
El mensaje de éxito aparecerá al crear el plan de mantenimiento. Haga clic en Cerrar cuando se ejecuten todas las operaciones.
Para probar el plan creado, expanda los nodos Management y Maintenance Plans en el explorador de servidores, haga clic con el botón derecho en el plan de mantenimiento creado y seleccione Execute.
El trabajo se inicia automáticamente. Si todo va bien, se completa con un mensaje de éxito. El formato del nombre de archivo es el mismo que en el caso anterior, y no se puede cambiar. Dado que la opción «Todas las bases de datos del usuario» se selecciona en el paso 6, se producen los siguientes archivos de copia de seguridad junto con el archivo txt del informe: