En el capítulo de apertura del libro de Craig Mullin, Administración de bases de datos, dice «En muchos sentidos, los negocios de hoy son datos». Dentro de la mayoría de las organizaciones, la persona responsable de proteger los datos es el administrador de la base de datos, usted.

Así es; todo el negocio está en sus manos capaces, ejecutándose en ese servidor que nunca se bloquea, con todos esos usuarios finales que nunca cometen errores usando aplicaciones, construidas por aquellos desarrolladores que escriben código impecable la primera vez, cada vez, con la ayuda capaz de esa nueva cooperativa que tiene privilegios de ‘sa’ gracias a su jefe.

OK. Deja de llorar. Hay cosas que puede hacer para proteger los datos de SQL Server bajo su cuidado y una de las más importantes es ejecutar copias de seguridad de bases de datos regulares.

Copias de seguridad

Microsoft, en SQL Server Books Online, define las copias de seguridad como:

Una copia de datos que se utiliza para restaurar y recuperar datos después de un fallo del sistema

Las copias de seguridad SQL se pueden crear de varias maneras y pueden incorporar todos o algunos de los datos, así como parte del registro de transacciones. Si bien este artículo se centra en la sintaxis de 2005, la mayoría de los conceptos son aplicables a 2000. Este es un gran tema. En el mejor de los casos, voy a arañar la superficie y darte suficiente información para que no empieces a llorar de nuevo. Después de leer esto, debería poder configurar un conjunto razonable de copias de seguridad para su sistema.

Modelos de recuperación

Para comenzar a trabajar en copias de seguridad, las necesidades empresariales definen un modelo de recuperación de base de datos. En esencia, un modelo de recuperación define lo que va a hacer con los datos del registro de transacciones.

Hay tres modelos de recuperación: Completo, Simple y con registro masivo. Estos son bastante fáciles de definir:

• Simple: en el modo de recuperación simple, el registro de transacciones no está respaldado, por lo que solo puede recuperarse con la copia de seguridad completa o diferencial más reciente.
• Completo: en el modo de recuperación completa, realiza una copia de seguridad de la base de datos y el registro de transacciones para que pueda recuperar la base de datos en cualquier momento.
• En modo de registro masivo, la mayoría de las transacciones se almacenan en el registro de transacciones, pero algunas operaciones masivas, como cargas masivas o creación de índices, no se registran.

Los dos modos más utilizados son Simples y completos. No asuma necesariamente que, por supuesto, siempre necesita usar la recuperación completa para proteger sus datos. Es una decisión de negocios. El negocio le dirá si necesita recuperarse hasta un punto en el tiempo o si simplemente necesita la última copia de seguridad completa. Va a definir si sus datos se pueden recuperar por otros medios, como la entrada manual, o si tiene que proteger tanto como sea posible a medida que se cruza el cable. Utiliza Recuperación simple si puede permitirse perder los datos almacenados desde la última copia de seguridad completa o diferencial y/o simplemente no necesita recuperación hasta un punto en el tiempo. En el modo simple, debe restaurar todos los grupos de archivos de lectura/escritura secundarios al restaurar el principal. Se usa Simple principalmente en bases de datos secundarias que no son una parte vital absoluta de la empresa o los sistemas de informes, con acceso de solo lectura, por lo que no hay un registro de transacciones del que preocuparse de todos modos. Utiliza Full si cada bit de los datos es vital, necesita recuperarse hasta un punto en el tiempo o, generalmente en el caso de bases de datos muy grandes (VLDB), necesita restaurar archivos individuales y grupos de archivos independientemente de otros archivos y grupos de archivos.

Con modelos de recuperación simples y completos, ahora puede ejecutar una copia de seguridad de solo copia que le permite copiar la base de datos a un archivo de copia de seguridad, pero no afecta el registro, los programas de copia de seguridad diferenciales ni la recuperación de impacto a un punto en el tiempo. Trataré de profundizar en tantos de estos temas como sea posible a través del artículo, pero no en los archivos y grupos de archivos.

Trabajar con recuperación simple

Basta de hablar. Vamos a empezar a ejecutar copias de seguridad. Supongamos que estamos en recuperación simple en una base de datos de tamaño pequeño a mediano. Voy a usar AdventureWorks para todos los scripts de muestra. Para configurarlo en recuperación simple:

Su estrategia de copia de seguridad más sencilla es ejecutar, a intervalos regulares, el siguiente comando de copia de seguridad de SQL Server, que realizará una copia de seguridad completa de la base de datos:

