Download RESPALDO Y RESTAURACIÓN DE BASE DE DATOS - UT-AGS

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;