Borrado automático de copias de seguridad antiguas en SQL Server Express

Siguiendo con el tutorial en el que expliqué cómo realizar copias de seguridad automáticas de nuestras bases de datos en SQL Server Express, veremos ahora cómo podemos realizar el borrado automático de las copias de seguridad antiguas, evitando que con la creación de las nuevas se llene nuestro disco duro.

Utilizando las mismas herramientas que en el tutorial anterior (procedimiento almacenado que realizará el borrado de las copias de seguridad antiguas, archivo .bat que ejecutará el procedimiento y el programador de tareas de Windows para automatizar el proceso), conseguiremos borrar automáticamente las copias de seguridad de las bases de datos según la antigüedad que nosotros decidamos.

Para evitar que el tutorial sea pesado y no repetirme, eliminaré los pasos que sean similares a lo explicado en el tutorial anterior. Podéis seguirlo como ayuda, añadiendo los detalles que os explicaré a continuación:

PASO 1. Crear el procedimiento almacenado en la base de datos maestra (master).

Utilizando la herramienta SQL Server Management Studio (SSMS), nos conectamos a la instancia de SQL Server y creamos una nueva consulta con el siguiente código:

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[sp_DeleteOldBackupDatabases]    Script Date: 23/10/2020 19:28:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_DeleteOldBackupDatabases]

    @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

PASO 2. Crear archivo .bat que ejecutará el procedimiento almacenado.

En lugar de crear un archivo .bat nuevo, usaremos el que se creó en el tutorial anterior, añadiéndole una nueva instrucción para ejecutar el procedimiento almacenado que hemos generado con el paso 1.

Lo abrimos con nuestro editor de texto y le añadimos a continuación una nueva línea con la siguiente instrucción:

sqlcmd -S .\PARÁMETRO_1 -E -Q «EXEC sp_DeleteOldBackupDatabases @path=’PARÁMETRO_2‘, @extension=’PARÁMETRO_3‘, @age_hrs=PARÁMETRO_4«

Los parámetros indicados en rojo deben contener lo siguiente:

• PARÁMETRO_1: Nombre de la instancia de SQL Server.
• PARÁMETRO_2: Ubicación física de la carpeta en la que están guardadas las copias de seguridad.
• PARÁMETRO_3: Extensión que tienen los archivos a eliminar, para nuestro caso sería «bak».
• PARÁMETRO_4: Número de horas de antigüedad que debe tener un archivo para permitir su eliminación. Si por ejemplo, queremos mantener sin borrar las copias de seguridad de un mes completo, colocaremos aquí el número 720 (24 horas x 30 días = 720). Los que superen esta antigüedad se borrarán.

PASO 3. Tarea programada de Windows para ejecutar el archivo .bat del paso 2.

Como hemos usado el mismo archivo .bat que en el tutorial anterior, no tenemos que realizar nada más en este paso.

La tarea que programamos en el paso 3 de ese tutorial se encargará de ejecutar el archivo, el cual contiene ahora dos instrucciones, la del tutorial anterior y la que le hemos añadido ahora.

Llegados a este punto, habrás conseguido asegurar de manera automática tus bases de datos, manteniendo un histórico de copias según tus necesidades y evitando que se llene el disco duro donde las almacenas, eliminando los backups más antiguos a medida que vas creando los nuevos.