Programación y automatización de copias de seguridad de bases de datos de SQL Server Express

Versión del producto original: SQL Server
Número de KB original: 2019698

Resumen

SQL Server Express no incluye Agente SQL Server, por lo que no se pueden programar trabajos de copia de seguridad ni planes de mantenimiento de la manera en que puede hacerlo en otras ediciones de SQL Server. Para automatizar las copias de seguridad en SQL Server Express, combine tres componentes: el sp_BackupDatabases procedimiento almacenado, la sqlcmd utilidad de línea de comandos y Windows Programador de tareas. En este artículo se describen los pasos que configuran copias de seguridad completas, diferenciales y del registro de transacciones programadas de bases de datos de SQL Server Express. También se explica cómo confirmar que se ejecutó una copia de seguridad programada.

Este artículo solo se aplica a SQL Server ediciones Express. No se aplica a SQL Server Express LocalDB.

Por qué SQL Server Express no puede programar copias de seguridad con Agente SQL Server

Agente SQL Server es el componente que ejecuta trabajos programados y planes de mantenimiento, y no se incluye en SQL Server Express. Para obtener una comparación completa de lo que incluye cada edición, consulte Ediciones y características admitidas de SQL Server.

Sin Agente SQL Server, todavía puede realizar una copia de seguridad de una base de datos SQL Server Express a petición mediante cualquiera de las siguientes herramientas:

Utilidad sqlcmd
SQL Server Management Studio (SSMS)
Extensión MSSQL para Visual Studio Code
Script de Transact-SQL que usa la familia backup (Transact-SQL) de comandos

Para ejecutar esas copias de seguridad según una programación en lugar de a petición, use Windows Programador de tareas como motor de programación, como se describe en los pasos siguientes. Para obtener información general sobre los tipos de copia de seguridad y la estrategia, consulte Copia de seguridad y restauración de bases de datos de SQL Server.

Prerequisites

Antes de comenzar, asegúrese de que tiene:

Una instancia en ejecución de SQL Server Express. En los ejemplos de este artículo se usa la instancia con nombre predeterminada, .\SQLEXPRESS.
La pertenencia al rol fijo de servidor sysadmin en esa instancia, lo cual es necesario para crear un procedimiento almacenado en la base de datos master.
Una carpeta o unidad local con suficiente espacio libre para almacenar los archivos de copia de seguridad. En los ejemplos se usa D:\SQLBackups.
Permiso para crear tareas programadas en el equipo que ejecuta SQL Server Express.

Paso 1: Crear el procedimiento almacenado sp_BackupDatabases

sp_BackupDatabaseses un procedimiento almacenado proporcionado Microsoft que ejecuta el comando adecuado BACKUP en una base de datos o en cada base de datos en línea de la instancia. La crea una sola vez en la base de datos master.

Descargue el script de SQL_Express_Backups.sql y guárdelo localmente, por ejemplo, como C:\Temp\SQL_Express_Backups.sql. A continuación, cree el procedimiento almacenado mediante SSMS o sqlcmd.

Creación del procedimiento almacenado mediante SSMS

Abra SSMS y conéctese a la instancia de SQL Server Express. En Nombre del servidor, escriba .\SQLEXPRESSy seleccione Conectar.
Seleccione Archivo>abierto>archivo, seleccione el archivo SQL_Express_Backups.sql que guardó y, a continuación, seleccione Abrir.
Confirme que la lista de bases de datos de la barra de herramientas muestra master. El script comienza con USE [master], por lo que tiene como destino la base de datos correcta automáticamente.
Seleccione Ejecutar (o presione F5). El panel Mensajes muestra Commands completed successfully.

Creación del procedimiento almacenado mediante sqlcmd

Ejecute el siguiente comando desde el símbolo del sistema:

sqlcmd -S .\SQLEXPRESS -E -i "C:\Temp\SQL_Express_Backups.sql"

Confirmación de que se creó el procedimiento almacenado

Ejecute la siguiente consulta en la instancia. Devuelve una fila si el procedimiento almacenado existe:

SELECT name, create_date
FROM master.sys.procedures
WHERE name = 'sp_BackupDatabases';

parámetros de sp_BackupDatabases

