Cómo programar un respaldo de SQL Server

Tener un buen plan de respaldo y restauración es una parte importante de una estrategia de recuperación de desastres. Este artículo describirá 3 diferentes soluciones/enfoques para crear una copia de seguridad programada en SQL Server.

Como una parte de la estrategia de respaldos, muchos tipos de copias de seguridad pueden ser usadas juntas.

Tipos de copias de seguridad

1. Copias de seguridad de bases de datos completas. Incluyen todos los objetos de la base de datos, tablas de sistema, datos y transacciones que ocurrieron durante la toma de copia de seguridad. Las copias de seguridad completas permiten realizar una restauración completa a un estado antes de que se realizara el respaldo.
2. Copias de seguridad diferenciales. Contienen datos que han cambiado desde la última copia de seguridad completa y transacciones que ocurren durante el proceso de respaldo. Una copia de seguridad diferencial es usada junto con la última copia de seguridad de la base de datos completa. Después de tomar una copia de seguridad diferencial, dado que es tomada después de la última copia de seguridad completa, todas las copias de seguridad diferenciales antiguas se vuelven obsoletas.
3. Copias de seguridad de registros de transacciones. Graban todas las transacciones que han ocurrido en la base de datos desde la copia de seguridad del registro de transacciones previa y luego truncan el registro de transacciones. Una copia de seguridad de registro de transacciones se asegura de que la base de datos se recupere a un punto específico del tiempo; por ejemplo, a un momento previo a la pérdida de datos.
4. Copias de seguridad de archivos y grupos de archivos. Esta opción es la más apropiada para respaldar bases de datos muy grandes. Un archivo de copia de seguridad contiene todos los datos en uno o más archivos o grupos de archivos. Una copia de seguridad del registro de transacciones tiene también que ser realizado para cubrir todos los archivos de respaldos desde el principio hasta el final usando copias de seguridad para restaurar la base de datos.
5. Copias de seguridad de sólo copia. Son principalmente usadas cuando es necesario respaldar una base de datos sin afectar el proceso de respaldo y restauración para una base de datos específica. La funcionalidad de copias de seguridad de sólo copia es la misma que la de copias de seguridad completas, con la diferencia de que una copia de seguridad del registro de transacciones respaldará todas las transacciones desde que el último respaldo completo se realizó e ignorará la existencia del respaldo de copia, por lo tanto una copia del respaldo no puede ser usada como la base para los respaldos diferencial y de registro de transacciones.

Prácticas de estrategia de toma de respaldos recomendadas

Localización de la copia de seguridad

Se recomienda que las copias de seguridad no sean almacenadas en la misma localización (unidad física) donde los archivos de la base de datos son almacenados. En casos donde una unidad física falle, use la unidad o una localización de red para realizar la restauración. Si la localización de un archivo no es especificada cuando se crea la base de datos, SQL Server restaurará los archivos de la base de datos en las localizaciones por defecto.

Note que cambiar las localizaciones por defecto no moverá los datos actuales y los archivos de registros a una nueva localización. Esto sólo será aplicable a las bases de datos creadas después de este cambio.

Copias de seguridad programadas y automatizadas

Para prevenir y respaldar de manera segura y confiable, es necesario configurar (automatizar) el proceso de respaldo a través de programas. Crear programas de copias de seguridad es importante, ya que con el tiempo las copias de seguridad actuales se vuelven obsoletas.

Manténgase protegido y esté seguro de que usted siempre tiene a mano una manera de restablecer sus datos hasta el punto donde la base de datos falló. Las copias de seguridad programadas proveen un historial de datos preciso.

La frecuencia especificada de los respaldos depende de las necesidades de negocio de la compañía, etc., y es definida por el Objetivo de Punto de Recuperación (Recovery Point Objective, RPO). Por ejemplo, si el Acuerdo a Nivel de Servicio (Service Level Agreement, SLA) de una organización especifica que no más de los datos de una hora atrás pueden ser perdidos de la base de datos, el RPO es una hora.

