Robert Seles

Múltiples métodos para efectuar una programación de una copia de seguridad de SQL Server automáticamente

September 17, 2019 by

Las copias de seguridad de SQL Server son parte esencial de toda buena estrategia para la recuperación en caso de desastres que pudieran darse. Eso es lo bueno de ellas. Pero el objetivo es el de configurar tales copias de seguridad de manera que se ejecuten sin requerir esfuerzo. En este artículo, se revisarán los tipos de copias de seguridad, las prácticas recomendadas para efectuarlas y tres métodos diferentes para poder configurar automáticamente las copias de seguridad de SQL Server en horario y a tiempo. Nota: estas soluciones también se pueden usar en de manera combinada

Diferentes tipos de copias de seguridad de SQL Server

Consulte también: Descripción de los tipos de copia de seguridad de SQL Server

Completo. Este es el tipo de copia de seguridad más común que incluye todo, incorporando objetos, datos de tablas del sistema y transacciones que ocurren durante la copia de seguridad. Con una copia de seguridad completa, se puede restaurar su base de datos al estado original en el que realizó la copia de seguridad. Las copias de seguridad completas no truncarán su registro de transacciones, pero si su base de datos está en recuperación será completa, adicionalmente usted también debe considerar las copias de seguridad del registro de transacciones

Vea también: Un recorrido por la copia de seguridad completa de la base de datos de SQL Server 2016

Diferencial. Este tipo de copia de seguridad ofrece un medio para mantener un historial completo de su base de datos, pero sin almacenar los datos redundantes. Una copia de seguridad diferencial retiene datos desde la última copia de seguridad completa. Una copia de seguridad diferencial solo es útil si la misma se usa en conjunto con una copia de seguridad completa, pero le permite eliminar copias de seguridad diferenciales anteriores ya que las mismas son redundantes

Registro de transacciones. Este tipo de copia de seguridad realizará una copia adicional de seguridad de todas las transacciones que se hayan producido desde la última copia de seguridad o truncamiento del registro, luego truncará al registro de transacciones. Esto capturará toda la información de la transacción, tanto DML como también DDL, que haya ocurrido en la base de datos. Con una copia de seguridad del registro de transacciones, se puede restaurar una base de datos a un punto particular en el tiempo, también conocido como recuperación en un punto en el tiempo, como para fijar puntualmente justo antes de un evento de pérdida de datos

Vea también: Comprender los modelos de recuperación de bases de datos de SQL Server

Grupo de archivos y archivo: este tipo de copia de seguridad es mejor para las bases de datos más grandes. Este tipo de copia de seguridad almacenará todos los datos relacionados en archivos o grupos de archivos (uno o muchos). Para poder usar las copias de seguridad de archivos para restaurar con éxito una base de datos, el registro de las transacciones también debe ser respaldado para poder cubrir todos los grupos de archivos de principio a fin.

Vea también: Grupo (s) de archivos de base de datos y restauraciones de Piecemeal en SQL Server

Solo copia. Este tipo de copia de seguridad normalmente se usará de forma ad-hoc y para no modificar el proceso existente de copias de seguridad de la base de datos, debido a que la copia de seguridad del registro de transacciones ignorará la copia de seguridad. Caso contrario, esto es lo mismo que una copia de seguridad completa de la base de datos. Las copias de seguridad de copia no pueden utilizarse para copias de seguridad diferenciales y copias de seguridad del registro de transacciones

Mejores prácticas de respaldo

Ubicación

Es importante indicar que la separación de la ubicación donde residen las copias de seguridad del servidor donde existe la base de datos, es crítica, porque de lo contrario, algunas fallas que afectan la base de datos también podrían mitigar su capacidad de usar las copias de seguridad para recuperarse de ella, por ejemplo, la falla de la unidad física.

Sin tener una guía explícita para la ubicación del archivo en cuanto a dónde almacenar las copias de seguridad, SQL Server usará la ubicación de la base de datos predeterminada.

Tenga en cuenta que simplemente el cambiar la ubicación predeterminada del archivo no tendrá ningún efecto en los archivos de copia de seguridad guardados anteriormente, pues solo determinará la ubicación donde se almacenaran las nuevas copias de seguridad.