¿Qué pasa con todo el trabajo de escritura que usted pide? No tenemos herramientas GUI para manejar el trabajo por nosotros? Sí, la mayoría de las copias de seguridad simples se pueden realizar con SQL Server Management Studio. Sin embargo, si desea aprender y comprender lo que Management Studio está haciendo por usted, o si desea un control preciso sobre lo que se respalda, cómo y dónde, entonces tendrá que abrir el teclado y guardar el mouse.

El comando anterior precipitará una copia de seguridad básica en el disco. La mayoría de los DBA que conozco hacen copias de seguridad para archivar y luego raspan los archivos en una cinta o en algún otro medio. Esto se debe a que los archivos en el disco son simples y rápidos de recuperar, mientras que los medios a veces pueden ser un poco molestos. Por ejemplo, generalmente tenemos dos o tres días de copias de seguridad en nuestros sistemas de archivos para la recuperación inmediata. Solo vamos a los sistemas de cinta si necesitamos ejecutar restauraciones para copias de seguridad antiguas.

¿Qué hizo ese comando? Hizo una copia de todos los datos confirmados en la base de datos. También copió entradas de registro no comprometidas. Estos se utilizan durante la recuperación para confirmar o revertir los cambios que se produjeron en los datos durante el proceso de copia de seguridad.

Copias de seguridad de solo copia

Normalmente, la copia de seguridad de una base de datos afecta a otros procesos de copia de seguridad y restauración. Por ejemplo, después de ejecutar el comando anterior, cualquier copia de seguridad diferencial (una copia de seguridad que solo copia los datos modificados desde la última copia de seguridad) usaría esto como punto de partida para los cambios de datos, no la copia de seguridad que ejecutó anoche. Como se señaló anteriormente, SQL 2005 introduce un nuevo concepto de copias de seguridad, copias de seguridad COPY_ONLY, que nos permiten evitar interrumpir el ciclo:

Ya hemos encontrado uno de esos momentos más detallados en los que el Estudio de Gestión no te ayudaría. Si desea una copia de seguridad de solo copia, debe usar la línea de comandos.

Copias de seguridad diferenciales

Supongamos por un momento que todavía estamos en recuperación simple, pero que estamos tratando con una base de datos más grande, digamos algo por encima de los 100 GB de tamaño. Las copias de seguridad completas en realidad pueden comenzar a ralentizar un poco el proceso. En su lugar, después de consultar con la empresa, hemos decidido hacer una copia de seguridad completa semanal y copias de seguridad diferenciales diarias. Las copias de seguridad diferenciales solo respaldan las páginas de datos que han cambiado desde la última copia de seguridad completa. A continuación se muestra el comando SQL backup para realizar una copia de seguridad diferencial:

Ahora, si tuviéramos que restaurar esta base de datos, primero iríamos a la última copia de seguridad completa, la restauraríamos y luego restauraríamos las copias de seguridad diferenciales en orden (más sobre eso más adelante).

Hay una serie de otras opciones de respaldo que no detallaré aquí. Lea los libros en línea para ver detalles sobre TAMAÑO DE BLOQUE, VENCIMIENTO, DÍAS DE RETENCIÓN, CONTRASEÑA, NOMBRE, ESTADÍSTICAS, etc.

También puede ejecutar una instrucción que compruebe la integridad de una copia de seguridad de la base de datos. No verifica la integridad de los datos dentro de una copia de seguridad, pero verifica que la copia de seguridad esté formateada correctamente y sea accesible.

Recuperación completa y copias de seguridad de registros

Hemos estado trabajando principalmente en una base de datos que estaba en modo de recuperación simple (esto solía llamarse Truncar el registro en el punto de control). En este modo, no hacemos copias de seguridad de los registros de transacciones para su posterior recuperación. Cada copia de seguridad bajo este mecanismo es una copia de seguridad de la base de datos. Las copias de seguridad de registros simplemente no son posibles.

Sin embargo, solo ha protegido los datos a partir de la última copia de seguridad válida, ya sea completa o diferencial. Cambiemos nuestras suposiciones. Ahora estamos tratando con una gran aplicación y base de datos de misión crítica. Queremos poder recuperar esta base de datos hasta el último minuto. Este es un punto muy importante. En teoría, dado que las entradas de registro se almacenan y respaldan, estamos protegidos hasta el punto de cualquier fallo. Sin embargo, algunos fallos pueden causar daños en el registro, lo que hace imposible la recuperación hasta un punto en el tiempo. Por lo tanto, tenemos que determinar cuál será el tiempo mínimo razonable entre copias de seguridad de registros. En este caso, podemos vivir con no más de 15 minutos de datos perdidos.

