Creando una copia de seguridad en el servidor SQL. Configuración de copias de seguridad periódicas de la base de datos de MS SQL Server. Restaurar una base de datos desde una copia de seguridad

La amplia funcionalidad de Bacula Enterprise Edition, entre otras cosas, le permite crear rápida y fácilmente copias de seguridad de bases de datos para archivos . Por ejemplo, estamos hablando de una herramienta con la que puedes hacer una copia de seguridad de MS SQL Server. El usuario puede realizar una copia de seguridad de MS SQL creando copias de seguridad de gran volumen de bases de datos MS SQL específicas utilizadas por la plataforma Windows, a menores costos que el software de terceros, con la capacidad de restaurar datos a un determinado momento en el tiempo (recuperación PITR ) a una red y a una unidad local.

El script de Bacula Systems para crear copias de seguridad de MS SQL Server se caracteriza por su extrema eficiencia, lograda mediante la implementación de una arquitectura moderna y altamente confiable. Además, el software le permite realizar una copia de seguridad de MS SQL Server y utilizar una variedad de opciones para crear copias de seguridad de MS SQL.

El script de copia de seguridad de MS SQL Bacula Systems funciona independientemente de VSS. Esto significa que la herramienta de copia de seguridad de MS SQL no utiliza instantáneas de VSS para crear copias de seguridad. Por lo tanto, el usuario puede establecer el siguiente valor "Habilitar VSS = no" en Bacula FileSet. La creación efectiva de copias de seguridad de MS SQL Server y su restauración utilizando esta solución se logra mediante el uso de la API de Microsoft para SQL Server. Esto permite a Bacula Systems soportar los mecanismos de seguridad y todos los tipos de autenticación implementados en Microsoft SQL Server.

Copia de seguridad del registro de transacciones de MS SQL y recuperación de un punto en el tiempo de MS SQL: el software Bacula Enterprise Edition le permite recuperar bloques de datos de MS SQL o configuraciones específicas en un momento específico. Con la implementación de modelos de recuperación completos y de registros masivos, puede recuperar MS SQL usando la recuperación PITR o usar LSN para restaurar el sistema a un estado específico. Puede restaurar un estado específico de una base de datos MS SQL en cualquier momento específico, hasta el segundo. En el caso de una copia de seguridad del registro de transacciones de MS SQL, al restaurar, el estado de la base de datos se restaurará a partir de varias copias de seguridad seleccionadas.

Características de un vistazo
 copia de seguridad y recuperación automática de MS SQL con Bacula Enterprise

Bacula Systems ha creado un complemento de respaldo de MS SQL Server para usar con Bacula Enterprise Edition. La copia de seguridad de MS SQL Server con Bacula tiene las siguientes características:

• Admite copias de seguridad completas y diferenciales de MS SQL
• Soporte de copia de seguridad incremental de MS SQL
• Copia de seguridad de MS SQL en la red y en la unidad local
• Copia de seguridad programada de MS SQL
• Crear copias de seguridad a nivel de base de datos de MS SQL Server
• Capacidad para incluir/excluir bases de datos del procedimiento de creación de copias de seguridad
• Soporte para crear copias de seguridad de bases de datos de solo lectura
• Restaurar copias de seguridad de MS SQL en el disco
• Envío de una secuencia de respaldo directamente al demonio de almacenamiento
• Recuperación puntual de MS SQL

Revisión y configuración de respaldo MS SQL 2008, 2008 R2, 2012 y 2014

Este documento proporciona soluciones para Bacula Enterprise Edition 8.4 y posteriores que no son compatibles con versiones anteriores del software. La copia de seguridad de la base de datos MS SQL ha sido probada y es compatible con MS SQL 2003 R2, MS SQL 2008 R2, MS SQL 2012, MS SQL 2005, MS SQL 2008, MS SQL 2014. La copia de seguridad de MS SQL de Bacula puede funcionar con SQL Express.

Glosario de copia de seguridad de MS SQL 2008, 2008 R2, 2012 y 2014

• Microsoft SQL significa Microsoft SQL Server.
• Registro de transacciones. Cualquier base de datos de MS SQL Server tiene un registro de transacciones, que registra todas las transacciones y modificaciones de la base de datos realizadas durante dichas transacciones. El registro de transacciones es un elemento importante de la base de datos. En caso de una falla del sistema, es posible que se requiera el registro de transacciones para restaurar la base de datos a un estado de funcionamiento. Puede encontrar más información en https://msdn.microsoft.com/en-us/library/ms190925.aspx.
• Backup diferencial de base de datos MS SQL Server. La copia de seguridad diferencial se basa en la última completa. Durante una copia de seguridad diferencial, solo se capturan los datos que han cambiado desde que se creó la última copia de seguridad completa. Puede encontrar más información en https://msdn.microsoft.com/en-us/library/ms175526.aspx.
• Copia de seguridad completa de la base de datos de MS SQL Server. Durante una copia de seguridad completa de la base de datos, se crea una copia de seguridad de toda la base de datos. La copia de seguridad incluye parte del registro de transacciones con el fin de restaurar la base de datos completa desde la copia de seguridad. Las copias de seguridad completas de la base de datos contienen la base de datos en el momento en que se completó la copia de seguridad. Puede encontrar más información en https://msdn.microsoft.com/en-us/library/ms186289.aspx.
• Copia de seguridad “solo copia” (CopyOnly). Las copias de seguridad de solo copia son copias de seguridad de MS SQL que son independientes del flujo normal de las copias de seguridad tradicionales de SQL Server. A veces es útil crear copias de seguridad para necesidades específicas sin afectar el proceso general de copia de seguridad y recuperación de la base de datos. Puede encontrar más información en https://msdn.microsoft.com/en-us/library/ms191495.aspx.
• VDI(Virtual Device Interface) es una tecnología de Microsoft que le permite crear tubería con nombre entre programas.
• Las máscaras estándar especifican conjuntos de cadenas con comodines. Por ejemplo, la máscara de producción estándar* incluirá las líneas producción1 y producción2.
• línea
• entero.
• LSN Cada entrada en el registro de transacciones de MS SQL Server se identifica mediante un número de serie de transacción (LSN) único. Puede encontrar información más detallada en https://technet.microsoft.com/en-us/library/ms190411%28v=sql.105%29.aspx.

Copia de seguridad de MS SQL Server 2008, 2008 R2, 2012 y 2014

Copia de seguridad completa de las bases de datos de MS SQL Server 2008, 2008 R2, 2012 y 2014

Durante una copia de seguridad completa de una base de datos MS SQL, se guardan los archivos de la base de datos y el registro de transacciones, lo que le permite proteger completamente la base de datos MS SQL en caso de falla de los medios. Si uno o más archivos están dañados, restaurar la base de datos MS SQL desde una copia de seguridad le permitirá restaurar todas las transacciones completadas. También se revertirán todas las transacciones que estaban en curso. En este modo, se crean copias de seguridad de las bases de datos maestra y mbdb.

Backup diferencial de bases de datos MS SQL Server 2008, 2008 R2, 2012 y 2014

La copia de seguridad diferencial de la base de datos de MS SQL Server se basa en la copia de seguridad completa más reciente de la base de datos de MS SQL. Al crear una copia de seguridad diferencial de MS SQL, solo se capturan los datos que se han modificado desde que se creó la última copia de seguridad completa de MS SQL. Para la función de copia de seguridad diferencial de MS SQL, la secuencia de las copias de seguridad es extremadamente importante. Si por alguna razón la copia de seguridad completa a la que hace referencia MS SQL no está disponible, no se pueden utilizar copias de seguridad diferenciales de la base de datos de MS SQL Server. MS SQL Backup de Bacula utiliza técnicas específicas para resolver este problema. Por lo tanto, si surgen dificultades, el estado de una copia de seguridad diferencial de la base de datos se puede actualizar automáticamente a una copia de seguridad completa.

Copia de seguridad del registro de transacciones de MS SQL 2008, 2008 R2, 2012 y 2014

Configurar la copia de seguridad de MS SQL y la configuración de la base de datos

Restaurar una base de datos MS SQL desde una copia de seguridad

Puede utilizar todos los métodos estándar para iniciar el procedimiento de restauración de una base de datos MS SQL desde una copia de seguridad. Sin embargo, debe asegurarse de que, en caso de restaurar datos diferenciales, también se restaurará la copia de seguridad anterior completa de la base de datos MS SQL. En este caso, la recuperación se produce automáticamente si la ejecuta en la consola. bconsola utilizando las opciones de recuperación 5 o 12. En la estructura de archivos generada, debe marcar la recuperación de bases de datos completas o instancias de bases de datos.

Opciones para restaurar una base de datos MS SQL desde una copia de seguridad

