Cómo eliminar los archivos antiguos de respaldo de la base de datos automáticamente en SQL Server

Introducción

Mientras se trabaja con un número de bases de datos, crear un plan de recuperación puede ser retador, especialmente si queremos un diseño a prueba de tontos. Las estrategias de respaldo y restauración automáticas aseguran que el plan de recuperación sea exitoso. De todas maneras, las estrategias que se apoyan en respaldos frecuentes, aunque parte de una gran estrategia, pueden comenzar a causar problemas cuando el espacio disponible de almacenamiento se vuelve limitado. Las copias de seguridad antiguas son en muchos casos innecesarias, y es a menudo prudente eliminar esos respaldos para incrementar el espacio disponible. Esto puede ser hecho manualmente o automáticamente. Hay muchas opciones para eliminar archivos de respaldo antiguos automáticamente en SQL Server:

• Eliminar los archivos de respaldo de la base de datos automáticamente en SQL Server usando un Trabajo de SQL Server Agent.

• Eliminar los archivos de respaldo de la base de datos automáticamente en SQL Server usando un plan de SQL Server Maintenance.

• Eliminar los archivos de respaldo de la base de datos automáticamente en SQL Server usando ApexSQL Backup

Eliminar archivos de respaldo de la base de datos antiguos en SQL Server usando SQL Server Agent:

Para esta opción, SQL Server Agent deber estar instalado y corriendo. Requiere un conocimiento y entendimiento básicos de codificación con Transact-SQL (T-SQL), pero el proceso es enteramente automatizado, así que se necesitan más entradas del usuario. Es necesario crear un procedimiento almacenado primero, el cual será llamado por el trabajo de SQL Server Agent. La ventaja de esto es la habilidad de usar el mismo procedimiento almacenado a través de diferentes trabajos con diferentes parámetros de entrada. Para crear un programa de SQL Server Agent para eliminar los archivos de respaldo antiguos, debemos seguir los siguientes pasos:

1. El servicio de SQL Server Agent debe estar corriendo. En Object Explorer, revise el ícono al lado de SQL Server Agent. Si el mensaje “Agent XPs disabled” es mostrado a su lado, vaya a Control Panel/System and Security/Administrative Tools y ejecute Services (la localización exacta puede variar debido a diferentes sistemas operativos). Localice el servicio SQL Server Agent para la instancia SQL Server correspondiente, selecciónelo y haga clic en Start en la parte superior izquierda de la ventana, o simplemente haga clic derecho en él y seleccione Start. Si el servicio ya está corriendo, usted puede saltar este paso:

   

2. Crear un procedimiento almacenado que usará la entrada desde el trabajo programado de SQL Server Agent para eliminar los archivos de respaldo antiguos. Haga clic derecho en la base de datos sobre la que queremos actuar y seleccione New Query:

   

   En la nueva ventana de consultas, ingrese el siguiente T-SQL:

   CREATE PROCEDURE [dbo].[usp_DeleteOldBackupFiles] @path NVARCHAR(256),
   	@extension NVARCHAR(10),
   	@age_hrs INT
   AS
   BEGIN
   	SET NOCOUNT ON;
   
   	DECLARE @DeleteDate NVARCHAR(50)
   	DECLARE @DeleteDateTime DATETIME
   
   	SET @DeleteDateTime = DateAdd(hh, - @age_hrs, GetDate())
   
           SET @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))
   
   	EXECUTE master.dbo.xp_delete_file 0,
   		@path,
   		@extension,
   		@DeleteDate,
   		1
   END
   

   Como puede verse en esta consulta, un procedimiento almacenado extendido xp_delete_file está siendo usado. Este lee la cabecera del archivo para revisar qué tipo de archivo es y sólo eliminará ciertos tipos basado en los parámetros de entrada que elijamos. Después de correr la consulta, deberíamos terminar con un procedimiento almacenado:

   

3. Después de crear los procedimientos almacenados, necesitamos crear un trabajo programado con SQL Server Agent, el cual usará el procedimiento almacenado con nuestros parámetros para eliminar los antiguos archivos de respaldo.

   Para hacer eso, haga clic derecho en SQL Server Agent y seleccione New y luego Job…

   

4. En la pestaña General, ingrese un nombre descriptivo y, opcionalmente, una descripción para el trabajo:

   

5. En la pestaña Steps, vaya a New…::

   

6. En la ventana New Job Step, debajo de la pestaña General, ingrese un nombre de trabajo descriptivo, debajo de Database seleccione la base de datos sobre la que deseamos trabajar y debajo de Command: inserte la siguiente línea:

   usp_DeleteOldBackupFiles ‘D:\MSSQL_DBBackups’, ‘bak’, 720

   

   Para explicar la línea de arriba:

   usp_DeleteOldBackupFiles – llama al procedimiento almacenado que creamos antes.

   ‘D:\MSSQL_DBBackups’ – el primer parámetro le dice al procedimiento almacenado dónde buscar.
   ‘bak’ – el Segundo parámetro le dice qué extensión o qué tipo de archivo buscar.

   Nota: para la extensión, no use punto antes de la extensión, ya que xp_delete_file ya toma en cuenta eso. ‘.bak’ es incorrecto, opuesto a ‘bak’, que es correcto.

   720 – el tercer parámetro que le dice al procedimiento almacenado el número de horas que tiene que envejecer un archivo de respaldo para ser eliminado.