Entonces, comencemos por poner nuestra base de datos en modo de recuperación COMPLETA:

Luego, de forma programada, en este caso cada 15 minutos, ejecutaremos el comando SQL backup para el registro de transacciones:

Este script hará una copia de seguridad de las transacciones confirmadas desde el registro de transacciones. Tiene marcadores en el archivo que muestran la hora de inicio y parada. Truncará el registro cuando se complete correctamente, eliminando del registro de transacciones las transacciones confirmadas que se hayan escrito en el archivo de copia de seguridad. Si es necesario, puede usar la instrucción WITH NO_TRUNCATE para capturar datos del registro de transacciones independientemente del estado de la base de datos, suponiendo que esté en línea y no en estado de EMERGENCIA. Esto es solo para emergencias.

Tenga en cuenta que no estamos utilizando la instrucción INIT en este caso, pero puede hacerlo si lo desea. Al hacer copias de seguridad de registros, tiene opciones:

1. Ejecute todas las copias de seguridad en un solo archivo, donde se apilarán y todo lo que tiene que hacer, en la restauración (cubierto más adelante), es recorrerlas en ciclo.
2. Asigne un nombre único a las copias de seguridad, probablemente utilizando la fecha y la hora en la cadena.

En este último caso, seguridad dice, use INIT porque está ejerciendo el máximo control sobre lo que se copia de seguridad en dónde, y podrá saber exactamente qué es una copia de seguridad, cuándo se tomó y de dónde según el nombre. Este es otro lugar donde operar copias de seguridad desde la línea de comandos le da más control que la interfaz gráfica de usuario. Hemos utilizado ambos enfoques en nuestros sistemas por diferentes razones. Usted puede decidir qué es lo mejor para sus requisitos de tecnología y negocios.

La mayoría de las opciones disponibles para la copia de seguridad de la base de datos se incluyen en la copia de seguridad del registro, incluida COPY_ONLY. Esto le permitiría capturar un conjunto de datos de transacción sin afectar el registro o la próxima copia de seguridad de registro programada. Esto sería útil para llevar los datos de producción a otro sistema para solucionar problemas, etc.

Si tiene la base de datos configurada en Recuperación COMPLETA, debe ejecutar copias de seguridad de registros. A veces, la gente se olvida y el registro de transacciones crece hasta el punto de llenar la unidad de disco. En este caso, puede ejecutar:

Al adjuntar NO_LOG a la copia de seguridad del registro, y no especificar una ubicación para el registro, se elimina la parte inactiva del registro y lo hace sin una entrada de registro, derrotando así a la unidad de disco completa. Esto no se recomienda en absoluto porque rompe la cadena de registros, la serie de copias de seguridad de registros de las que recuperaría su base de datos hasta un punto en el tiempo. Microsoft recomienda ejecutar una copia de seguridad completa inmediatamente después de usar esta instrucción. Además, advierten que esta declaración puede ser obsoleta en una versión futura.

Restaurar bases de datos

Aunque las copias de seguridad de SQL Server son importantes y vitales, son inútiles sin la capacidad de restaurar la base de datos.

Restaurar una copia de seguridad completa de la base de datos

Restaurar una copia de seguridad completa de la base de datos es tan simple como crearla:

Es realmente así de simple, a menos que, como nosotros, estemos haciendo una copia de seguridad de todo en un archivo como si fuera un dispositivo de copia de seguridad. En ese caso, deberá especificar a qué archivo dentro del» dispositivo » está accediendo. Si no sabes qué archivo, tendrás que generar una lista:

Esto le dará la misma lista como mostré arriba desde Management Studio. Así que ahora, si quisiéramos restaurar el segundo archivo del grupo, la copia de seguridad COPY_ONLY, emitiríamos el siguiente comando:

Desafortunadamente, si lo estás siguiendo, es posible que encuentres que acabas de generar este error:

Lo que esto significa es que su base de datos está en modo de recuperación completa, pero no ha realizado una copia de seguridad de la «cola del registro», lo que significa que las transacciones ingresadas desde la última vez que ejecutó una copia de seguridad. Puede anular este requisito si cambia la sintaxis anterior a:

Es la primera vez que apilamos las cláusulas WITH (CON FILE=2 y CON REPLACE se representa como CON FILE=2, REPLACE), pero no será la última. Lea los libros en línea. La mayoría de las sentencias de la cláusula WITH se pueden usar en combinación con las demás.