El software Bacula Enterprise Edition permite a los usuarios utilizar múltiples opciones de recuperación de MS SQL y aplicar una variedad de métodos de reversión de bases de datos. Las opciones de recuperación más utilizadas se describen a continuación:

• Parámetro Where: En el caso de Bacula Enterprise Edition, este parámetro permite al administrador restaurar la base de datos en una ubicación específica.
• Reemplazar parámetro: Se utiliza para definir cómo debe comportarse Bacula con la base de datos actual cuando se restaure. La copia de seguridad de MS SQL de Bacula también le permite utilizar varias opciones más al restaurar, como por ejemplo:
• Instancia: dado que MS SQL utiliza varias instancias, la copia de seguridad de la base de datos MS SQL de Bacula le permite elegir qué instancia restaurar. Este parámetro es opcional y, si no se especifica, el valor especificado al crear la copia de seguridad se utilizará al restaurar. De forma predeterminada, se utiliza una instancia denominada "MSSQLSERVER".
• Base de datos. Esta opción especifica el nombre de la base de datos a restaurar y utiliza el valor especificado en el momento en que se creó la base de datos. Este parámetro es opcional. De forma predeterminada, las copias de seguridad de la base de datos de SQL Server utilizan el parámetro Dónde para determinar el nombre de la nueva base de datos. Si a los parámetros Dónde y Base de datos se les asigna un nombre de base de datos válido, se utilizará el parámetro Base de datos.
• Usuario. El nombre de usuario utilizado para conectarse a la instancia de la base de datos MS SQL. Este parámetro es opcional y, si no se especifica, el valor especificado al crear la copia de seguridad se utilizará al restaurar.
• Contraseña. Contraseña utilizada para conectarse a la instancia de la base de datos MS SQL. Este parámetro es opcional y, si no se especifica, el valor especificado al crear la copia de seguridad se utilizará al restaurar.
• Dominio. El dominio utilizado para conectarse a la instancia de la base de datos MS SQL. Este parámetro es opcional y, si no se especifica, el valor especificado al crear la copia de seguridad se utilizará al restaurar.
• Recuperación. El parámetro le permite determinar si la base de datos volverá a su estado anterior durante la recuperación o no. De forma predeterminada, al restaurar una base de datos, volverá al estado anterior.
• Stop_before_mark. Condición CON STOPBEFOREMARK = Se utiliza para indicar que la entrada del registro de transacciones inmediatamente anterior al indicador es el punto de restauración. El punto de recuperación puede ser una fecha y hora, un LSN o un indicador mark_name.
• Parada_en_marca. Condición CON MARCA DE PARADA = Se utiliza para indicar que la transacción marcada es un punto de recuperación. STOPATMARK avanza hasta la bandera y reproduce la transacción marcada. El punto de recuperación puede ser una fecha y hora, un LSN o un indicador mark_name.
• Parar_en= . Condición CON PARADA = se utiliza para indicar que el punto de restauración es fecha/hora.
• Restringir_usuario. La cláusula FROM RESTRICT_USER se utiliza para restringir el acceso a la base de datos restaurada. El valor predeterminado es no.

La restauración de MS SQL a un momento determinado se puede realizar directamente desde el complemento de copia de seguridad de MS SQL. También puede restaurar archivos localmente y realizar operaciones desde Microsoft SQL Server Management Console para obtener más funciones.

LSN

El número LSN de la entrada de registro en la que ocurrió un evento de copia de seguridad y recuperación específico se puede ver de una de las siguientes maneras:

• Al mostrar una descripción de tareas para crear copias de seguridad usando el software Bacula
• En el nombre del archivo de registro
• En la tabla msdb.backupset
• En la tabla msdb.backupfile

Al realizar una tarea para crear una copia de seguridad de una base de datos MS SQL, se mostrará la siguiente información sobre los números LSN al mostrar la descripción de la tarea:

Número Primera LSN corresponde al último número LSN de la última copia de seguridad del registro de transacciones. Dicha copia de seguridad puede ser la primera copia de seguridad completa o la última copia de seguridad (incremental).

Número último LSN coincide con la última transacción registrada en el diario.

En el caso de una copia de seguridad del registro de transacciones (incremental), el nombre del archivo asociado con esta base de datos en la tarea de creación de una copia de seguridad incremental se verá así:

El número en el nombre, en nuestro caso. 42000162001, corresponde al último número LSN de la tarea anterior (para crear una copia de seguridad completa o incremental).

Figura 2: Primer LSN, último LSN y LSN en nombres de archivos

Como se muestra en el ejemplo de la Figura 2, si el administrador necesita restaurar la base de datos MS SQL a un estado correspondiente al número LSN 14, se pueden realizar las siguientes acciones:

• En el menú de recuperación de la base de datos, use la opción 5
• Seleccione el último archivo de copia de seguridad completa “data.bak” (LSN: 10)
• Seleccione la copia de seguridad incremental "log-10.trn"

O, si la última copia de seguridad completa de MS SQL Server no está disponible, pero sí la copia de seguridad completa anterior, entonces:

• Utilice la opción de restauración 3, seleccione los valores de Jobids apropiados
• Seleccione el directorio de la base de datos “/@mssql/db29187”
• Seleccione el archivo de copia de seguridad completo “data.bak” (LSN: 2)
• Seleccione copias de seguridad incrementales “log-2.trn”, “log-3.trn”, “log-10.trn”
• Establezca el parámetro stop_at_mark en "lsn:14"
• Ejecute la tarea para restaurar la copia de seguridad.

Scripts de recuperación de MS SQL

Descripción	Dónde	Base de datos	Ejemplo
Recuperar archivos al disco	Camino		donde=c:/tmp
Restaurar base de datos original			donde=/
Restaurar con nuevo nombre	Nombre		donde = nueva base de datos
Restaurar con nuevo nombre		Nombre	base de datos = nueva base de datos
Recuperar con nuevo nombre y mover archivos		Nombre

Tabla 1: escenarios de recuperación de MS SQL

2.3.1 Restaurar una base de datos MS SQL con el nombre original

Para restaurar la base de datos con el nombre original, la opción Dónde no se debe especificar (valor vacío), o se debe especificar el valor “/” y el parámetro Reemplazar se le debe asignar un valor Siempre, o primero debe eliminar la base de datos de origen.

Restaurar una copia de seguridad de MS SQL con un nuevo nombre

Para restaurar una copia de seguridad de una base de datos MS SQL con un nuevo nombre, es posible que primero deba mover los archivos de la base de datos al disco. Todo depende de si la base de datos original todavía existe.

Si la base de datos de origen ya no está disponible, entonces el parámetro dónde, o el campo "Opciones de complemento" puede contener el nombre de la nueva base de datos. MS SQL Backup de Bacula creará automáticamente la base de datos con un nuevo nombre.

Si aún se necesita la base de datos original, el parámetro donde se usará para mover los archivos al disco y deberá nombrar la nueva base de datos usando el menú Opciones del complemento. En el árbol de recuperación, debe seleccionar el archivo layout.dat.

Usando Mi Catálogo

Ejecute la tarea de recuperación de MS SQL:

Usando Mi catálogo, ejecute la tarea de recuperación de la base de datos MS SQL:

Recuperar MS SQL al disco local

Si especifica donde=c:/ruta/, los archivos se restaurarán en el disco local y el administrador de la base de datos MS SQL podrá utilizar la extensión de procedimiento TSQL para Microsoft SQL Server Management Console para restaurar la base de datos. Los comandos SQL necesarios para restaurar la base de datos se enumeran en la descripción. Salida del trabajo como se muestra en la imagen de abajo.

“Quien posee la información es dueño del mundo” - Mayer Amschel Rothschild

El activo más valioso en cualquier negocio es la información. La pérdida de información puede tener consecuencias impredecibles, principalmente financieras. Por tanto, una de las principales tareas de los especialistas en TI es la copia de seguridad de toda la infraestructura de TI. Esto también se aplica a las bases de datos de MS SQL Server.

Para garantizar la seguridad de la información en las bases de datos utilizadas, así como para reducir el tiempo de restauración de la funcionalidad, es necesario realizar copias de seguridad periódicas de los servidores SQL.

Veamos un ejemplo simple: necesita configurar la copia de seguridad de la base de datos en un disco separado.

Solución:

1. Apertura Estudio de administración de Microsoft SQL Server. En el menú de navegación de la derecha, abra la pestaña "Control". Allí vemos una pestaña. "Planes de servicio". Haga clic derecho -> "Crear un plan de servicio" y darle un nombre a nuestro plan (Fig. 1):

Fig.1 Creación de un nuevo plan de servicio.

2. Agrega una tarea en la barra de herramientas. "Copia de seguridad de la base de datos"(Figura 2):