Automatizar y programar copias de seguridad

Para hacer una copia de seguridad de una base de datos con éxito, en la ubicación del archivo y con la configuración que se desee es solo un primer paso. Una vez logrado el mismo se, querrá poder replicar este proceso una y otra vez, automáticamente y en horario y a tiempo. Programar copias de seguridad automatizadas es fundamental para garantizar la continuidad de la base de datos y reducir el esfuerzo manual requerido para lograrlo.

Es importante mencionar que las copias de seguridad periódicas de la base de datos no solo son una excelente póliza de seguro contra la pérdida accidental de datos y otros escenarios adicionales de ese tipo de desastre, sino que también brindan la capacidad de punto de almacenamiento (para copias de seguridad del registro de transacciones) y tiempo de inactividad reducido, si es necesario restaurar (ventanas de copia de seguridad más cortas > menos pérdida de datos)

Con qué frecuencia se debe programar las copias de seguridad? Esto depende de los requisitos de su negocio y los Objetivos de punto de recuperación (RPO), por ejemplo, si en su estándar no puede perder más de 15 minutos de datos, entonces las copias de seguridad deben programarse cada 15 minutos

Nota:: Si su base de datos está en modo de recuperación completa, puede usar ApexSQL Log para evitar restauraciones completas. ApexSQL Log podrá aislar y revertir transacciones falsas, mitigando los datos dañados sin tener que hacer una restauración completa. También se puede reproducir transacciones anteriores para restaurar los datos perdidos

También podrá usar ApexSQL Data Diff para comparar la base de datos actual con la última copia de seguridad de la base de datos (sin siquiera restaurarla), identificar filas diferentes / dañadas y repararlas quirúrgicamente con un script de sincronización, nuevamente, sin tener que restaurar la copia de seguridad.

Restaurar y probar copias de seguridad

Una copia de seguridad solo es buena siempre y cuando se puede restaurar con éxito por ello esto debe verificarse, y volver a verificar de manera continua para poder garantizar que su estrategia de copia de seguridad y restauración se pueda ejecutar con éxito cuando sea necesario. Las copias de seguridad solamente deben restaurarse en un entorno de prueba y verificarse completamente para garantizar que cumplan con todos los requisitos comerciales y las diversas permutaciones, contingencias y contextos de la estrategia de recuperación de la empresa

Al realizar las pruebas con las copias de seguridad, existen varias variables, incluidos el entorno, los datos, los marcos de tiempo de recuperación y las ventanas de pérdida de datos, el tiempo de inactividad de la aplicación al igual que el umbral máximo aceptable de pérdida de datos

Verificación de copias de seguridad

Para la verificación de respaldo de SQL Server incluye las siguientes verificaciones

  1. La copia de seguridad se creó con éxito
  2. Actualmente está intacto, físicamente y que todos los archivos no solo existen, sino que también son legibles
  3. La copia de seguridad se podrá restaurar cuando sea necesario
  4. Todas las transacciones son consistentes

Tenga en cuenta que al verificar una copia de seguridad no es posible garantizar la integridad o integridad de todos los datos, pero si que la copia de seguridad original se haya creado con WITH CHECKSUMS, eso permitirá que WITH CHECKSUMS se utilice para verificar los datos, a nivel agregado, para poder proporcionar nivel de confianza y cerciorarse no se perdieron datos. Veamos algunos casos de respaldo a continuación para proporcionar algunos ejemplos prácticos

con T-SQL:

Cuando se incluye el CHECKSUM, se podrá asegurar más tarde la integridad de los datos, cuando la copia de seguridad esté completa y escrita en el archivo. Para lograr esto, vea el siguiente ejemplo:

SSMS proporciona la facilidad para poder verifirar el respaldo usando CHECKSUM al crear una tarea de respaldo

Existen dos opciones disponibles en SSMS para poder implementar esta funcionalidad, ya sea la verificación de la copia de seguridad cuando finalice y luego realizar la suma de comprobación antes de escribir en los medios de almacenamiento. Estas opciones están destinadas a proteger su proceso de copia de seguridad de la base de datos e identificar cualquier falla

Es importante también incluir esta funcionalidad en sus copias de seguridad programadas automáticamente