¿Qué sucede si queremos restaurar a una base de datos diferente a la original? Por ejemplo, queremos hacer una copia de nuestra base de datos a partir de una copia de seguridad separada. Tal vez queramos moverlo a un servidor de soporte de producción donde vamos a trabajar en él, separado de la copia de producción de la base de datos. Si tomamos el enfoque simple, bien, pruebe esto:

En este caso, debería ver toda una serie de errores relacionados con archivos que no se sobrescriben. Realmente puede crear nuevas bases de datos a partir de copias de seguridad, pero si lo hace en un servidor con la base de datos existente, deberá cambiar la ubicación de los archivos físicos utilizando los nombres lógicos. Para conocer los nombres lógicos de los archivos de una base de datos determinada, ejecute esto antes de intentar mover los archivos:

Esto se puede usar para identificar los nombres lógicos apropiados para generar este script:

Restauración de una copia de seguridad diferencial

El último método es aplicar la copia de seguridad diferencial. Esto requiere dos pasos. Primero, restauraremos la base de datos, pero con un giro y luego aplicaremos la copia de seguridad diferencial:

La mayor parte de esto probablemente se explique por sí mismo en base a lo que ya hemos cubierto. La única arruga es la inclusión de la palabra clave NORECOVERY. De manera muy sencilla, durante una restauración, las transacciones pueden haber comenzado durante el proceso de copia de seguridad. Al final de una restauración, las transacciones completadas se transfieren a la base de datos y las transacciones incompletas se devuelven. La configuración de NORECOVERY mantiene abiertas las transacciones. Esto permite que el siguiente conjunto de transacciones se recoja de la siguiente copia de seguridad en orden.

Nos ocupamos principalmente de copias de seguridad y restauraciones simples en este artículo, pero una restauración más avanzada en 2005 permite restaurar grupos de archivos secundarios mientras la base de datos está en línea. Su grupo de archivos principal debe estar en línea durante la operación. Esto será más útil para sistemas de bases de datos muy grandes.

Restaurar bases de datos de SQL Server a un punto en el tiempo

Restaurar registros no es mucho más difícil que la restauración diferencial de bases de datos que acabamos de completar. Hay un poco más involucrado en restaurar un momento en el tiempo. Suponiendo que usted copia de seguridad de sus registros a un archivo o dispositivo:

De lo contrario, simplemente vaya y obtenga los nombres de archivo que necesita. Primero ejecute la restauración de la base de datos, teniendo cuidado de dejarla en un estado no recuperado. Siga esto con una serie de restauraciones de registros a un punto en el tiempo.

Ahora lo que tenemos es una base de datos que está hasta la última transacción confirmada exacta a las 14:30:29 del 23 de octubre. Recuerde, durante las restauraciones de varios pasos como esta, debe dejar la base de datos en estado de recuperación. Eso significa agregar NORECOVERY a cada instrucción hasta que haya completado el proceso de restauración. Si por alguna razón ha agregado NORECOVERY a todas sus declaraciones, o simplemente se detiene en el medio y desea volver a poner la base de datos en línea, puede usar esta declaración para completar el proceso:

Instantáneas de base de datos

SQL Server 2005 introdujo el concepto de una instantánea, o una vista estática de solo lectura de una base de datos. Las instantáneas se crean principalmente para proporcionar una versión de solo lectura de una base de datos con fines de generación de informes. Sin embargo, funcionan de manera similar a las copias de seguridad. La única diferencia principal es que todas las transacciones no comprometidas se revierten. No hay opción para avanzar, capturar registros, etc., que proporcionan las copias de seguridad, ni se utilizan muchos recursos de SQL Server en absoluto. Más bien, la tecnología de disco se utiliza para crear una copia de los datos. Debido a esto, son mucho más rápidos que las copias de seguridad, tanto para crear como para restaurar.

NOTA:
Para obtener más información sobre la instantánea SQL 2005, consulte http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/.

Un buen uso de las instantáneas, además de los informes, podría ser crear una antes del mantenimiento después de haber eliminado todos los usuarios activos (y sus transacciones) del sistema. Si bien las instantáneas no admiten la volatilidad de las copias de seguridad en vivo, su velocidad y facilidad de recuperación son una gran herramienta para una recuperación rápida de una implementación fallida. Las instantáneas se almacenan en el servidor, por lo que debe asegurarse de tener el almacenamiento adecuado.

La sintaxis es diferente porque no está haciendo una copia de seguridad de una base de datos; está creando una nueva:

Ahora será accesible para acceso de sólo lectura. Dado que nos preocupa principalmente usar esto como mecanismo de copia de seguridad, incluyamos el método para revertir una base de datos a una instantánea de base de datos.