Fig.2 Agregar la tarea "Copia de seguridad de la base de datos".

3. En la tarea creada, haga clic derecho -> "Cambiar"(Figura 3):

4. En la ventana de propiedades de la tarea, seleccione el tipo de copia de seguridad (en mi caso, completa), seleccione la base de datos deseada (tengo ka_cons), el directorio para las copias de seguridad, la capacidad de verificar la integridad de las copias de seguridad y las opciones de compresión para ellas ( Figura 4-6):

Fig.4 Tipo de copia de seguridad: completa.

Fig.5 Selección de una base de datos para respaldo.

Fig.6 Definiendo el directorio para las copias de seguridad, verificando la integridad y la relación de compresión.

5. En el panel de configuración del plan de servicio a la derecha. presione el botón "Cronograma"(Figura 7):

6. Configure el horario que necesitamos y haga clic "DE ACUERDO"(Figura 8):

Fig.8 Configuración de un programa de respaldo.

7. Guarde nuestro plan de servicio (Fig. 9):

Fig.9 Guardar un plan de mantenimiento.

La copia de seguridad completa programada de la base de datos está configurada.

Los administradores de bases de datos se dividen en los que realizan las copias de seguridad y los que las realizarán.

Introducción

Este artículo describe la copia de seguridad de 1C IB más común utilizando las herramientas de MS SQL Server 2008 R2, explica por qué debe hacerlo de esta manera y no de otra manera y disipa varios mitos. El artículo contiene bastantes referencias a la documentación de MS SQL. Este artículo es más una descripción general de los mecanismos de copia de seguridad que una guía completa. Pero para aquellos que se enfrentan por primera vez a esta tarea, se proporcionan instrucciones sencillas y paso a paso que son aplicables a situaciones sencillas. El artículo no está destinado a gurús de la administración, los gurús ya saben todo esto, pero se supone que el lector es capaz de instalar él mismo MS SQL Server y obligar a este milagro de tecnología hostil a crear una base de datos en sus profundidades, que a su vez es capaz de forzar el almacenamiento de datos 1C.

Considero que el comando TSQL BACKUP DATABASE (y su hermano BACKUP LOG) es esencialmente el único medio para realizar una copia de seguridad de las bases de datos 1C utilizando MS SQL Server como DBMS. ¿Por qué? Veamos qué métodos tenemos generalmente:

Cómo	Bien	Gravemente	Total
Subir a dt	Formato muy compacto.	Lleva mucho tiempo formarse, requiere acceso exclusivo, no guarda algunos datos sin importancia (como la configuración del usuario en versiones anteriores) y lleva mucho tiempo implementar.	No es tanto un método de copia de seguridad sino una forma de mover datos de un entorno a otro. Ideal para canales estrechos.
Copiar archivos mdf y ldf	Una forma muy clara para administradores novatos.	Requiere liberar los archivos de la base de datos para que no se bloqueen, y esto es posible si la base de datos está deshabilitada (desconecte el comando del menú contextual), desconectada (separada) o simplemente se detiene el servidor. Evidentemente, los usuarios no podrán trabajar en este momento.	Tiene sentido utilizar este método si y solo si ya ha ocurrido un accidente, de modo que al intentar recuperarse, al menos tenga la oportunidad de volver a la opción desde la que comenzó la recuperación.
Copia de seguridad mediante sistema operativo o hipervisor	Una forma conveniente para entornos de desarrollo y prueba.	No siempre es amigable con la integridad de los datos. Método que requiere muchos recursos.	Puede tener un uso limitado para el desarrollo. No tiene ningún significado práctico en un entorno alimentario.
Copia de seguridad usando MS SQL	No se requiere tiempo de inactividad. Le permite restaurar un estado completo en un momento arbitrario, si se preocupa por ello con anticipación. Excelente automatización. Económico en tiempo y otros recursos.	No es un formato muy compacto. No todo el mundo sabe cómo utilizar este método en la medida necesaria.	Para entornos alimentarios: la herramienta principal.

Las principales dificultades al utilizar la copia de seguridad con herramientas MS SQL integradas surgen de una mala comprensión básica de los principios de funcionamiento. Esto se explica en parte por una gran pereza, en parte por la falta de una explicación sencilla y comprensible a nivel de “recetas preparadas” (hmm, digamos, no me he encontrado con ninguna), y la situación también se agrava. por el consejo mitológico del “underguru” en los foros. No sé qué hacer con la pereza, pero intentaré explicar los conceptos básicos de la copia de seguridad.

¿Qué y por qué ahorramos?

Hace mucho tiempo, en una galaxia lejana, existía un producto de ingeniería y contabilidad como 1C: Enterprise 7.7. Aparentemente debido al hecho de que las primeras versiones de 1C:Enterprise fueron desarrolladas para usar el popular formato de archivo dbf, su versión SQL no almacenaba suficiente información en la base de datos para considerar que la copia de seguridad de MS SQL estaba completa, e incluso con cada cambio en la estructura Se rompieron las condiciones operativas del modelo de recuperación completa, por lo que fue necesario hacer todo lo posible para obligar al sistema de respaldo a realizar su función principal. Pero desde que salió la versión 8, los administradores de bases de datos finalmente han podido relajarse. Las herramientas de respaldo estándar le permiten crear un sistema de respaldo completo e integral. Solo el registro de registro y algunas pequeñas cosas como la configuración de la posición de los formularios (en versiones anteriores) no se incluyen en la copia de seguridad, pero la pérdida de estos datos no afecta la funcionalidad del sistema, aunque ciertamente es correcta y útil. para realizar copias de seguridad del registro de registro.

¿Por qué necesitamos respaldo? Mmm. A primera vista, una pregunta extraña. Bueno, probablemente, en primer lugar, para poder implementar una copia del sistema y, en segundo lugar, para restaurar el sistema en caso de fallo. Estoy de acuerdo con el primero, pero el segundo propósito es el primer mito de la copia de seguridad.

La copia de seguridad es la última línea de seguridad del sistema. Si un administrador de base de datos tiene que restaurar el sistema de un producto a partir de copias de seguridad, es probable que se hayan cometido muchos errores graves en la organización del trabajo. No se puede tratar la copia de seguridad como la forma principal de garantizar la integridad de los datos; no, es más bien un sistema de extinción de incendios. Se requiere un sistema de extinción de incendios. Debe estar configurado, probado y operativo. Pero si funcionó, entonces esto en sí mismo es una emergencia grave con muchas consecuencias negativas.

Para garantizar que la copia de seguridad se utilice únicamente con fines "pacíficos", utilice otros medios para garantizar el rendimiento:

• Garantice la seguridad física de sus servidores: incendios, inundaciones, suministros de energía deficientes, limpiadores, trabajadores de la construcción, meteoritos y animales salvajes están esperando a la vuelta de la esquina para destruir su sala de servidores.
• Abordar las amenazas a la seguridad de la información de manera responsable.
• Realice cambios en el sistema con habilidad y asegúrese de antemano de que estos cambios no provoquen un deterioro. Además de un plan para realizar cambios, es recomendable tener un plan sobre “qué hacer si todo sale mal”.
• Utilice activamente tecnologías para aumentar la disponibilidad y confiabilidad del sistema en lugar de lidiar más tarde con las consecuencias de los accidentes. Para MS SQL debes prestar atención a las siguientes características:
  • Usar clústeres de MS SQL (aunque, para ser honesto, creo que esta es una de las formas más caras e inútiles de ocupar a un administrador de bases de datos para sistemas que no requieren 24 horas al día, 7 días a la semana)
  • Duplicación de bases de datos (sincrónica y asincrónica según la disponibilidad, el rendimiento y los requisitos de costo)
  • Entrega del registro de transacciones
  • Replicación utilizando herramientas 1C (bases de datos distribuidas)

Dependiendo de los requisitos de disponibilidad del sistema y del presupuesto asignado para estos fines, es muy posible elegir soluciones que reduzcan el tiempo de inactividad y el tiempo de recuperación de fallas en 1 o 2 órdenes de magnitud. No hay por qué tener miedo de las tecnologías de accesibilidad: son lo suficientemente sencillas como para aprenderlas en unos pocos días con conocimientos básicos de MS SQL.

Pero, pase lo que pase, la copia de seguridad sigue siendo necesaria. Este es el mismo paracaídas de reserva que puedes usar cuando fallan todos los demás medios de rescate. Pero, como un auténtico paracaídas de reserva, para esto:

• este sistema debe configurarse correcta y profesionalmente con antelación,
• un especialista que utilice el sistema debe tener habilidades teóricas y prácticas para su uso (reforzadas periódicamente),
• el sistema debe constar de los componentes más fiables y sencillos (esta es nuestra última esperanza).