En este artículo, mostraremos cómo crear y programar automáticamente una copia de seguridad de SQL Server usando Trabajos del Agente SQL Server y planes de mantenimiento

Crear una copia de seguridad programada utilizando trabajos del Agente SQL Server

Con el fin de programar una copia de seguridad automatizada con un trabajo del Agente SQL Server:

  1. Haga clic en el botón derecho en Trabajos (Jobs) y seleccione Nuevo trabajo (New Job) en el menú contextual, en el panel del explorador de objetos, en SSMS, debajo del nodo Agente SQL Server.

  2. 2. Ingrese un nombre para el trabajo en el cuadro de diálogo Nuevo trabajo

  3. Haga clic en el botón Nuevo, debajo de la pestaña Pasos, y cree un nuevo paso de respaldo agregando una declaración SQL

    Use el próximo código en SQL para crear una copia de seguridad diferencial

    Para poder crear una copia de seguridad del registro de transacciones, use esta instrucción en SQL

    6. Es importante indicar un requisito previo necesario para poder crear un registro de transacciones o una copia de seguridad diferencial es que ya debe existir una copia de seguridad completa de la base de datos de SQL Server. Por lo tanto, se debe crear una copia de seguridad completa de la base de datos, si la base de datos nunca ha sido respaldada antes, previamente antes de crear un diferencial

    7. El registro de transacciones y las copias de seguridad diferenciales funcionan bien al realizarse junto con las copias de seguridad completas de la base de datos y se podrán usar en conjunto. Al adoptar un enfoque sofisticado para las copias de seguridad, se puede lograr un alto nivel de continuidad de la base de datos y un seguro de protección contra la pérdida de datos al tiempo que minimiza los requisitos de almacenamiento de archivos de copia de seguridad. Es importante indicar que puede por ejemplo, programar una copia de seguridad completa de SQL Server cada 12 horas, pero un diferencial, con mucha más frecuencia, digamos cada 4 horas y finalmente, hacer una copia de seguridad de su registro de transacciones cada 15 minutos. Es fundamental mencionar que la clave es encontrar el punto óptimo entre la mitigación de la pérdida potencial de datos y los requisitos de almacenamiento que sea óptimo para su organización

  4. Haga clic en Aceptar para agregar un paso y en Aceptar. de nuevo, para crear un trabajo:

  5. Para programar un trabajo, haga clic en Nuevo trabajo en el cuadro de diálogo Nuevo trabajo, en la pestaña Programación

  6. En la pestaña Programa de trabajo seleccione una frecuencia recurrente y una fecha de inicio y haga clic en Aceptar:

Haga clic con el botón derecho en el trabajo y seleccione Iniciartrabajo en la opción de paso, para verificar un trabajo creado en el panel del Explorador de objetos SSMS, en el Agente SQL Server y luego en el nodo Trabajos node

Con el objeto de realizar una copia de seguridad de todas las bases de datos, por medio del uso del Agente SQL Server, para una instancia de SQL Server hay dos enfoques; en ambos se requerirá de un esfuerzo adicional. Una forma es crear un paquete en SSIS utilizando la opción Tarea de base de datosde respaldo de la barra de herramientas SSIS y así crear un trabajo del Agente SQL Server, luego programarlo

El enfoque es crear una secuencia de comandos en SQL para poder hacer una copia de seguridad de todas las bases de datos en el cuadro de diálogo del Paso de trabajo del Agente SQL Server.

Crear una copia de seguridad programada de SQL Server con planes de mantenimiento de SQL Server

Se podrá usar Planes de mantenimiento de SQL Server, de forma manual, para crear una copia de seguridad programada o usar el Asistente de planes de mantenimiento.

Con el fin de crear una tarea de copia de seguridad de SQL Server programada manualmente:

  1. Haga clic en Planes de mantenimiento, en el nodo Administración en el explorador de objetos SSMS, y seleccione Nuevo plan de mantenimiento.

  2. Seleccione Copia de seguridad de la tarea de base de datos: en la barra de herramientas Tareas del plan de mantenimiento

    Adicionalmente existe una opción de tarea para verificar la integridad de la base de datos como una opción en el Asistente del plan de mantenimiento, que permite incluir esta funcionalidad en su plan de mantenimiento

  3. Haga clic en el plan recién agregado para configurar los ajustes de respaldo:

  4. Use la opción de planificación de Sub plan para programar un trabajo del Agente SQL Server
  5. Una vez que tenga un plan que esté configurado correctamente, haga clic en Guardar y esto creará un nuevo trabajo en el Agente SQL Server y luego en la carpeta Trabajos