En primer lugar, identifique la instantánea que desea utilizar. Si hay más de una en cualquier base de datos que va a revertir, deberá eliminar todas excepto la que está utilizando:

A continuación, puede revertir la base de datos ejecutando una instrucción RESTORE (metáforas mixtas, no es buena):

Eso es. En mi sistema, ejecutar las instantáneas de la base de datos de Adventureworks tomó 136 ms. La copia de seguridad completa tomó 5670 ms. La restauración de la instantánea tomó 905 ms y la restauración de la base de datos tomó 13382 ms. Incorporar esto en un proceso de implementación de producción podría resultar en beneficios significativos

De nuevo, vale la pena señalar que hay algunas advertencias al usar la instantánea. Debe tener suficiente espacio en disco para una segunda copia de la base de datos. Debe tener cuidado con las instantáneas, ya que la mayor parte de la sintaxis es similar a la utilizada por las propias bases de datos. Por último, mientras haya instantáneas adjuntas a una base de datos, no puede ejecutar una restauración desde una copia de seguridad de la base de datos de esa base de datos.

Prácticas recomendadas

La forma en que realice las copias de seguridad de la base de datos no debe ser una decisión técnica. Debe ser dictado por el negocio. Los sistemas pequeños con tasas de transacción bajas y/o sistemas de informes que se cargan regularmente solo necesitarán una copia de seguridad completa de la base de datos. Los sistemas de tamaño mediano y los sistemas grandes se vuelven dependientes del tipo de datos administrados para determinar qué tipos de copias de seguridad se requieren.

Para un sistema de tamaño mediano, una copia de seguridad diaria con copias de seguridad de registro durante el día probablemente respondería a la mayoría de los requisitos de datos de manera oportuna.

Para una base de datos grande, el mejor enfoque es mezclar y combinar las copias de seguridad para garantizar la máxima recuperabilidad en un tiempo mínimo. Por ejemplo, ejecute una copia de seguridad completa semanal. Dos veces al día durante la semana, ejecute un respaldo diferencial. Cada 10 minutos durante el día, ejecute una copia de seguridad de registro. Esto le da una gran cantidad de mecanismos de recuperación.

Para bases de datos muy grandes, deberá comenzar a ejecutar grupos de archivos y copias de seguridad de archivos porque puede que no sea posible realizar una copia de seguridad completa o incluso una copia de seguridad diferencial de la base de datos completa. Una serie de funciones adicionales están disponibles para ayudar en esta área, pero no voy a entrar en ellas aquí.

Debería tomarse el tiempo para desarrollar algunos scripts para ejecutar sus copias de seguridad y restauraciones. Una convención de nombres para que sepa qué base de datos, desde qué servidor, desde qué fecha, en qué copia de seguridad y formato específicos será muy propicia para su cordura. Se debe definir una ubicación común para copias de seguridad, registros, completos o incrementales. Todos los responsables deben estar capacitados en copia de seguridad y recuperación y solución de problemas al mismo tiempo. Hay muchas maneras de hacer esto, pero puedes encontrar algunas sugerencias en Copias de seguridad Pop y restauraciones Pop.

La prueba real es ejecutar sus mecanismos de copia de seguridad y luego ejecutar una restauración. A continuación, intente un tipo diferente de restauración, y otro, y otro. Asegúrese de que no solo ha realizado la diligencia debida para definir cómo hacer una copia de seguridad del sistema, sino que ha hecho el paso adicional de asegurarse de que puede recuperar esas copias de seguridad. Si no ha practicado esto y documentado la práctica y luego probado el documento, en efecto, no está listo para un desastre.

Resumen

Las copias de seguridad dentro de su empresa deben ser como votar en Chicago, temprano y a menudo. Configurar copias de seguridad básicas es bastante simple. Agregar copias de seguridad y diferenciales de registros también es fácil. Explore las opciones para ver cómo agregar copias de seguridad y restauraciones de archivos y grupos de archivos para aumentar la velocidad de sus copias de seguridad y restauraciones, lo que aumentará la disponibilidad del sistema y el tiempo de actividad. Mantenga un estándar de nomenclatura común. Tenga cuidado al usar instantáneas, pero sin duda emplearlas. Almacene sus archivos en una ubicación estándar entre servidores. Practica tus recuperaciones. Finalmente, para hacer que sus copias de seguridad realmente suenen, descargue una prueba gratuita de la copia de seguridad SQL de Red Gate.¢. Ofrece compresión de alto rendimiento y resiliencia de red para que el proceso de escritura o copia de copias de seguridad a través de redes escamosas sea tolerante a fallos.