Información básica sobre el almacenamiento y procesamiento de datos de MS SQL

Los datos en MS SQL generalmente se almacenan en archivos de datos (en adelante, FD, una abreviatura que no se usa comúnmente; en este artículo habrá varias abreviaturas más no muy comunes) con las extensiones mdf o ndf. Además de estos archivos, también existen registros de transacciones (TL), que se almacenan en archivos con la extensión .ldf. A menudo, los administradores novatos son irresponsables y frívolos cuando se trata de VT, tanto en términos de rendimiento como de confiabilidad del almacenamiento. Este es un error muy grave. De hecho, es más bien lo contrario: si hay un sistema de respaldo que funciona de manera confiable y se puede dedicar mucho tiempo a restaurar el sistema, entonces se pueden almacenar datos en un RAID-0 rápido, pero extremadamente poco confiable, pero luego los datos deben almacenarse en un recurso independiente confiable y productivo (aunque estaría en RAID-1). ¿Por qué es así? Echemos un vistazo más de cerca. Permítanme hacer una reserva de inmediato: la presentación es algo simplificada, pero suficiente para una comprensión inicial.

El FD almacena datos en páginas de 8 kilobytes (que se combinan en extensiones de 64 kilobytes, pero esto no es significativo). Microsoft SQL no garantiza que inmediatamente después de ejecutar un comando de cambio de datos, estos cambios irán al FD. No, es solo que la página en la memoria está marcada como "requiere guardar". Si el servidor tiene suficientes recursos, pronto estos datos estarán en el disco. Además, el servidor funciona de manera "optimista" y si estos cambios ocurren en una transacción, es posible que terminen en el disco antes de que se confirme la transacción. Es decir, en el caso general, durante la operación activa, el FD contiene datos dispersos y transacciones inconclusas, para las cuales se desconoce si serán canceladas o confirmadas. Hay un comando especial "CHECKPOINT", que le dice al servidor que necesita vaciar todos los datos no guardados en el disco "ahora mismo", pero el alcance de este comando es bastante específico. Baste decir que 1C no lo usa (no lo he encontrado) y comprender que durante el funcionamiento el FD no suele estar intacto.

Para hacer frente a este caos, sólo necesitamos VT. En él se registran los siguientes hechos:

• Información sobre el inicio de la transacción y su identificador.
• Información sobre el hecho de realizar o cancelar una transacción.
• Información sobre todos los cambios en los datos del FD (en términos generales, qué pasó y qué pasó).
• Información sobre cambios en el propio FD o en la estructura de la base de datos (aumentar archivos, disminuir archivos, asignar y liberar páginas, crear y eliminar tablas e índices)

Toda esta información está escrita indicando el identificador de la transacción en la que ocurrió y en un volumen suficiente para comprender cómo pasar del estado anterior a esta operación al estado posterior a esta operación y viceversa (la excepción es el modelo de recuperación de registro parcial) .

Es importante que esta información se escriba en el disco inmediatamente. Hasta que la información no sea registrada en el VT, el comando no se considera ejecutado. En una situación normal, cuando el tamaño del VT es suficiente y no está muy fragmentado, los registros se escriben en él secuencialmente en registros pequeños (no necesariamente múltiplos de 8 kb). En el registro de transacciones solo se incluyen los datos que realmente son necesarios para la recuperación. En particular No Se obtiene información sobre qué texto de solicitud provocó las modificaciones, qué plan de ejecución tenía esa solicitud, qué usuario la lanzó y otra información innecesaria para la recuperación. La consulta puede proporcionar información sobre la estructura de datos del registro de transacciones.

Seleccione * de::fn_dblog(nulo,nulo)

Debido al hecho de que los discos duros funcionan de manera mucho más eficiente con escrituras secuenciales que con un flujo caótico de comandos de lectura y escritura, y debido al hecho de que los comandos SQL esperarán hasta el final de la escritura en el disco duro, surge la siguiente recomendación :

Si existe la más mínima posibilidad, entonces, en el entorno de un producto, los VT deben ubicarse en medios físicos separados (de todo lo demás), preferiblemente con un tiempo de acceso mínimo para la grabación secuencial y con la máxima confiabilidad. Para sistemas simples, RAID-1 es bastante adecuado.

Si se cancela la transacción, el servidor devolverá todos los cambios ya realizados al estado anterior. Es por eso

La cancelación de una transacción en MS SQL Server suele tener una duración comparable a la duración total de las operaciones para cambiar los datos de la transacción en sí. Trate de no cancelar transacciones o tome la decisión de cancelarlas lo antes posible.

Si el servidor deja de funcionar inesperadamente por algún motivo, cuando se reinicie, se analizará qué datos en el FD no corresponden al estado integral (transacciones no registradas pero confirmadas y transacciones registradas pero canceladas) y estos datos se corregirán. Por lo tanto, si, por ejemplo, comenzó a reconstruir los índices de una tabla grande y reinició el servidor, cuando lo reinicie, tomará una cantidad significativa de tiempo revertir esta transacción y no hay forma de interrumpir este proceso. .

¿Qué sucede cuando el VT llega al final del archivo? Es simple: si hay espacio libre al principio, comenzará a escribir en el espacio libre al comienzo del archivo antes del espacio ocupado. Como una cinta magnética en bucle. Si no hay espacio al principio, entonces el servidor generalmente intentará expandir el archivo de registro de transacciones, mientras que para el servidor la nueva pieza asignada es un nuevo archivo de registro de transacciones virtual, del cual puede haber muchos en el archivo de transacciones físico. , pero esto tiene poco que ver con la copia de seguridad. Si el servidor no puede expandir el archivo (el espacio en disco se ha agotado o la configuración prohíbe expandir el archivo), la transacción actual se cancelará con el error 9002.

Ups. ¿Qué hay que hacer para que siempre haya un lugar en el ferrocarril? Aquí es donde llegamos al sistema de respaldo y los modelos de recuperación. Para cancelar transacciones y restaurar el estado correcto del servidor en caso de un apagado repentino, es necesario almacenar registros en el VT, comenzando desde el inicio de la primera transacción abierta. Este mínimo está escrito y almacenado en ZhT. Necesariamente. Independientemente del clima, la configuración del servidor y los deseos del administrador. El servidor no puede permitir que esta información no exista. Por lo tanto, si abre una transacción en una sesión y realiza diferentes acciones en otras, el registro de transacciones puede finalizar inesperadamente. La transacción más antigua se puede identificar con el comando DBCC OPENTRAN. Pero esto es sólo el mínimo necesario de información. Lo que suceda a continuación depende de modelos de recuperación. Hay tres de ellos en SQL Server:

• Simple— sólo se almacena el resto del VT necesario para la vida.
• Lleno— todo el VT se almacena desde la última copia de seguridad registro de transacciones. ¡Tenga en cuenta que no desde el momento de realizar una copia de seguridad completa!
• Registro masivo— parte (normalmente una parte muy pequeña) de las operaciones se registra en un formato muy compacto (esencialmente sólo un registro de que tal o cual página del archivo de datos ha sido modificada). Por lo demás, idéntico a Full.

Existen varios mitos asociados con los modelos de recuperación.

• Simple le permite reducir la carga en el subsistema de disco.. Esto está mal. Se escribe exactamente la misma cantidad que con el registro masivo, solo que se considera gratuito mucho antes.
• El registro masivo le permite reducir la carga en el subsistema de disco. Para 1C este casi no es el caso. De hecho, una de las pocas operaciones que pueden incluirse en un registro mínimo sin bailes adicionales con pandereta es cargar datos desde una carga en formato dt y reestructurar tablas.
• Cuando se utiliza el modelo de registro masivo, algunas transacciones no se incluyen en la copia de seguridad del registro de transacciones y no le permite restaurar el estado en el momento de esta copia de seguridad. Esto no es del todo cierto. Si la operación se registra mínimamente, las páginas actuales con datos se incluirán en la copia de seguridad y será posible "reproducir" el registro de transacciones hasta el final (aunque esto no es posible en un momento arbitrario si hay operaciones mínimamente registradas).

Es casi inútil utilizar el modelo de registro masivo para bases de datos 1C, por lo que no lo consideraremos más. Pero consideraremos la elección entre Completo y Simple con más detalle en la siguiente parte.

• Estructura del registro de transacciones
• • Modelos de recuperación y gestión de registros de transacciones
  • Gestión de registros de transacciones
• Uso de copias de seguridad del registro de transacciones

Cómo funciona la copia de seguridad en los modelos de recuperación simple y completa

Según el tipo de formación, las copias de seguridad son de tres tipos:

• Lleno(Lleno)
• Diferencial(Diferencial, diferencia)
• Registro(Copia de seguridad de los registros de transacciones, dada la frecuencia con la que se usa este término, lo abreviaremos como RKZhT)

