Copias de seguridad automáticas en SQL Server Express

En esta entrada os voy a describir los pasos que podéis seguir para programar, de forma sencilla y fiable, copias de seguridad automáticas de las bases de datos que tengáis en una instancia de SQL Server Express.

De todas las opciones que existen para gestionar bases de datos en Microsoft SQL Server, para la gran mayoría de proyectos es más que suficiente las posibilidades que nos ofrece la edición gratuita SQL Server Express, a pesar de que cuenta con algunas limitaciones:

• Trabaja con sólo 1 procesador, de 4 núcleos como máximo.
• Utiliza como máximo 1 GB de RAM para almacenar cachés de información.
• Permite bases de datos de hasta 10 GB de tamaño.

Al ser una plataforma gratuita, es comprensible que cuando queremos realizar tareas más o menos avanzadas, como automatizar las copias de seguridad de las bases de datos que tenemos en la instancia, nos encontremos con que las funcionalidades que tenemos disponibles estén un poco limitadas.

En SQL Server Express no contamos con el componente SQL Server Agent, el cual nos permite programar multitud de tareas automáticas, entre ellas, las copias de seguridad de las bases de datos. Por tanto, si queremos conseguir este objetivo y que podamos hacerlo con este sistema gratuito, debemos buscar una alternativa.

¡Pongámonos en marcha!

Vamos a crear un procedimiento almacenado en la base de datos maestra de la instancia, que será el encargado de realizar el trabajo. Para ejecutarlo, guardaremos un script en un archivo .bat con una instrucción. Y para que el proceso de ejecución sea automático, lo configuraremos mediante el Programador de Tareas de Windows.

Como véis, herramientas fáciles, gratuitas y al alcance de cualquier usuario que haya montado su base de datos en SQL Server Express.

Los pasos a seguir son los siguientes:

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

Como ya os he comentado, ejecutará todas las instrucciones necesarias para generar las copias de seguridad de las bases de datos que tengamos en la instancia. Para ello, usaremos la herramienta SQL Server Management Studio (SSMS), que suele instalarse conjuntamente con el servicio avanzado o el kit de herramientas de SQL Server Express.

Nos conectamos a la instancia:

Una vez dentro, pinchamos en «Nueva consulta»:

En la pestaña que se abre añadimos el siguiente código:

USE [master]
GO

/******** Object: StoredProcedure [dbo].[sp_BackupDatabases] ********/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ========================================================================
-- Copyright © Microsoft Corporation. Todos los derechos reservados.
-- Código publicado bajo los términos de la licencia pública de Microsoft
-- (MS-PL) http://opensource.org/licenses/ms-pl.html
-- Descripción: Copia de seguridad de bases de datos para SQLExpress
-- Parameter1: databaseName
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: ubicación del archivo de copia de seguridad
-- ========================================================================

CREATE PROCEDURE [dbo].[sp_BackupDatabases]

    @databaseName sysname = null,
    @backupType CHAR(1),
    @backupLocation nvarchar(200)

AS

SET NOCOUNT ON;

DECLARE @DBs TABLE
(
    ID int IDENTITY PRIMARY KEY,
    DBNAME nvarchar(500)
)

-- Elija solo bases de datos que estén en línea en caso de que se haya optado por crear copias de seguridad de TODAS las bases de datos
-- Si se optó por hacer una copia de seguridad de una base de datos específica, selecciónela en @DBs

INSERT INTO @DBs (DBNAME)
SELECT Name FROM master.sys.databases
    where state = 0
    AND name = @DatabaseName
    OR @DatabaseName IS NULL
ORDER BY Name

-- Filtra las bases de datos que no requieren copia de seguridad

IF @backupType = 'F'
    BEGIN
    DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
    END
ELSE IF @backupType = 'D'
    BEGIN
    DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
    END
ELSE IF @backupType = 'L'
    BEGIN
    DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
    END
ELSE
    BEGIN
    RETURN
    END

-- Declara las variables

DECLARE @BackupName varchar(100)
DECLARE @BackupFile varchar(100)
DECLARE @DBNAME varchar(300)
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
DECLARE @Loop int                  

-- Repite una base de datos tras otra

SELECT @Loop = min(ID) FROM @DBs

WHILE @Loop IS NOT NULL
    
BEGIN

