Download RESPALDO Y RESTAURACIÓN DE BASE DE DATOS - UT-AGS
Document related concepts
Transcript
RESPALDO Y RESTAURACIÓN DE BASE DE DATOS TIPOS DE ARCHIVOS Ficheros de datos primarios: cada BASE DE DATOS tiene un único fichero de datos primario con la extensión por defecto .MDF. El fichero de datos primario es el único que tiene no sólo la información contenida en la BASE DE DATOS sino también la información sobre la propia BASE DE DATOS Cuando la BASE DE DATOS es creada la ubicación de cada archivo se almacena en la BASE DE DATOS master, pero también se incluye en el fichero de datos primario. Fichero de datos secundarios: una BASE DE DATOS también puede tener uno o varios ficheros de datos secundarios que tienen por defecto la extensión .NDF. Generalmente se utilizan para crear espacio de almacenamiento en unidades de disco distintas a la del fichero de datos primario, o para mantener el tamaño de cada fichero de datos individual en un tamaño máximo práctico, por lo general, por cuestiones de portabilidad. Registro de transacciones: una BASE DE DATOS debe tener al menos un fichero de registro de transacciones con una extensión por defecto .LDF. El registro o log es imprescindible pues sin un registro de transacciones accesible, no se podría realizar ningún cambio en la BASE DE DATOS Para la mayoría de las BASE DE DATOS en producción es crítico un sistema de copia de seguridad adecuado para el registro de transacciones. Cuando se realiza un cambio en la BASE DE DATOS se hace en el contexto de una transacción: una o más unidades de trabajo que deben tener éxito o fallar en su conjunto. Cuando se realiza un cambio en la BASE DE DATOS los datos no se escriben directamente en los archivos de datos. En su lugar, el servidor de BASE DE DATOS primero escribe el cambio en el registro de transacciones. Cuando se complete la transacción, se marcará como confirmada en el registro de transacciones. Esto no significa que los datos se muevan desde el registro de transacciones al fichero de datos. Una transacción confirmada sólo significa que todos los elementos de la transacción se han completado satisfactoriamente. El caché de buffer se puede actualizar, pero no necesariamente el fichero de datos. Periódicamente se realiza un punto de control. Esto le indica a SQL Server que se asegure de que todas las transacciones realizadas son escritas en el fichero de datos apropiado. Los puntos de control pueden realizarse por estos motivos: Alguien lanza un comando Checkpoint. Se alcanza el intervalo de recuperación configurado en la instancia (ver imagen). Esto indica con qué frecuencia se producen los puntos de control. Se realiza una copia de seguridad de la BASE DE DATOS (en modo de recuperación simple). La estructura de archivos de BASE DE DATOS se ve alterada (en modo de recuperación simple). El motor de la BASE DE DATOS se ha detenido. El proceso de escritura de datos a disco se realiza de forma automática, como se indica en el siguiente esquema: No se escribe directamente a un fichero .mdf o .ndf. Todos los datos primero van al registro de transacciones (.ldf), siguiendo los pasos siguientes: 1. Se ejecuta algún tipo de comando INSERT, UPDATE o DELETE. 2. Los datos se escriben inmediatamente a la caché de log interna. 3. El caché de log actualiza el fichero de log físico (.ldf) y realiza cambios en el buffer caché de datos. 4. El buffer caché de datos se limpia eventualmente y el fichero de datos (.mdf o .ndf) se actualiza. Cuando se realiza un backup de la BASE DE DATOS, sólo los datos actuales y los ficheros del registro de transacciones se escriben a disco. Las transacciones realizadas que aún no se han escrito a disco permanecerán en el log de transacciones y se actualizan durante el proceso de recuperación mientras la BASE DE DATOS se vuelve a conectar. Las transacciones incompletas se revierten durante la recuperación. MODELOS DE RECUPERACIÓN En este segundo artículo vamos a ver los modelos de recuperación existentes en SQL Server y las características principales de cada uno de ellos. Existen tres modelos de recuperación distintos: Full Recovery, Simple Recovery y Bulk-Logged Recovery. Cada modelo determina como debería comportarse el registro de transacciones, tanto en el uso activo como durante el backup del registro de transacciones. Full Recovery (recuperación completa): modelo de recuperación por defecto para una BASE DE DATOS recién creada. Todo el historial de transacciones se mantiene en el registro de transacciones que deberíamos administrar. Su ventaja es que se puede restaurar hasta el momento en el que una BASE DE DATOS falla, o hasta un punto específico en el tiempo. Su desventaja es que si lo dejamos sin administrar, el registro de transacciones puede crecer rápidamente. Si se llena el disco, lo más probable es que la BASE DE DATOS falle. Este modo no ofrece ningún tipo de mantenimiento automatizado del registro de transacciones. La única forma de limpiar es cambiar al modo de recuperación simple o realizar una copia de seguridad del registro de transacciones. Simple Recovery (recuperación simple): limpia el registro de transacciones activo de las transacciones realizadas durante un checkpoint. Su ventaja es que el registro de transacciones se mantiene y no necesita una administración directa. El inconveniente es que, en caso de desastre, está garantizado un cierto nivel de pérdida de datos. Es imposible restaurar el momento exacto del fallo. A pesar de que este modo de recuperación trunca el registro de transacciones periódicamente, esto no conlleva a que no crezca. Podemos fijar el tamaño del registro de transacciones a un determinado tamaño, pero si se ejecuta una inserción masiva (Bulk Insert), SQL Server lo tratará como una inserción simple. Por tanto, no sirve como respuesta para el mantenimiento de logs por lo que no deberíamos usarlo en BASE DE DATOS en producción. Bulk-Logged Recovery (recuperación de registros masiva): Cuando se utiliza este modelo, el registro de transacciones se comporta exactamente como si se estuviese en modo de recuperación completa, con una excepción: las operaciones masivas se registran mínimamente. Las operaciones masivas incluyen: o Importaciones masivas de datos, que se podrían realizar haciendo uso de BCP (Bulk Copy Program), el comando BULK INSERT u OPENWROWSET con la cláusula Bulk. o Operaciones LOB (Large OBject), tales como WRITETEXT o UPDATETEXT, para columnas NText o Image. o Cláusulas SELECT INTO. o CREATE INDEX, ALTER INDEX, ALTER INDEX REBUILD o DBCC REINDEX. Como todas estas operaciones se registran en el modelo Full Recovery, el registro de transacciones puede crecer enormemente. La ventaja del modelo Bulk-Logged Recovery es que previene de un crecimiento del registro no deseado o imprevisto. Cada vez que se realiza una operación de registro masiva, SQL Server sólo registra los identificadores de las páginas de datos que se han visto afectados. Las páginas SQL Server tienen identificadores internos, así que se puede comprimir un aumento de identificadores de páginas en una pequeña parte del registro. Este modelo de recuperación tiene la desventaja de que la recuperación en un punto del tiempo no es técnicamente posible. Sólo podemos restaurar al punto de la última copia de seguridad del registro de transacciones. Será posible recuperar las transacciones masivas siempre y cuando se disponga de un backup del registro de transacciones que las contiene. Por otra parte, mientras que el registro de transacciones activo en este modo es más pequeño, la copia de seguridad del log contiene copias de las páginas modificadas durante el proceso de carga masiva. El resultado son copias del registro de transacciones potencialmente grandes. La clave para utilizar este modelo de recuperación de forma efectiva es invocarlo sólo cuando sea necesario, y a continuación, volver al modo de recuperación completa para un punto en el tiempo del backup. Se trata de un procedimiento de riesgo, y para mover entre Bulk-Logged Recovery y Full Recovery, siempre debemos seguir los siguientes pasos: 1. 2. 3. 4. Cambiar de Full Recovery a Bulk-Logged Recovery. Realizar la operación de registro masivo. Una vez completado, volver inmediatamente al modo Full Recovery. Realizar un backup completo de la BASE DE DATOS TRANSACT-SQL BÁSICO PARA LA REALIZACIÓN DE BACKUPS Backups completos. Es necesario establecer un punto de referencia inicial, independientemente del modo de recuperación que vayamos a emplear. Para ello, crearemos un backup completo de nuestra BASE DE DATOS La cláusula en T-SQL es: BACKUP DATABASE Nombre_BASE DE DATOS TO DISK = 'Unidad:\ruta\ficherobackup.bak' WITH INIT Con el parámetro WITH INIT nos aseguraremos de que el fichero de backup contiene una única copia de seguridad ya que, por defecto, el comando BACKUP lo añade al fichero existente. De esta forma nos aseguramos que el fichero se sobreescribe. En los modos Full-Recovery o Bulk-Logged Recovery, los backups de los logs son esenciales, no sólo por el propósito de recuperación, sino también para controlar el tamaño del registro de transacciones activo. El modo de recuperación simple es el único que elimina las transacciones periódicamente. Si nunca se realiza un respaldo, el registro de transacciones de una BASE DE DATOS en modo FullRecovery o Bulk-Logged Recovery continuará creciendo hasta consumir todo el espacio disponible en disco. Y si el disco se queda sin espacio, el gestor de la BASE DE DATOS se parará. El comando para realizar el backup del fichero de log es: BACKUP LOG Nombre_Log_BASE DE DATOS TO DISK = 'Unidad:\ruta\ficherobackup.trn' Cada acción contra la BASE DE DATOS se asigna a un Log Sequence Number (LSN). Para restaurar a un punto específico en el tiempo, debemos tener un continuo registro de LSNs. Backups diferenciales. La restauración de los bakups de transacciones tiende a ser una operación lenta, especialmente si nuestro backup completo es semanal, o incluso superior en su programación en el tiempo. Los backups diferenciales intentan decrementar el tiempo de recuperación. La cláusula T-SQL es: BACKUP DATABASE Nombre_BASE DE DATOS TO DISK = 'Unidad:\ruta\ficherobackup.dif' WITH DIFFERENTIAL, INIT El backup "ficherobackup.dif" contiene todos los cambios realizados desde el último backup completo. Podemos utilizarlo durante el proceso de restauración en combinación a los backups del registro de transacciones. En primer lugar, restaurando el backup completo, seguido de la restauración del último diferencial, y a continuación restaurando cualquier log de transacciones posterior. Consideraremos los siguientes elementos cuando hagamos uso de backups diferenciales: Si no realizamos backups completos frecuentemente, los backups diferenciales crecerán significativamente en tamaño para una BASE DE DATOS operativa. Recordemos que un backup diferencial contiene todos los cambios desde el backup completo más reciente. Cuanto mayor sea el tiempo entre backups completos, más cambios recogerán en los diferenciales. Un backup diferencial está directamente ligado a un específico backup completo. La realización de un backup completo fuera del calendario habitual de backups puede hacer inservible una copia diferencial. A través del examen de la frecuencia de backups diferenciales, se puede establecer un plan de copias de seguridad. Cuando la BASE DE DATOS sufre cambios frecuentemente, los backups diferenciales pueden consumir bastante espacio. Deberemos encontrar el equilibrio entre la velocidad de recuperación necesaria con respecto al espacio disponible. Verificación de errores. El proceso de backup puede realizar una verificación de los datos mientras se están respaldando ya sea verificando páginas dañadas o validando por checksums. Debemos habilitar cualquier opción que deseemos en el nivel de la base de datos. La opción "Verificación de páginas" nos permitirá descubrir e informar sobre transacciones de E/S incompletas debidas a errores de E/S de disco. Podremos elegir entre las opciones "Ninguna", "checksum" y "TornPageDetection". La opción "TornPagesDetection" (detección de páginas dañadas) verifica simplemente cada página de datos para ver si un proceso de escritura se ha completado en su totalidad. Si encuentra una página que ha sido sólo parcialmente escrita (debido a algún tipo de fallo hardware) simplemente se marca como "dañado". La validación por comprobación de sumas ("Checksum") es una técnica de verificación de páginas que añade un valor para cada página de datos, esencialmente identificando el tamaño exacto en bytes de cada página. El proceso de backup puede validar por checksum comparando el valor almacenado en la base de datos con el valor asociado con la página de datos escrita en disco. Sin embargo, no lo hace por defecto. Si la validación por checksums está habilitada, podemos forzar el proceso de backup para realizar está validación. BACKUP DATABASE Nombre_BASE DE DATOS TO DISK = 'unidad:\ruta\fichero.back' WITH CHECKSUM Cuando se encuentre con un error durante la validación por checksum, SQL Server escribirá un registro a MSDB..SUSPECT_PAGE. El comportamiento por defecto es STOP_ON_ERROR, permitiendo corregir el problema y continuar, lanzando el mismo comando con la adicción de RESTART. La otra opción de validación por checksum es CONTINUE_ON_ERROR. Cuando está habilitada, el backup simplemente escribe el error en la tabla MSDB..SUSPECT_PAGE y continua. Sin embargo, esta tabla tiene un límite de 1000 filas, y si se alcanza, el backup fallará. El habilitar la validación por checksum obviamente tiene un impacto en el rendimiento del proceso de backup Backups divididos (striped). Algunas BASE DE DATOS son demasiado grandes para crear un backup completo en una única cinta LTO o en un array de discos. En estos casos, podemos hacer uso de los backup striped, también denominados multiplexados. La ventaja es que cada dispositivo utiliza la totalidad de su capacidad para crear el backup. Su desventaja es que, en caso de fallo, todas las cintas o ficheros se necesitarán para completar una restauración. Para crear un backup striped utilizaremos: BACKUP DATABASE Nombre_BASE DE DATOS TO DISK = 'unidad1:\ruta1\fichero1.bak' , DISK = 'unidad2:\ruta2\fichero2.bak', DISK = 'unidad3:\ruta3\fichero3.bak' WITH INIT, CHECKSUM El backup será "extendido" a través de todos los ficheros indicados. Backups en espejo (mirrored). Los backups en espejo son una característica incorporada desde la versión 2005 de SQL Server, y que nos permite escribir el mismo fichero de backup en múltiples ubicaciones. El comando para crear un backup en espejo es BACKUP DATABASE Nombre_BASE DE DATOS TO DISK = 'unidad1:\ruta1\fichero1.bak' MIRROR TO DISK = 'unidad2:\ruta2\fichero2.bak' MIRROR TO DISK = 'unidad3:\ruta3\fichero3.bak' WITH FORMAT, CHECKSUM La única restricción para realizar respaldos en múltiples localizaciones es que los dispositivos utilizados deben ser idénticos. En particular, múltiples dispositivos de cinta deben ser del mismo modelo del mismo fabricante. Backups Sólo_copia (copy_only). Podemos utilizar backup para propósitos distintos a los de recuperación en caso de desastre. Por ejemplo, un uso típico es utilizar un backup para mover una copia de la BASE DE DATOS a un entorno de desarrollo. Como ya hemos indicado, los backups diferenciales están asociados directamente a un único backup completo. Desde SQL Server 2005 existe una característica, el backup "copy-only", que no resetea la cadena de backups. Cualquier copia de seguridad realizada fuera del esquema de los backups estándar debería ser realizada como un backup "only-copy": BACKUP DATABASE Nombre_BASE DE DATOS TO DISK = 'unidad:\ruta\ficheroBackup.bk' WITH INIT, CHECKSUM, COPY_ONLY Si existe un calendario de respaldos en SQL Server, para cada respaldo creado existe un número de secuencia o LSN. Entonces si creamos un backup completo sin hacer uso de la opción "copy-only", esa secuencia se verá afectada y tendremos problemas para recuperar un respaldo diferencial realizado posteriormente. Necesitaremos contar con el respaldo completo que se realizó en medio de la secuencia entonces no podremos recuperar la información. Si queremos conocer el número de secuencia podemos lanzar esta query: SELECT database_name, backup_start_date, is_copy_only, first_lsn FROM msdb..backupset WHERE database_name = ''Nombre_BASE DE DATOS' ORDER BY backup_start_date DESC MOVER LOS FICHEROS DE DATOS El método apropiado para reubicar ficheros en estas últimas versiones es con ALTER DATABASE … MODIFY FILE. Ejecutaremos este comando para cada fichero que queramos mover y conmutaremos el estado de la base de datos a online/offline. ALTER DATABASE BASE DE DATOS SET OFFLINE; ALTER DATABASE BASE DE DATOS MODIFY FILE ( NAME='BASE DE DATOS_Log', FILENAME='Unidad:\ruta\BASE DE DATOS_Log.ldf'); ALTER DATABASE BASE DE DATOS SET ONLINE; RESTAURACIÓN DE LA BASE DE DATOS En primer lugar vamos a realizar una copia de seguridad completa de la BASE DE DATOS. Para ello abriremos el analizador de consultas (en este ejemplo utilizo un SQL Server 2000 aunque sería lo mismo pero a través del Management Studio de SQL Server 2005/8) y nos conectaremos al servidor origen. Lanzamos el backup completo: BACKUP DATABASE WSS_Content TO DISK = 'E:\bk_WSS_Content.bk' WITH INIT Y a continuación haremos lo propio con el registro de transacciones: BACKUP LOG WSS_Content TO DISK = 'E:\bk_WSS_Content_log.trn' Para realizar la restauración deberemos tener en cuenta que la ubicación de la base de datos, al tratarse de otro servidor, puede ser diferente. Para ello haremos uso de la opción MOVE que nos servirá para indicar la ubicación tanto del fichero de datos como del registro de transacciones. Para conocer la ubicación en disco exacta pulsaremos con el botón secundario del ratón sobre la BASE DE DATOS sobre la que vayamos a realizar la restauración, y seleccionamos "Propiedades". En las pestañas "Archivo de datos" y "Registro de transacciones" de la ventana que se abre, podremos conocer la ubicación de estos dos ficheros: Vamos a partir del supuesto que la base de datos ya existe en el servidor destino. En caso negativo, deberemos crear la base de datos (simplemente crearemos una BASE DE DATOS nueva, con el mismo nombre). Empezamos el proceso de restauración propiamente dicho. En primer lugar deberemos poner nuestra base de datos en modo monousuario o exclusivo, para lo cual nos conectaremos al servidor destino mediante el analizador de consultas de SQL Server y ejecutaremos la sentencia: ALTER DATABASE WSS_Content SET Single_User; Podemos "expulsar" a los usuarios inmediatamente o tras un determinado tiempo haciendo de la opción ROLLBACK, para lo cual añadiremos a la sentencia anterior WITH ROLLBACK AFTER segundos o WITH ROLLBACK IMMEDIATE. Y una vez realizado este paso, podemos comenzar el proceso de restauración. En primer lugar hacemos uso del Full Backup: RESTORE DATABASE WSS_Content FROM DISK = 'E:\backupsProduccion\bk_WSS_Content.bak' WITH MOVE 'WSS_Content' TO 'E:\Archivos de Programa\Microsoft SQL Server\MSSQL\Data\WSS_Content_Data.mdf', MOVE 'WSS_Content_log' TO 'E:\Archivos de Programa\Microsoft SQL Server\MSSQL\Data\WSS_Content_log.ldf', NORECOVERY Indicando la opción NORECOVERY. En el caso de que tuviésemos incrementales, deberíamos ir haciendo restauraciones sucesivas de cada uno de estos backups. A continuación, restauramos el registro de transacciones: RESTORE LOG WSS_Content FROM DISK = 'E:\backupsProduccion\bk_WSS_Content_log.trn' WITH RECOVERY O si queremos hacer una restauración hasta un punto concreto en el tiempo podemos hacer uso de la opción STOPAT: RESTORE LOG WSS_Content FROM DISK = 'E:\backupsProduccion\bk_WSS_Content_log.trn' WITH STOPAT = N'4/28/2010 11:01:45 PM', RECOVERY Una vez finalizado el proceso de restauración, ponemos la BASE DE DATOS en modo multiusuario: ALTER DATABASE WSS_Content SET Multi_User;