Contar con el Asistente de plan de mantenimiento es más fácil y más conveniente, pero proporcionará menor control granular y menor cantidad opciones de configuración

Para este fin el programar automáticamente una copia de seguridad en SQL Server con el Asistente del plan de mantenimiento de SQL Server, usted deberá realizar con los siguientes pasos:

  1. 1. Haga clic en el botón derecho en Planes de mantenimiento y seleccione la opción de Asistente de planes de mantenimiento en el panel Explorador de objetos en el nodo de Administración:

  2. En la ventana de Seleccionar propiedades del plan proporcione un nombre para el plan. haga clic en el botón Cambiar para programar el trabajo:

  3. Seleccione la opción Copia de seguridad de la base de datos en Seleccionar tareas del plan de mantenimiento y la configuración Verificar integridad de datos. La tarea Verificar integridad de datos realizara una verificación de la consistencia de las páginas de datos e índices dentro de la base de datos:

  4. 4. Al configurar la tarea de mantenimiento, en el siguiente cuadro de diálogo, es importante especificar una base de datos para la copia de seguridad y la configuración de la copia de seguridad. Marque la opción Verificar integridad de copia de seguridad, en la ventana Definir tarea de base de datos de copia de seguridad también:

  5. Haga clic en Finalizar una vez que esté contento con la configuración de su trabajo:

Para los planes de mantenimiento, debido a sus características con valor agregado y su interfaz de apuntar y hacer clic (en vez de tener que escribir scripts), los mismos son una buena opción para las personas con menos experiencia. Pero la facilidad de uso se produce al costo de la incapacidad de personalizar muchas opciones o ejercer un control granular sobre los mismos.

Además, la extensibilidad de los planes de mantenimiento la misma es extremadamente limitada, ya que son básicamente ponis de un solo truco, que son incapaces de procesar múltiples tareas a la vez. Un plan puede solamente admitir solo una tarea de mantenimiento, por lo que, por definición, ejecutar un plan de este tipo dará como resultado la ejecución de una sola tarea. Imagine tener que crear un plan para eliminar archivos de copia de seguridad antiguos, pero solo podrá eliminar un tipo de archivo a la vez. La “solución” es crear más y más planes, también conocidos como “Fluencia del plan” con el fin de poder escalar el procesamiento de tareas, y recuerde que cada Plan de mantenimiento requerirá su propio trabajo del Agente SQL Server, si se va a programar. El resultado de esto puede convertirse rápidamente en un ecosistema de planes que no es manejable, que requiere mucho esfuerzo y mantenimiento para poder organizarse.

Para aprender la forma de crear copias de seguridad con SSIS, vea Crear copias de seguridad con SSIS

Con el fin de obtener información sobre cómo realizar copias de seguridad con contenedores Docker, usted debe consultar la Descripción de las operaciones de copia de seguridad y restauración en contenedores Docker de SQL Server

Ver también: copias de seguridad de bases de datos inteligentes en SQL Server 2017

Aquí existe un resumen de enfoques sobre cómo poder programar una copia de seguridad de la base de datos comparada de lado a lado:

Maintenance Plans SQL Server Agent
Run jobs without SQL scripting required X
Backup all databases without SQL coding X
Execute a task with a single job X

Referencias

Robert Seles
Últimas entradas de Robert Seles (ver todo)

Related posts:

  1. Una tarea de copia de seguridad de base de datos vía SQL Server Maintenance Plans
  2. Respaldar y restaurar una base de datos SQL Server usando múltiples archivos
  3. Seis métodos diferentes para copiar las tablas entre bases de datos en SQL Server
  4. Técnicas para realizar una copia masiva, importación y exportación en SQL Server
  5. Una guía para principiantes acerca de los registros de transacciones SQL Server
168 Views