-- Los nombres de las bases de datos tienen que tener el formato [dbname], ya que algunos incluyen los caracteres - o _

    SET @DBNAME = '[' + (SELECT DBNAME FROM @DBs WHERE ID = @Loop) + ']'

-- Define la fecha y hora actuales en formato aaaahhmmss

    SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')

-- Crea el nombre del archivo de la copia de seguridad en formato ruta\nombredearchivo.extension para copias de seguridad completas, diferenciales y de registro (logs)

    IF @backupType = 'F'
        SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','') + '_FULL_' + @dateTime + '.BAK'
    ELSE IF @backupType = 'D'
        SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','') + '_DIFF_' + @dateTime + '.BAK'
    ELSE IF @backupType = 'L'
        SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','') + '_LOG_' + @dateTime + '.TRN'

-- Coloca un nombre a la copia de seguridad para guardarla en los dispositivos

    IF @backupType = 'F'
        SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') + ' full backup for ' + @dateTime
    IF @backupType = 'D'
        SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') + ' differential backup for ' + @dateTime
    IF @backupType = 'L'
        SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') + ' log backup for ' + @dateTime

-- Genera el comando SQL dinámico que se ejecutará

    IF @backupType = 'F'
        BEGIN
        SET @sqlCommand = 'BACKUP DATABASE ' + @DBNAME + ' TO DISK = ''' + @BackupFile + ''' WITH INIT, NAME= ''' + @BackupName + ''', NOSKIP, NOFORMAT'
        END
    IF @backupType = 'D'
        BEGIN
        SET @sqlCommand = 'BACKUP DATABASE ' + @DBNAME + ' TO DISK = ''' + @BackupFile + ''' WITH DIFFERENTIAL, INIT, NAME= ''' + @BackupName + ''', NOSKIP, NOFORMAT'
        END
    IF @backupType = 'L'
        BEGIN
        SET @sqlCommand = 'BACKUP LOG ' + @DBNAME + ' TO DISK = ''' + @BackupFile + ''' WITH INIT, NAME= ''' + @BackupName + ''', NOSKIP, NOFORMAT'
        END

-- Ejecuta el comando SQL generado

    EXEC(@sqlCommand)

-- Va a la siguiente base de datos

    SELECT @Loop = min(ID) FROM @DBs where ID > @Loop

END

Y ejecutamos:

PASO 2. Crear un archivo .bat con la instrucción que ejecutará el procedimiento del paso 1.

Con nuestro editor de texto preferido vamos a crear un archivo nuevo, lo guardaremos con el nombre «Sqlbackup.bat» (recomiendo guardarlo en una carpeta específica para este tipo de archivos, para tenerlos siempre localizados), y dependiendo del tipo de copia de seguridad que queramos hacer, indicaremos en su contenido una instrucción diferente, construida mediante la utilidad SQLCMD.

Aunque podemos complicar la instrucción todo lo que necesitemos, para lo cual podéis echar un vistazo a todos los parámetros que admite SQLCMD, os indico a continuación la instrucción más sencilla posible, señalando en rojo aquellas partes que se deben / pueden modificar, en función del resultado que busquemos conseguir:

sqlcmd -S .\PARÁMETRO_1 -Q «EXEC sp_BackupDatabases @backupLocation=’PARÁMETRO_2‘, @backupType=’PARÁMETRO_3‘»

• PARÁMETRO_1: Nombre de la instancia en la que se encuentran las bases de datos para las que queremos hacer la copia de seguridad.
• PARÁMETRO_2: Ubicación física en la que vamos a guardar el archivo de la copia de seguridad.
• PARÁMETRO_3: Tipo de copia de seguridad que vamos a realizar. Puede tomar los siguientes valores, F (completa), D (diferencial) o L (de registro – log).
• Por último, hay que añadir el tipo de conexión que vamos a realizar con la instancia. Si vamos a usar una conexión de confianza (lo más habitual) como la Autenticación de Windows, colocaremos después del PARÁMETRO_1 «-E«. Si en lugar de conectar así preferimos hacerlo mediante usuario y contraseña de SQL (el usuario debe tener por lo menos el rol de operador de copia de seguridad (BackupOperator) en SQL Server), debemos indicar al principio de la instrucción, después de la palabra sqlcmd, «-U USUARIO -P CONTRASEÑA«.

Para que veáis mejor cómo quedaría la instrucción, os pongo dos ejemplos. En ambos, se conecta a una instancia denominada SQLEXPRESS y guarda las copias de seguridad en una carpeta denominada «backups», ubicada en el disco duro «D».

En el primer ejemplo, para hacer copias de seguridad completas y conectarse a la instancia mediante la autenticación de Windows, la instrucción sería la siguiente:

sqlcmd -S .\SQLEXPRESS –E -Q «EXEC sp_BackupDatabases @backupLocation=’D:\backups\’, @backupType=’F'»

En el segundo ejemplo, para hacer copias de seguridad diferenciales de sólo una de las bases de datos, denominada DBPRUEBA, y conectarse a la instancia mediante un usuario denominado DBUSER y la contraseña 12345, la instrucción sería la siguiente:

sqlcmd -U DBUSER -P 12345 -S .\SQLEXPRESS -Q «EXEC sp_BackupDatabases  @backupLocation =’D:\backups\’, @databaseName=’DBPRUEBA’, @BackupType=’D’»

PASO 3. Automatizar con el Programador de tareas de Windows la ejecución del archivo .bat creado en el paso 2.

Nos vamos al menú Inicio > Herramientas administrativas de Windows > Programador de tareas.

En el panel de la derecha (Acciones), pulsamos en «Crear tarea…«, tras lo cual nos aparecerá una ventana con cinco pestañas:

GENERAL

Le ponemos un nombre y una descripción a la tarea. En el punto que remarco con el número 2 debe indicarse un usuario que tenga en SQL Server al menos el rol BackupOperator. Si estamos programando la tarea con un usuario de Windows que sepamos tiene ese rol, con que los puntos 1-2 coincidan será suficiente. De lo contrario, tendríamos que pinchar en «Cambiar usuario o grupo…» y escoger un usuario que tenga al menos ese rol, ya que es necesario para que pueda ejecutar el procedimiento que configuramos en el paso 1. También debemos señalar la opción «Ejecutar tanto si el usuario inició sesión como si no«.

DESENCADENADORES

Pinchamos en «Nuevo…» y nos aparece otra ventana. Aquí debemos programar la frecuencia de ejecución de la tarea. Personalmente, recomiendo que al menos sea diaria (he marcado «Diariamente y «Repetir cada 1 días») y sobre todo, que se ejecute a una hora en la que sepamos que no se va a estar trabajando con las bases de datos (en el ejemplo la he puesto a las 2:00 de la madrugada). Además, hay que asegurarse de que está marcada la opción «Habilitado» y pulsamos «Aceptar».

ACCIONES

Pinchamos en «Nuevo…». En la ventana que nos aparece nos fijamos que en «Acción» esté seleccionada la opción «Iniciar un programa«. Pinchamos en «Examinar…» y buscamos el archivo .bat que creamos en el paso 2. Una vez seleccionado le damos a «Aceptar».

En las pestañas «Condiciones» y «Configuración» recomiendo dejar las opciones tal cual vienen configuradas por defecto. Pulsamos en «Aceptar» y ya tendríamos la tarea programada.

Recomiendo también en este punto ejecutarla manualmente una vez, para comprobar que hace bien el trabajo que pretendemos. Para ello, en el panel central seleccionamos la tarea con el botón derecho, escogiendo la opción «Ejecutar», o bien la seleccionamos con el botón izquierdo y en el panel de la derecha, pinchamos en la opción «Ejecutar».

Si todo ha ido bien, tendremos los archivos de las copias de seguridad en la carpeta que definimos en la instrucción, en el archivo .bat del paso 2.

Para que el Programador de tareas de Windows pueda poner en marcha la tarea que hemos preparado periódicamente, es necesario que su servicio se esté ejecutando de forma continua, aunque haya reinicios del sistema. Para ello, deberá tener definido el «tipo de inicio» como Automático:

Una vez que pongas en marcha este proceso, debes tener en cuenta que se irán creando copias de seguridad según la programación que hayas establecido, sin parar, por lo que llegará un momento en el que el disco duro se llenará si no vas borrando periódicamente archivos.

Si quieres evitar tener que borrarlos manualmente, puedes seguir mi tutorial para hacer un borrado automático de las copias de seguridad más antiguas, a la misma vez que se van creando las nuevas.