Es importante no confundirse aquí: un modelo de recuperación completa y una copia de seguridad completa son cosas significativamente diferentes. Para no confundirlos, a continuación utilizaré términos en inglés para el modelo de recuperación y términos en ruso para los tipos de copias de seguridad.

La copia completa y diferencial funcionan igual para Simple y Full. La copia de seguridad del registro de transacciones falta por completo en Simple.

Copia de seguridad completa

Le permite restaurar el estado de la base de datos a un momento determinado (aquel en el que se inició la copia de seguridad). Consiste en una copia página por página de la parte utilizada de los archivos de datos y una parte activa del registro de transacciones durante el tiempo que se estaba formando la copia de seguridad.

Respaldo diferencial

Almacena páginas de datos que han cambiado desde la última copia de seguridad completa. Al restaurar, primero debe restaurar una copia de seguridad completa (en el modo NORECOVERY, se darán ejemplos a continuación), luego puede aplicar cualquiera de las copias diferenciales posteriores al "espacio en blanco" resultante, pero, por supuesto, solo de las realizadas antes. la siguiente copia de seguridad completa. Gracias a esto, puede reducir significativamente la cantidad de espacio en disco para almacenar una copia de seguridad.

Puntos importantes:

• Sin una copia de seguridad completa previa, una copia diferencial no sirve de nada. Por tanto, es recomendable guardarlos en un lugar cercano entre sí.
• Cada copia de seguridad diferencial posterior conservará todas las páginas incluidas en la copia de seguridad diferencial anterior realizada después de la copia de seguridad completa anterior (aunque quizás con contenido diferente). Por lo tanto, cada copia diferencial posterior es más grande que las anteriores, hasta que se vuelve a realizar una copia completa (si esto se viola, es solo por algoritmos de compresión)
• Para recuperarse en algún momento es suficiente. último copia de seguridad completa en este punto y último copia diferencial en este punto. No se necesitan copias intermedias para la recuperación (aunque pueden ser necesarias para seleccionar el momento de la recuperación)

RKZHT

Contiene una copia del VT por un período determinado. Generalmente desde el momento del último RKZhT hasta la formación del RKZhT actual. RKZHT le permite restaurar el estado de una copia restaurada en modo NORECOVERY en cualquier momento incluido en el período de la copia restaurada del ZHT en cualquier momento posterior incluido en el intervalo de la copia de seguridad restaurada. Al crear una copia de seguridad con parámetros estándar, se libera espacio en el archivo de registro de transacciones (hasta el momento de la última transacción abierta).

Obviamente, el RKZhT no tiene sentido en el modelo Simple (entonces el ZhT contiene sólo información del momento de la última transacción no cerrada).

Cuando se utiliza RKZhT, surge un concepto importante: cadena RKZHT continua. Esta cadena puede interrumpirse mediante la pérdida de algunas copias de seguridad de esta cadena o convirtiendo la base de datos a Simple y viceversa.

Atención: un conjunto de RKZhT es esencialmente inútil a menos que sea una cadena continua, y el punto de inicio de la última copia de seguridad completa o diferencial exitosa debe ser adentro período de esta cadena.

Conceptos erróneos y mitos comunes:

• "RKZhT contiene datos del registro de transacciones desde el momento de la anterior copia de seguridad completa o diferencial". No, eso no es cierto. El RKZhT también contiene, a primera vista, datos inútiles entre el RKZhT anterior y la copia de seguridad completa posterior.
• "Una copia de seguridad completa o diferencial debería liberar espacio dentro del registro de transacciones". No, eso no es cierto. Las copias de seguridad completas y diferenciales no afectan la cadena RKZhT.
• El VT debe limpiarse periódicamente de forma manual, reducirse y triturarse. No, no es necesario y, al contrario, es indeseable. Si suelta el VT entre el RCVT, la cadena RCVT necesaria para la restauración se romperá. Y la reducción/expansión constante del archivo conducirá a su fragmentación física y lógica.

Cómo funciona de forma sencilla

Que haya una base de datos de 1000 GB. Cada día la base de datos crece 2 GB, mientras que se modifican 10 GB de datos antiguos. Se han realizado las siguientes copias de seguridad

• Copia completa de F1 desde las 0:00 del 1 de febrero (volumen 1000 GB, no se tiene en cuenta la compresión para simplificar la imagen)
  • Copia diferencial de D1.1 de las 0:00 horas del 2 de febrero (volumen 12 GB)
  • Copia diferencial de D1.2 de las 0:00 horas del 3 de febrero (volumen 19 GB)
  • Copia diferencial de D1.3 de las 0:00 horas del 4 de febrero (volumen 25 GB)
  • Copia diferencial de D1.4 de las 0:00 horas del 5 de febrero (volumen 31 GB)
  • Copia diferencial D1.5 de las 0:00 del 6 de febrero (volumen 36 GB)
  • Copia diferencial de D1.6 de las 0:00 horas del 7 de febrero (volumen 40 GB)
• Copia completa de F2 de las 0:00 del 8 de febrero (volumen 1014 GB)
  • Copia diferencial de D2.1 de las 0:00 horas del 9 de febrero (volumen 12 GB)
  • Copia diferencial de D2.2 de las 0:00 horas del 10 de febrero (volumen 19 GB)
  • Copia diferencial de D2.3 de las 0:00 horas del 11 de febrero (volumen 25 GB)
  • Copia diferencial de D2.4 de las 0:00 horas del 12 de febrero (volumen 31 GB)
  • Copia diferencial D2.5 de las 0:00 del 13 de febrero (volumen 36 GB)
  • Copia diferencial de D2.6 de las 0:00 horas del 14 de febrero (volumen 40 GB)

Con este conjunto, podemos restaurar datos a las 0:00 de cualquier día del 1 al 14 de febrero. Para hacer esto, necesitamos tomar una copia completa de F1 para la semana del 1 al 7 de febrero o una copia completa de F2 del 8 al 14 de febrero, restaurarla en modo NORECOVERY y luego aplicar una copia diferencial del día deseado.

Como funciona en su totalidad

Tengamos el mismo conjunto de copias de seguridad completas y diferenciales que en el ejemplo anterior. Además de esto, existen los siguientes RKZhT:

• RKZhT 1 para el período comprendido entre las 12:00 del 31 de enero y las 12:00 del 2 de febrero (aproximadamente 30 GB)
• RKZhT 2 para el período comprendido entre las 12:00 del 2 de febrero y las 12:00 del 4 de febrero (aproximadamente 30 GB)
• RKZhT 3 para el período comprendido entre las 12:00 del 4 de febrero y las 12:00 del 6 de febrero (aproximadamente 30 GB)
• RKZhT 4 para el período comprendido entre las 12:00 del 6 de febrero y las 12:00 del 7 de febrero (aproximadamente 30 GB)
• RKZhT 5 para el período comprendido entre las 12:00 del 8 de febrero y las 12:00 del 10 de febrero (aproximadamente 30 GB)
• RKZhT 6 para el período comprendido entre las 12:00 del 10 de febrero y las 12:00 del 12 de febrero (aproximadamente 30 GB)
• RKZhT 7 para el período comprendido entre las 12:00 del 12 de febrero y las 12:00 del 14 de febrero (aproximadamente 30 GB)
• RKZhT 8 para el período comprendido entre las 12:00 del 14 de febrero y las 12:00 del 16 de febrero (aproximadamente 30 GB)

Tenga en cuenta:

1. El tamaño del RKZhT será aproximadamente constante.
2. Podemos hacer copias de seguridad con menos frecuencia que las diferenciales o completas, o podemos hacerlas con más frecuencia, entonces serán de menor tamaño.
3. Ahora podemos restaurar el estado del sistema en cualquier punto desde las 0:00 del 1 de febrero, cuando tenemos la primera copia completa, hasta las 12:00 del 16 de febrero.

En el caso más simple, necesitamos restaurar:

1. Última copia completa antes de la recuperación
2. Última copia diferencial antes de la recuperación
3. Todo RKZhT, desde el momento de la última copia diferenciada hasta el momento de la restauración.

• Copia completa de F2 desde las 0:00 del 8 de febrero
• Copia de diferencia D2.2 de las 0:00 del 10 de febrero
• RKZhT 6 para el período comprendido entre las 12:00 del 10 de enero y las 12:00 del 12 de febrero

Primero se restaurará F2, luego D2.2, luego RKZhT 6 hasta las 13:13:13 del 10 de febrero. Pero una ventaja significativa del modelo completo es que tenemos la opción de usar la última copia completa o diferencial o NO la última. Por ejemplo, si se descubriera que la copia de D2.2 estaba dañada y necesitáramos restaurarla al momento anterior a las 13:13:13 del 10 de febrero, entonces, para el modelo Simple, esto significaría que solo podríamos restaurar la copia. datos al momento D2.1. Con Full - "NO PÁNICO", tenemos las siguientes opciones:

1. Restaure F2, luego D2.1, luego RKZHT 5, luego RKZHT 6 hasta las 13:13:13 del 10 de febrero.
2. Restaure F2, luego RKZHT 4, luego RKZHT 5, luego RKZHT 6 hasta las 13:13:13 del 10 de febrero.
3. O incluso restaurar F1 y ejecutar todos los RKZhT hasta RKZhT 6 hasta las 13:13:13 del 10 de febrero.

Como puede ver, el modelo completo nos da más opciones.

Ahora imaginemos que somos muy astutos. Y un par de días antes del fracaso (13:13:13 del 10 de febrero) sabemos que habrá fracaso. Restauramos la base de datos en un servidor vecino desde una copia de seguridad completa, dejando la posibilidad de acumular estados posteriores con copias diferenciales o RKZhT, es decir, la dejamos en modo NORECOVERY. Y cada vez, inmediatamente después de la formación del RKZhT, lo aplicamos a esta base de reserva, dejándola en modo NORECOVERY. ¡Guau! ¡Ahora nos llevará sólo entre 10 y 15 minutos restaurar la base de datos, en lugar de restaurar una base de datos enorme! Felicitaciones, hemos reinventado el envío de registros, una de las formas de reducir el tiempo de inactividad. Si transfiere datos de esta manera no una vez por período, sino constantemente, obtendrá una duplicación, y si la base de origen espera a que se actualice la base reflejada, entonces esto es una duplicación sincrónica, si no espera, entonces es asincrónico.

Puede leer más sobre herramientas de alta disponibilidad en la ayuda:

• Alta disponibilidad (motor de base de datos)
  • Comprender las soluciones de alta disponibilidad
  • Alto nivel de accesibilidad. Interacción y colaboración

Otras consideraciones sobre la copia de seguridad

Puedes saltarte esta sección con seguridad si estás aburrido de la teoría y tienes ganas de probar la configuración de la copia de seguridad.

Grupos de archivos

1C:Enterprise esencialmente no sabe cómo trabajar con grupos de archivos. Hay un solo grupo de archivos y listo. De hecho, un programador o administrador de una base de datos MS SQL puede colocar algunas tablas, índices o incluso partes de tablas e índices en grupos de archivos separados (en la versión más simple, en archivos separados). Esto es necesario para acelerar el acceso a algunos datos (colocándolos en medios muy rápidos), o viceversa, sacrificando la velocidad y colocándolos en medios más baratos (por ejemplo, datos poco utilizados pero voluminosos). Cuando se trabaja con grupos de archivos, es posible hacer copias de seguridad de ellos por separado y también puede restaurarlos por separado, pero debe tener en cuenta que todos los grupos de archivos deberán "recuperarse" en un punto haciendo rodar el RKZHT.

Archivos de datos

Si una persona administra la ubicación de los datos en diferentes grupos de archivos, cuando hay varios archivos dentro del grupo de archivos, MS SQL Server inserta los datos en ellos de forma independiente (si el volumen de archivos es igual, lo intentará de manera uniforme). Desde el punto de vista de la aplicación, esto se utiliza para paralelizar las operaciones de E/S. Pero desde el punto de vista de las copias de seguridad, hay otro punto. Para bases de datos muy grandes en la era anterior a SQL 2008, era un problema típico asignar una ventana contigua para una copia de seguridad completa, y es posible que el disco de destino para esta copia de seguridad simplemente no la admitiera. La forma más sencilla en este caso era hacer una copia de seguridad de cada archivo (o grupo de archivos) en su propia ventana. Ahora, con la expansión activa de la compresión de copias de seguridad, este problema ha disminuido, pero aún se puede tener en cuenta esta técnica.

Compresión de respaldo

MS SQL Server 2008 introdujo una característica súper-mega-ultra. De ahora en adelante y para siempre, las copias de seguridad se pueden comprimir cuando se generan sobre la marcha. Esto reduce el tamaño de una copia de seguridad de una base de datos 1C entre 5 y 10 veces. Y teniendo en cuenta que el rendimiento del subsistema de disco suele ser el cuello de botella del DBMS, esto no solo reduce el costo de almacenamiento, sino que también acelera enormemente la copia de seguridad (aunque la carga en los procesadores aumenta, pero generalmente la potencia del procesador es suficiente en el servidor DBMS).

Si en la versión 2008 esta función solo estaba disponible para la edición Enterprise (que es muy cara), en 2008 R2 esta función se incluyó en la versión Standard, lo cual es muy agradable.

La configuración de compresión no se trata en los ejemplos siguientes, pero recomiendo encarecidamente utilizar la compresión de copia de seguridad a menos que exista una razón específica para desactivarla.

Un archivo de copia de seguridad, muchos elementos internos

De hecho, una copia de seguridad no es sólo un archivo, es un contenedor bastante complejo en el que se pueden almacenar muchas copias de seguridad. Este enfoque tiene una historia muy antigua (yo personalmente lo he estado observando desde la versión 6.5), pero por el momento para los administradores de bases de datos "normales", especialmente bases de datos 1C, no existen razones serias para no utilizar "una copia de seguridad, una". enfoque de archivo”. Para el desarrollo general, es útil estudiar la capacidad de colocar varias copias de seguridad en un archivo, pero lo más probable es que no tenga que usarla (o, si es necesario, será ordenar los escombros de un posible administrador). quien sin reservas aprovechó esta oportunidad).

Múltiples copias espejo

SQL Server tiene otra gran característica. Puedes crear una copia de seguridad en paralelo en varios receptores. Como ejemplo simple, puede volcar una copia en un disco local y almacenarla simultáneamente en un recurso de red. Una copia local es conveniente, ya que la recuperación es mucho más rápida; una copia remota resistirá mucho mejor la destrucción física del servidor de base de datos principal.

Ejemplos de sistemas de respaldo

Basta de teoría. Es hora de demostrar con la práctica que toda esta cocina funciona.

Configurar una reserva de servidor típica a través de Planes de Mantenimiento

Esta sección está estructurada en forma de recetas preparadas con explicaciones. Esta sección es muy aburrida y larga debido a las imágenes, así que puedes saltearla.

Usando el asistente de creación de planes de servicio

Configuración de la copia de seguridad del servidor mediante scripts TSQL, ejemplos de algunas funciones

Inmediatamente surge la pregunta: ¿qué más se necesita? ¿Parece que acabas de configurar todo y todo funciona como un reloj? ¿Por qué molestarse con todo tipo de guiones? Los planes de servicio no permiten:

• Usar copia de seguridad espejo
• Utilice configuraciones de compresión diferentes a las configuraciones del servidor
• No permite una respuesta flexible a situaciones emergentes (sin capacidades de manejo de errores)
• No permite el uso flexible de la configuración de seguridad.
• Los planes de servicio son muy inconvenientes de implementar (y mantener los mismos) en una gran cantidad de servidores (incluso, quizás, ya en 3-4)

A continuación se muestran los comandos de copia de seguridad típicos.

Copia de seguridad completa

Copia de seguridad completa con sobrescritura del archivo existente (si corresponde) y verificación de las sumas de verificación de las páginas antes de escribir. Al crear una copia de seguridad, se cuenta cada porcentaje del progreso.

RESPALDO DE LA BASE DE DATOS EN DISCO = N"C:\Backup\mydb.bak" CON INIT, FORMAT, STATS = 1, CHECKSUM

Respaldo diferencial

Del mismo modo - copia de diferencia

RESPALDAR BASE DE DATOS EN DISCO = N"C:\Backup\mydb.diff" CON DIFERENCIAL, INIT, FORMATO, ESTADÍSTICAS = 1, SUMA DE VERIFICACIÓN

RKZHT

Copia de seguridad del registro de transacciones

REGISTRO DE RESPALDO EN DISCO = N"C:\Backup\mydb.trn" CON INIT, FORMATEAR

Copia de seguridad espejo

Muchas veces es conveniente hacer no una copia de seguridad a la vez, sino dos. Por ejemplo, uno se puede almacenar localmente en el servidor (para que esté disponible) y el segundo se puede convertir inmediatamente en un almacenamiento físicamente remoto y protegido de influencias adversas:

RESPALDAR BASE DE DATOS EN DISCO = N"C:\Backup\mydb.bak", ESPEJO A DISCO = N"\\safe-server\backup\mydb.bak" CON INIT, FORMATO