Copias de seguridad de prueba

La estrategia de respaldo y recuperación no puede ser completada hasta que las copias de seguridad son exitosamente restauradas para cumplir con todos los requerimientos y condiciones incluyendo todas las combinaciones que la estrategia de recuperación requiere. Hay una variedad de factores a considerar como: los requerimientos de la organización respecto del uso de los datos, la protección, etc.

Verificación de la copia de seguridad

Verificar la copia de seguridad asegura que la copia de seguridad es creada correctamente, físicamente intacta, que todos los archivos en la copia de seguridad son legibles y pueden ser restaurados en el evento que el usuario necesite usarla, y que todas las transacciones sean consistentes. Es importante entender que verificar la copia de seguridad no verifica la estructura de los datos. De todas maneras, si la copia de seguridad fue creada usando WITH CHECKSUMS, verificarla usando WITH CHECKSUMS puede proveer una buena indicación de la confiabilidad de los datos en la copia de seguridad.

Usando T-SQL:

Incluir la sentencia CHECKSUM asegura la consistencia de los datos en el destino de la base de datos. Para incluir CHECKSUM use la siguiente consulta:

BACKUP DATABASE [AdventureWorks2012]
TO  DISK = N'F:\Backup\AW12.bak'
WITH CHECKSUM;

SQL Server Management Studio también provee opciones para incluir en la verificación de la copia de seguridad una verificación CHECKSUM cuando se esté creando una tarea de respaldo:

Las opciones Verify backup when finished y Perform checksum before writing to media son usadas como un seguro de que la copia de seguridad y sus datos son consistentes.

También mostraremos cómo incluir verificaciones cuando programan copias de seguridad.

En este artículo crearemos una copia de seguridad programada en SQL Server usando un trabajo de SQL Server Agent y SQL Server Maintenance Plans.

Crear una copia de seguridad programada de SQL Server usando un trabajo de SQL Server Agent

Para automatizar y programar una copia de seguridad con SQL Server Agent:

1. En el panel Object Explorer, debajo del nodo SQL Server Agent, haga clic derecho en jobs y seleccione New job desde el menú contextual:

   

2. En el diálogo New Job ingrese el nombre del trabajo.
3. Debajo de la pestaña Steps, haga clic en el botón New y cree un paso de respaldo insertando una sentencia T-SQL. En este caso, la cláusula CHECKSUM tiene que ser incluida en el código T-SQL:

   USE AdventureWorks2012
   GO
   BACKUP DATABASE [AdventureWorks2012]
   TO  DISK = N'F:\Backup\AW12.bak'
   WITH CHECKSUM;
   

   Para crear una copia de seguridad diferencial use el siguiente script T-SQL:

   USE AdventureWorks2012
   GO
   BACKUP DATABASE [AdventureWorks2012]
   TO  DISK = N'F:\Backup\AW12.bak'
   WITH CHECKSUM;
   
   
   BACKUP DATABASE [AdventureWorks2012]
      TO  DISK = N'F:\Backup\AWD12.bak'
      WITH DIFFERENTIAL;
      WITH CHECKSUM;
   
   GO
   

   Para respaldar el registro de transacciones use el siguiente script:

   BACKUP LOG [AdventureWorks2012]
      TO  DISK = N'F:\Logs\AWD12.log';
   GO
   

   Note: Para crear una copia de seguridad del registro de transacciones o de la base de datos SQL Server, una copia de seguridad completa de la base de datos tiene que existir. Si la base de datos deseada nunca ha sido respaldada, antes de crear copias de seguridad diferenciales, primero cree una copia de seguridad completa. Las copias de seguridad de los registros de transacciones pueden ser usadas junto a la copia de seguridad completa de la base de datos. Por ejemplo, una copia de seguridad completa puede ser programada cada 24 horas, una copia de seguridad diferencial puede ser realizada cada 5 horas, y una copia de seguridad del registro de transacciones cada 15 minutos.

   

4. Haga clic en OK para añadir un paso, y haga clic en OK para crear un trabajo:

   