7. Debajo de la pestaña Advanced, elija qué será hecho después de que este paso es completado exitosamente. Viendo que este es el único paso, podemos seleccionar ‘Quit the job reporting success’. Aquí también podemos establecer cuántas veces el paso se reintentará, así como el tiempo de intervalo entre intentos en minutos.

   

8. La siguiente pestaña es Schedules. Debajo de esta pestaña podemos establecer cuándo funcionará este trabajo.

   Vaya a New…:

   

9. En la nueva ventana, ingrese un nombre descriptivo para el programa. También, para Schedule Type revise que esté establecido a Recurring para una operación programada apropiada. Después de eso, podemos configurar el programa usando las opciones de abajo:

   

   El resto de las pestañas es opcional en este caso de uso, aunque podríamos usar la pestaña Notifications para configurar las notificaciones de correo electrónico cuando el trabajo esté completado.

10. Después de completar el paso previo, nuestro Trabajo está creado. Correrá de acuerdo al programa, eliminando los archivos de respaldo antiguos como se estableció en el trabajo en sí mismo. SQL Server Management Studio por defecto no muestra ninguna notificación en tiempo real cuando el trabajo funciona. Si deseamos ver el historial del trabajo, podemos hacer clic derecho en el trabajo y seleccionar View History:

    

11. En la nueva ventana podemos ver el historial para el trabajo seleccionado, que se ejecutó exitosamente:

    

Eliminar archivos de respaldo antiguos de bases de datos automáticamente en SQL Server usando un plan de Mantenimiento de SQL Server

SQL Server Maintenance es otra manera de eliminar archivos de respaldo antiguo usando la tarea cleanup.

1. Cuando estemos conectados al servidor, expándalo y luego la carpeta Management. Luego, haga clic derecho en Maintenance Plans y haga clic en Maintenance Plan Wizard.

   

2. En Maintenance Plan Wizard, haga clic en Next.

   

3. Ingrese un nombre descriptive y, opcionalmente, una descripción, y abajo haga clic derecho en Change…

   

4. Cuando la ventana New Job Schedule aparezca, revise si Schedule type está establecido a Recurring. Después de eso, podemos establecer el programa usando las opciones abajo. Después de eso, haga clic en OK.

   

5. Revise si todo está correcto antes de hacer clic en Next:

   

6. En la siguiente ventana, seleccione Maintenance Cleanup Task y haga clic en Next:

   

7. En la siguiente ventana no hay un orden, ya que tenemos sólo una tarea, así que procedamos a hacer clic en Next:

   

8. En la siguiente ventana:

   1. Debajo de Delete files of the following type: seleccionamos Backup files haciendo clic en el botón radial.

   2. Debajo de File location: seleccionamos Search folder and delete files based on an extension. Debajo de eso, especificamos dónde buscar las carpetas y qué tipo de extensión buscar. También, podemos seleccionado la opción Include first-level subfolders si los respaldos están almacenados en sub carpetas separadas.

      Nota: la extensión de archivo que ingresamos no debe contener el punto (‘.’) – ‘.bak’ es incorrecto, ‘bak’ es correcto.

   3. Debajo de File age: seleccionamos la opción Delete files based on the age of the file at task run time y especificamos la edad de los archivos abajo.

   Después de revisar que todo esté correcto, procedemos a hacer clic en Next:

   

9. En la siguiente ventana, podemos seleccionar que un reporte sea escrito y/o enviado por correo electrónico a la dirección que ingresamos cada vez que el plan de mantenimiento corre.

   

10. En la siguiente ventana presionamos Finish para completar la creación de nuestro Plan de Mantenimiento. Después de eso, revisamos debajo de Management -> Maintenance Plans nuestro nuevo plan:

    

    

Eliminar archivos de respaldo antiguos de bases de datos en SQL Server usando ApexSQL Backup:

ApexSQL Backup es una herramienta de terceros que crea, organiza y monitorea varios trabajos de respaldo, restauración y mantenimiento..

La eliminación automática de archivos de respaldo antiguos es realizada con facilidad en unos pocos pasos sin necesidad de usar T-SQL u otros scripts.

Para configurar la eliminación automática de archivos de respaldo antiguos en ApexSQL Backup, debemos establecer un programa de respaldos a través de Backup Wizard.

Para hacer esto, debemos realizar los siguientes pasos:

1. Vaya a la pestaña Home y haga clic en Backup:

   

2. Cuando la ventana de Backup Wizard se abra, haga clic en la pestaña Advanced de la ventana Backup Wizard, donde vemos varias opciones para afinar el programa de respaldos. Al fondo de la ventana, debajo de Cleanup, elija la opción Delete backup files older than y especifique la edad en días cuando archivos de respaldo más antiguos que esa edad serán eliminados. De esa manera, cuando sea que el programa es ejecutado, verificará archivos de base de datos antiguos y los eliminará automáticamente:

   

Descargas

Por favor descargue los scripts asociados con este artículo en nuestro repositorio de GitHub.

Por favor contáctenos para cualquier problema o consulta acerca de los scripts.