Un punto importante que a menudo se pasa por alto: el usuario en cuyo nombre se inicia el proceso del servidor MSSQL debe tener acceso al recurso "\\safe-server\backup\", de lo contrario la copia fallará. Si se inicia MSSQL Server en nombre del sistema, entonces se debe otorgar acceso al usuario del dominio "server_name$", pero aún es mejor configurar correctamente MS SQL para que se ejecute en nombre de un usuario creado especialmente.

Si no especifica MIRROR TO , no serán 2 copias espejo, sino una copia, dividida en 2 archivos, según el principio de intercalado. Y cada uno de ellos por separado será inútil.

sqlcmd -S DECLSERVER\SQLGTD -E -Q "declare @s varchar(255) set @s='E:\backup\GTD_' + convert(varchar(1), datepart(dw, getdate())) + '. bak' copia de seguridad de la base de datos GTD en el disco = @s con init, noformat, skip, nounload"

sqlcmd le permite ingresar declaraciones Transact-SQL, procedimientos del sistema y archivos de script desde la línea de comando al editor de consultas en modo SQLCMD,

• -S - especifica el nombre del servidor, servidor[\nombre_instancia];
• DECLSERVER\SQLGTD - nombre del servidor/nombre de la instancia en la que se ejecuta la base de datos;
• -MI - utiliza una conexión confiable para conectarse al servidor SQL en lugar de un nombre de usuario y contraseña;
• -Q "consultacmdline" - al iniciar el programa sqlcmd ejecuta la solicitud, pero no sale del programa al finalizar su ejecución. Se pueden ejecutar varias consultas, separadas por punto y coma. Incluya la consulta entre comillas como se muestra arriba;
• declarar - declarar la variable s, el nombre de la variable siempre comienza con @, entonces @s. En nuestro caso @s- esta es la carpeta (disco) para almacenar copias de seguridad;
• varchar(n) - establece el tipo de variable @s como una cadena con una cadena larga n, en el ejemplo 255 caracteres;
• colocar - establece el valor de una variable @s, en el ejemplo esta es la carpeta de respaldo en la unidad E ( E:\copia de seguridad\), luego se especifica el nombre del archivo de respaldo, donde se encuentra el conjunto de funciones convertir(varchar(1), datepart(dw, getdate())) devuelve en formato de texto con una longitud de 1 carácter el día actual de la semana (lunes - 1 , Martes - 2 , etc.) y se agrega la extensión hornear. La salida será un archivo con el nombre GTD_Número del día de la semana.bak;
• respaldo - crea una copia de seguridad;
• base de datos - indica la creación de una copia de seguridad de toda la base de datos;
• GTD - en nuestro ejemplo, el nombre de la base de datos en el servidor SQL;
• al disco - indica el tipo de dispositivo de almacenamiento de respaldo, archivo de disco duro y variable especificada @s, al que se le asigna la ruta y el nombre del archivo que se está creando;
• con init, noformat, skip, nounload - indica que es necesario reescribir los datos en un círculo redefiniendo los encabezados, lo que nos permitirá tener 7 archivos de respaldo para cada día de la semana, reescritos en un círculo.

Puede usar otras funciones, como la compresión, según sea necesario; consulte Ayuda de funciones y consultas de Transact-SQL.

Paso 2. Cambie la extensión del archivo de texto a .cmd

Como resultado, obtenemos el archivo. copia de seguridadGTD.cmd. Debe ejecutar el archivo por lotes creado desde la máquina donde está instalada la base de datos MS SQL.

Paso 3. Automatiza este proceso

Consideremos este paso usando MS Windows Server 2008 como ejemplo: Administrador del servidor -> Configuración -> Programador de trabajos -> Biblioteca del programador de trabajos.

A pesar de que en nuestros materiales anteriores ya hemos abordado el tema de la copia de seguridad de las bases de datos de Microsoft SQL Server, la respuesta de los lectores mostró la necesidad de crear un material completo con un estudio más profundo de la parte teórica. De hecho, los artículos, escritos con énfasis en instrucciones prácticas, le permiten configurar rápidamente copias de seguridad, pero no explican las razones para elegir determinadas configuraciones. Intentemos corregir esta brecha.

Modelos de recuperación

Antes de configurar una copia de seguridad, debe seleccionar un modelo de recuperación. Para tomar la decisión óptima, debe evaluar los requisitos de recuperación y la importancia de la pérdida de datos, comparándolos con los costos generales de implementar un modelo en particular.

Como sabe, la base de datos MS SQL consta de dos partes: la base de datos en sí y el registro de transacciones. La base de datos contiene datos de usuarios y servicios en el momento actual, el registro de transacciones incluye el historial de todos los cambios en la base de datos durante un período determinado, con el registro de transacciones podemos revertir el estado de la base de datos a cualquier momento arbitrario.

Hay dos modelos de recuperación disponibles para usar en entornos de producción: simple y completo. También hay un modelo con registro incompleto, pero se recomienda solo como complemento al modelo completo para períodos de operaciones masivas a gran escala cuando no es necesario restaurar la base en un momento determinado.

modelo sencillo proporciona copia de seguridad solo de la base de datos; en consecuencia, podemos restaurar el estado de la base de datos solo en el momento en que se creó la copia de seguridad; se perderán todos los cambios en el período de tiempo entre la creación de la última copia de seguridad y la falla; Al mismo tiempo, un esquema simple tiene pocos gastos generales: solo necesita almacenar copias de la base de datos; el registro de transacciones se trunca automáticamente y no aumenta de tamaño; Además, el proceso de recuperación es el más sencillo y no lleva mucho tiempo.

modelo completo le permite restaurar la base de datos en cualquier momento arbitrario, pero requiere, además de las copias de seguridad de la base de datos, almacenar copias del registro de transacciones durante todo el período durante el cual puede ser necesaria la restauración. Cuando se trabaja activamente con la base de datos, el tamaño del registro de transacciones y, en consecuencia, el tamaño de los archivos, puede alcanzar tamaños grandes. El proceso de recuperación también es mucho más complejo y requiere más tiempo.

Al elegir un modelo de recuperación, debe comparar el costo de recuperación con el costo de almacenar copias de seguridad, y también debe tener en cuenta la disponibilidad y calificaciones del personal que realizará la recuperación. La restauración con un modelo completo requiere que el personal tenga ciertas cualificaciones y conocimientos, mientras que con un esquema sencillo bastará con seguir las instrucciones.

Para bases de datos con una pequeña cantidad de información agregada, puede ser más rentable utilizar un modelo simple con una alta frecuencia de copias, lo que le permitirá recuperarse rápidamente y continuar trabajando ingresando manualmente los datos perdidos. El modelo completo debería usarse principalmente cuando la pérdida de datos sea inaceptable y donde una eventual recuperación sería costosa.

Tipos de copias de seguridad

Copia completa de la base de datos.- como su nombre indica, representa el contenido de la base de datos y parte del registro de transacciones activas durante el momento en que se formó la copia de seguridad (es decir, información sobre todas las transacciones actuales e incompletas). Le permite restaurar completamente la base de datos al momento en que se creó la copia de seguridad.

Copia de la base de datos delta- una copia completa tiene un inconveniente importante: contiene toda la información de la base de datos. Si es necesario realizar copias de seguridad con bastante frecuencia, surge inmediatamente la cuestión del desperdicio del espacio en disco, ya que la mayor parte del almacenamiento estará ocupado por los mismos datos. Para eliminar este inconveniente, puede utilizar copias diferenciales de la base de datos, que contienen sólo información que ha cambiado desde la última copia completa.

Tenga en cuenta que una copia diferencial son datos de la última vez. lleno copiar, es decir Cada copia diferencial posterior contiene los datos de la anterior (pero se pueden cambiar) y el tamaño de la copia crecerá constantemente. Para restaurar basta con una copia completa y otra diferencial, normalmente la última. El número de copias diferenciales debe seleccionarse en función del aumento de su tamaño; tan pronto como el tamaño de la copia diferencial sea igual al tamaño de la mitad de la copia completa, tiene sentido hacer una nueva copia completa.

Copia de seguridad del registro de transacciones- se aplica únicamente al modelo de recuperación completa y contiene una copia del registro de transacciones a partir del momento en que se creó la copia anterior.

Es importante recordar el siguiente punto: las copias del registro de transacciones no están relacionadas de ninguna manera con las copias de la base de datos y no contienen información de copias anteriores, por lo que para restaurar la base de datos necesita tener una cadena ininterrumpida de copias durante el período durante el cual desea para poder revertir el estado de la base de datos. En este caso, el momento de la última copia exitosa deberá estar dentro de este plazo.