5. Para programar un trabajo, en el diálogo New Jobs, debajo de la pestaña Schedule haga clic en New.

6. En Job Schedule seleccione una frecuencia de ocurrencia y una fecha de inicio y haga clic en OK:

   

Para verificar un trabajo creado, en el panel Object Explorer y debajo de SQL Server Agent ➜ Jobs, haga clic derecho en el trabajo creado anteriormente y seleccione la opción Start job at step:

Para usar SQL Server Agent para respaldar todas las bases de datos en una instancia hay dos enfoques, los cuales requieren algo de trabajo manual. Un enfoque es crear un paquete SSIS usando la opción Backup Database Task de la barra de herramientas SSIS y crear un trabajo de SQL Server Agent para programarlo.

El otro enfoque es escribir un script T-SQL para respaldar todas las bases de datos en el diálogo SQL Server Agent Job Step.

Crear una copia de seguridad programada en SQL Server usando SQL Server Maintenance Plans

Hay dos opciones para crear una tarea de respaldo programado usando SQL Server Maintenance Plans: Creando manualmente un nuevo plan y usando Maintenance Plan Wizard.

Para crear manualmente una tarea de respaldo programado:

1. En el panel Object Explorer, debajo del nodo Management, haga clic derecho en Maintenance Plans y seleccione la opción New Maintenance Plan:

   

2. Desde la barra de herramientas Maintenance Plan Tasks seleccione Back Up Database Task:

   

   Maintenance Plan Wizard también provee la tarea Check database integrity que puede ser incluida en Maintenance Plan:

   

3. Haga doble clic en el plan añadido y establezca las opciones de respaldo:

   

4. Para programar un trabajo de SQL Server Agent use la opción Sub plan scheduling.
5. Cuando el plan sea creado, haga clic en Save y esta acción creará un trabajo correspondiente en la carpeta SQL Server Agent ➜ Jobs.

El método para crear un nuevo plan de mantenimiento es a través de Maintenance Plan Wizard guía al usuario a través del proceso, pero esta opción provee menos opciones granulares.

Para automatizar y programar una copia de seguridad usando SQL Server Maintenance Plan Wizard:

1. En el panel Object Explorer, debajo del nodo Management, haga clic derecho en Maintenance Plans y seleccione la opción Maintenance Plan Wizard:

   

2. En la ventana Select Plan Properties, especifique un nombre de plan. Para programar un trabajo de SQL Server Agent haga clic en el botón Change:

   

   

3. En Select Maintenance Plan Tasks seleccione las opciones Back Up Database y Check data integrity. La tarea Check data integrity realizar una verificación interna de consistencia de los datos y las páginas del índice dentro de la base de datos:

   

4. En la siguiente ventana configure la tarea de mantenimiento especificando la base de datos para respaldar y las opciones de respaldo. En la ventana Define Back Up Database Task también seleccione la opción Verify backup integrity:

   

5. Después de verificar las selecciones y acciones haga clic en Finish:

   

   

Los Planes de Mantenimiento son más apropiados para Administradores de Bases de Datos con menos experiencia porque proveen una Interfaz Gráfica fácil de usar, y no requieren scripts de mantenimiento manualmente escritos. La desventaja de los Planes de Mantenimiento es que las tareas que proveen son básicas y no dejan espacio a la personalización.

Un Plan de Mantenimiento es también atómico y por lo tanto no puede correr múltiples tareas. Cada tipo de tarea de mantenimiento dentro de un Plan de Mantenimiento puede ser configurada sólo para correr una vez dentro de ese Plan. Por ejemplo, si una tarea que es hecha para eliminar archivos de copias de seguridad antiguos, sólo eliminará un tipo de archivo al mismo tiempo. Debido a esto, múltiples Planes de Mantenimiento tiene que ser creados sólo para realizar una sola tarea, en algunos casos, y cada Plan de Mantenimiento tiene que tener un correspondiente trabajo de SQL Server Agent a ser programado.

Traductor: Daniel Calbimonte