El procedimiento almacenado acepta los parámetros siguientes:

Parámetro	Required	Description
`@backupLocation`	Yes	Carpeta que recibe los archivos de copia de seguridad, por ejemplo `D:\SQLBackups\`. La carpeta ya debe existir.
`@backupType`	Yes	Tipo de copia de seguridad: `F` para completo, `D` para diferencial o `L` para el registro de transacciones.
`@databaseName`	No	La base de datos de la que se va a hacer una copia de seguridad. Si omite este parámetro, el procedimiento almacenado realiza una copia de seguridad de todas las bases de datos en línea de la instancia, excepto las bases de datos que excluye el script.

El procedimiento almacenado nunca realiza una copia de seguridad de tempdb. También omite las bases de datos Northwindde ejemplo , pubsy AdventureWorks para cada tipo de copia de seguridad y omite master las copias de seguridad diferenciales y de registro porque esos tipos de copia de seguridad no son compatibles con la master base de datos.

Importante

Los nombres de base de datos excluidos se codifican de forma dura en el script. Si tiene una base de datos que usa uno de esos nombres, el procedimiento almacenado lo omite sin notificar un error. Para realizar una copia de seguridad de dicha base de datos, edite las DELETE @DBs WHERE DBNAME IN (...) listas del script antes de crear el procedimiento almacenado.

Paso 2: Instalación de la utilidad sqlcmd

La sqlcmd utilidad permite ejecutar instrucciones Transact-SQL, procedimientos almacenados del sistema y archivos de script desde la línea de comandos. Se instala con SSMS y también está disponible como descarga independiente para equipos que no tienen SSMS. Para instalarlo, consulte Descargar e instalar la utilidad sqlcmd.

Para confirmar que sqlcmd está instalado y disponible, ejecute sqlcmd -? en una ventana del símbolo del sistema.

La carpeta que contiene el sqlcmd archivo ejecutable normalmente se agrega a la Path variable de entorno al instalar SQL Server o las herramientas independientes. Si sqlcmd -? notifica que el comando no se reconoce, agregue la carpeta a la Path variable o especifique la ruta de acceso completa a la utilidad en el archivo por lotes.

Paso 3: Crear el archivo por lotes de Sqlbackup.bat

En un editor de texto, cree un archivo por lotes denominado Sqlbackup.bat. Copie el texto de uno de los ejemplos siguientes en ese archivo, en función de su escenario.

Tenga en cuenta los siguientes puntos antes de elegir un ejemplo:

Cada ejemplo usa D:\SQLBackups como marcador de posición. Cambie la ruta de acceso a la unidad y la carpeta de copia de seguridad que desea usar en el entorno y asegúrese de que la carpeta existe.
Si usa la autenticación de SQL Server, la contraseña se almacena en texto sin cifrar en el archivo por lotes. Restrinja el acceso a la carpeta que contiene el archivo por lotes solo a los usuarios autorizados.

Ejemplo 1: Copias de seguridad completas de todas las bases de datos mediante la autenticación de Windows

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"

Ejemplo 2: Copias de seguridad diferenciales de todas las bases de datos mediante la autenticación de SQL Server

REM Sqlbackup.bat
sqlcmd -U <YourSQLLogin> -P <StrongPassword> -S .\SQLEXPRESS -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='D'"

Nota:

Para ejecutar copias de seguridad, el inicio de sesión debe ser miembro del rol fijo de base de datos db_backupoperator o db_owner en cada base de datos de la que haga una copia de seguridad, o miembro del rol fijo de servidor sysadmin. Para obtener más información, consulte Roles de nivel de base de datos.

Ejemplo 3: Copias de seguridad del registro de transacciones de todas las bases de datos mediante la autenticación de Windows

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='L'"

Las copias de seguridad del registro de transacciones requieren el modelo de recuperación completo o optimizado para cargas masivas de registros y requieren al menos una copia de seguridad completa anterior. Para obtener más información, consulte modelos de recuperación (SQL Server).

Ejemplo 4: Copia de seguridad completa de una base de datos única mediante la autenticación de Windows

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName='USERDB', @backupType='F'"

Para realizar una copia de seguridad diferencial de USERDB, cambie el @backupType parámetro a D. Para realizar una copia de seguridad del registro de transacciones, cámbiela a L.

La opción -b hace que sqlcmd se cierre y devuelva un valor DOS ERRORLEVEL de 1 cuando se produce un error de SQL Server con un nivel de gravedad superior a 10. Sin -b, sqlcmd devuelve 0 incluso cuando falla la copia de seguridad, y el Programador de tareas informa de que la tarea se ha completado correctamente.

Antes de continuar, ejecute Sqlbackup.bat manualmente en una ventana del símbolo del sistema y confirme que los archivos de copia de seguridad aparecen en la carpeta de copia de seguridad. La corrección de errores ahora es más fácil que diagnosticarlos más adelante a través del Programador de tareas.

Paso 4: Programar el archivo por lotes en Windows Programador de tareas

Siga estos pasos para ejecutar Sqlbackup.bat según una programación:

En el equipo que ejecuta SQL Server Express, seleccione Iniciar y escriba Programador de tareas en el cuadro de texto.
En Mejor coincidencia, seleccione Programador de tareas para iniciarlo.
En Programador de tareas, haga clic con el botón derecho en Programador de tareas (local) y seleccione Crear tarea básica.
Escriba un nombre para la nueva tarea (por ejemplo, SQLBackup) y, a continuación, seleccione Siguiente.
Seleccione Diario para el desencadenador de tareas y, a continuación, seleccione Siguiente.
Establezca la periodicidad en un día y, a continuación, seleccione Siguiente.
Seleccione Iniciar un programa como acción y, a continuación, seleccione Siguiente.
Seleccione Examinar, seleccione el archivo Sqlbackup.bat que creó en paso 3: Crear el archivo por lotes de Sqlbackup.bat y, a continuación, seleccione Abrir.
Active la casilla Abrir el cuadro de diálogo Propiedades de esta tarea al hacer clic en Finalizar y, a continuación, seleccione Finalizar.
En la pestaña General , revise las opciones seguridad y confirme lo siguiente para la cuenta que aparece en Al ejecutar la tarea, use la siguiente cuenta de usuario:
- La cuenta tiene al menos permisos de lectura y ejecución para ejecutar la sqlcmd utilidad.
- Si el archivo por lotes usa Windows autenticación, la cuenta tiene permiso para realizar copias de seguridad de las bases de datos de SQL Server.
- Si el archivo por lotes usa la autenticación de SQL Server, la cuenta de inicio de sesión de SQL Server del archivo por lotes dispone de permisos para realizar copias de seguridad de las bases de datos.
Ajuste la configuración restante para que coincida con los requisitos y, a continuación, seleccione Aceptar.

Sugerencia

Como prueba, ejecute Sqlbackup.bat desde un Símbolo del sistema que haya iniciado con la misma cuenta de usuario con la que se ejecuta la tarea. Este paso confirma que la cuenta tiene los permisos que necesita antes de que se ejecute la programación.

Para obtener más información sobre las opciones de programación, vea Programador de tareas.

Comprobación de que se ejecutó la copia de seguridad programada

Después de la primera ejecución programada, confirme que la copia de seguridad se realizó correctamente:

Compruebe la carpeta de copia de seguridad de los nuevos archivos de .bak (copias de seguridad completas y diferenciales) o archivos .trn (copias de seguridad del registro de transacciones). El procedimiento almacenado agrega una marca de fecha y hora a cada nombre de archivo.
En Programador de tareas, seleccione Biblioteca del programador de tareas, seleccione la tarea y revise la columna Resultado de la última ejecución . Esta columna muestra el código de salida que devolvió el archivo por lotes. Dado que los ejemplos utilizan la opción -b, un valor de 0x1 significa que el comando de copia de seguridad falló. Para otros valores, consulte constantes de error y éxito del Programador de tareas.
Consulte el historial de copia de seguridad en la instancia:
```
SELECT database_name, type, backup_start_date, backup_finish_date, physical_device_name
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bmf
    ON bs.media_set_id = bmf.media_set_id
ORDER BY backup_start_date DESC;
```
Estas tablas forman parte del historial de copia de seguridad que SQL Server mantiene en la msdb base de datos. Para obtener más información, consulte Historial de copia de seguridad e información de encabezado (SQL Server).