Miremos la figura anterior, si se pierde la primera copia del archivo de registro, podrá restaurar el estado de la base de datos solo en el momento de la copia completa, que será similar al modelo de recuperación simple; podrá restaurar el estado de la base de datos en cualquier momento solo después de la siguiente copia diferencial (o completa), siempre que la cadena de copias del registro que comienza desde la anterior a la copia de la base de datos y en adelante sea continua (en la figura - desde el tercero en adelante).

Registro de transacciones

Para comprender los procesos de recuperación y el propósito de los diferentes tipos de copias de seguridad, debe observar más de cerca la estructura y el funcionamiento del registro de transacciones. Una transacción es la mínima operación lógica posible que tiene sentido y sólo puede completarse por completo. Este enfoque garantiza la integridad y coherencia de los datos en todas las situaciones, ya que un estado intermedio de la operación es inaceptable. Se utiliza un registro de transacciones para controlar cualquier cambio en la base de datos.

Cuando se realiza cualquier operación, se agrega un registro sobre el comienzo de la transacción al registro de transacciones, a cada registro se le asigna un número único (LSN) de una secuencia ininterrumpida, cuando cualquier dato cambia, se realiza una entrada correspondiente en el registro. y una vez completada la operación, aparece en el registro una marca que indica el cierre (fijación) de la transacción.

En cada inicio, el sistema analiza el registro de transacciones y revierte todas las transacciones no confirmadas, al mismo tiempo que revierte los cambios que se registraron en el registro pero que no se escribieron en el disco. Esto hace posible utilizar el almacenamiento en caché y la escritura diferida sin preocuparse por la integridad de los datos, incluso en ausencia de sistemas de energía de respaldo.

La parte del registro que contiene transacciones activas y se utiliza para la recuperación de datos se denomina parte activa del registro. Comienza con un número llamado número mínimo de recuperación (MinLSN).

En el caso más simple, MinLSN es el número de registro de la primera transacción pendiente. Si observa la figura de arriba, al abrir la transacción azul obtendremos un MinLSN igual a 321, después de que se fije en el registro 324, el número MinLSN cambiará a 323, que corresponderá al número del verde, no aún comprometida, transacción.

En la práctica, todo es un poco más complicado, por ejemplo, es posible que los datos de una transacción azul cerrada aún no se hayan vaciado en el disco y mover MinLSN a 323 hará imposible la recuperación de esta operación. Para evitar este tipo de situaciones, se introdujo el concepto de punto de control. Un punto de control se crea automáticamente cuando ocurren las siguientes condiciones:

• Al ejecutar explícitamente la declaración CHECKPOINT. El punto de control se activa en la base de datos de conexión actual.
• Cuando realiza una operación mínimamente registrada en una base de datos, como cuando realiza una operación de copia masiva en una base de datos sujeta al modelo de recuperación de registros masivos.
• Al agregar o eliminar archivos de bases de datos utilizando la instrucción ALTER DATABASE.
• Cuando detiene una instancia de SQL Server mediante la instrucción SHUTDOWN o cuando detiene el servicio de SQL Server (MSSQLSERVER). En ambos casos, se creará un punto de control para cada base de datos en la instancia de SQL Server.
• Si su instancia de SQL Server crea periódicamente puntos de control automáticos en cada base de datos para reducir el tiempo de recuperación de la base de datos.
• Al crear una copia de seguridad de la base de datos.
• Al realizar una acción que requiere cerrar la base de datos. Los ejemplos incluyen configurar el parámetro AUTO_CLOSE en ON y cerrar la última conexión del usuario a la base de datos, o cambiar una configuración de la base de datos que requiere reiniciar la base de datos.

Dependiendo de qué evento ocurrió primero, MinLSN se establecerá en el número de registro del punto de control o en el inicio de la transacción pendiente más antigua.

Truncamiento del registro de transacciones

El registro de transacciones, como cualquier registro, requiere una limpieza periódica de entradas obsoletas; de lo contrario, crecerá y ocupará todo el espacio disponible. Teniendo en cuenta que cuando se trabaja activamente con la base de datos, el tamaño del registro de transacciones puede exceder significativamente el tamaño de la base de datos, esta pregunta es relevante para muchos administradores.

Físicamente, el archivo de registro de transacciones es un contenedor para registros virtuales, que se llenan secuencialmente a medida que crece el registro. El registro lógico que contiene la entrada MinLSN es el comienzo del registro activo; los registros lógicos que lo preceden están inactivos y no son necesarios para la recuperación automática de la base de datos.

Si se selecciona un modelo de recuperación simple, cuando los registros lógicos alcanzan un tamaño igual al 70% del archivo físico, la parte inactiva del registro se borra automáticamente, lo que se denomina. truncamiento. Sin embargo, esto no reduce el archivo de registro físico; solo se truncan los registros lógicos, que luego se pueden reutilizar después de esta operación.

Si la cantidad de transacciones es grande y cuando se alcanza el 70% del tamaño del archivo físico no hay registros lógicos inactivos, entonces se aumentará el tamaño del archivo físico.

Por lo tanto, el archivo de registro de transacciones con un modelo de recuperación simple crecerá de acuerdo con la actividad de trabajo con la base de datos hasta que contenga de manera confiable toda la parte activa del registro. Después de lo cual su crecimiento se detendrá.

Con el modelo completo, la parte inactiva del registro no se puede truncar hasta que se realice una copia de seguridad completa. El truncamiento del registro se produce si se realizó una copia de seguridad del registro de transacciones y se creó un punto de control.

La configuración incorrecta de la copia de seguridad del registro de transacciones en un modelo completo puede provocar un crecimiento descontrolado de los archivos de registro, lo que suele ser un problema para los administradores sin experiencia. También suele encontrarse con consejos sobre cómo truncar manualmente el registro de transacciones. Con un modelo de recuperación completa, esto no debe hacerse categóricamente, ya que esto violará la integridad de la cadena de copias del registro y podrá restaurar la base de datos solo en el momento en que se crearon las copias, lo que corresponderá al modelo simple. .

En este caso, toca recordar lo que hablamos al principio del artículo; si los costes de un modelo completo superan los costes de restauración, se debe dar preferencia a un modelo simple.

Modelo de recuperación simple

Ahora, habiendo obtenido el mínimo de conocimientos necesarios, podemos pasar a una consideración más detallada de los modelos de recuperación. Comencemos con uno simple. Digamos que en el momento del fallo tenemos una copia completa y dos diferenciales:

Las copias de seguridad se realizaron una vez al día y la última copia se creó en la noche del 21 al 22. El error se produce la tarde del día 22 antes de que se cree la siguiente copia. En este caso, necesitaremos restaurar secuencialmente las copias completa y diferencial, y se perderán los datos del último día hábil. Si por alguna razón la copia del día 21 también resulta dañada, entonces podemos restaurar la copia anterior, perdiendo un día más de trabajo, mientras que el daño a la copia del día 20 no nos impedirá de ninguna manera. de restaurar con éxito los datos en la tarde del día 21, cuando esté disponible una copia correspondiente.

Modelo de recuperación total

Consideremos una situación similar, pero utilizando el modelo de recuperación total. También realizamos copias de seguridad diariamente, utilizando el principio completo + diferencial, y también copiamos el registro de transacciones varias veces al día.

El proceso de recuperación en este caso será más complicado. En primer lugar, deberá hacer una copia de seguridad manual de la cola del tronco (que se muestra en rojo), es decir. parte del registro desde el momento en que se creó la última copia hasta el accidente.

Si no se hace esto, será posible restaurar la base de datos solo al estado en el que se creó la última copia del registro de transacciones.

En este caso, el daño al archivo de copia de registro del día anterior no nos impedirá restaurar el estado actual de la base de datos, sino que nos limitará al momento en que se creó la última copia, es decir. días actuales.

Luego restauramos secuencialmente las copias completa y diferencial y la cadena de copias del registro creadas después de la última copia de seguridad, la última que restauramos es una copia del fragmento final del registro, lo que nos dará la oportunidad de restaurar la base de datos correctamente. en el momento del accidente o uno arbitrario que lo precedió.

Si la última copia diferencial está dañada, en el caso de un modelo simple esto provocará la pérdida de otro día hábil; el modelo completo le permite restaurar la penúltima copia, después de lo cual deberá restaurar toda la cadena de transacciones; registrar copias desde el momento de la penúltima copia hasta el fallo. La profundidad de recuperación depende únicamente de la profundidad de la cadena continua de troncos.

Por otro lado, si una de las copias del registro de transacciones está dañada, digamos la penúltima, entonces podremos restaurar los datos solo al momento de la última copia de seguridad + el período en la cadena intacta de copias del registro. Por ejemplo, si los registros se realizaron a las 12, 14 y 16 horas y el registro creado a las 14 horas está dañado, entonces teniendo una copia diaria podemos restaurar la base de datos hasta el final de la cadena continua, es decir. hasta las 12 en punto.