SQL SERVER 2022 –

NIVEL BÁSICO
Capítulo 4: Recuperación de desastres
OBJETIVOS
Al finalizar el capítulo, el alumno logrará:

• Diseñar estrategias para un adecuado respaldo de

información.
• Restaurar una base de datos en función a las
estrategias de respaldo de bases.
• Almacenar y gestionar las copias de respaldo, haciendo
uso de las capacidades de Azure.
AGENDA
1. Registro de transacciones en SQL Server 2022
2. Modelos de recuperación de SQL Server 2022
3. Tipos de backup
4. Estrategias de backup
5. Compresión de backup
6. Respaldando una base de datos y el registro de transacciones
7. Respaldo de base de datos en la nube
8. Administración de copias de respaldo
9. Copia de respaldo de bases de datos del sistema
[Link]ón de bases de datos
[Link]ón de bases de datos del sistema
1. REGISTRO DE
TRANSACCIONES EN SQL
SERVER 2022
• El registro de transacciones es clave para la confiabilidad de
SQL Server.

• Varias formas de configurar SQL Server para determinar cómo

va a operar el log de transacciones.
− Modelo de recuperación
− Capacidad de almacenamiento o el tamaño del archivo del log.

• Conocer cómo funciona el registro de transacciones también es

clave para diseñar la estrategia de recuperación de desastres
adecuada para su organización.
1. REGISTRO DE
TRANSACCIONES EN SQL
SERVER 2022
1 Se inicia la Transacción

3 Actualiza
TRAN LOG

2
Cambios en el
Buffer Cache
4 Checkpoint
actualiza
DATA FI LE
1. REGISTRO DE
TRANSACCIONES EN SQL
SERVER 2022
Controles por parte del motor de base de datos para la
implementación de transacciones:

• Atomicidad
Requiere que una transacción se complete en su totalidad o no se
complete en absoluto, es decir, que todas las sentencias se
ejecuten correctamente. Basta que una falle para que todas las
demás se deshagan (rollback).

• Durabilidad
Una vez que la transacción se complete, debe recuperarse si es
que el sistema se reinicia, inclusive si el reinicio es provocado por
una falla del sistema.
1. REGISTRO DE
TRANSACCIONES EN SQL
SERVER 2022
Planeamiento de la capacidad del transaction log

En base a estrategia de copia de seguridad de la base de datos.

Cuando el registro de transacciones está lleno, SQL Server puede
impedir la actividad de la base de datos.
Para evitar este problema, se pueden escoger entre las siguientes
acciones:

• Establecer el modelo de recuperación de la base de datos como

simple.
• Borrar periódicamente el registro de transacciones mediante
copias de seguridad del log de transacciones, de manera
periódica, varias veces al día, dependiendo del tamaño de la
2. MODELOS DE
RECUPERACIÓN DE SQL
SERVER 2022
MODELO DE
RECUPERACIÓ DESCRIPCIÓN
N

Usa copias completas o diferenciales de la base de

Simple datos. Trunca los registros de transacciones
automáticamente.

Usa copias completas o diferenciales de la base de

datos y copia de registro de transacciones. No
Completa
trunca los registros de transacciones
automáticamente.

Incluye copias de seguridad de la base de datos y

Registro del registro de transacciones, pero usa menos
masivo espacio de registro para ciertas operaciones de
carga masiva.
2. MODELOS DE
RECUPERACIÓN DE SQL
SERVER 2022
Configuración del modelo de recuperación

Propiedades de la base de datos

Transact-SQL:
3. TIPOS DE BACKUP
Backup Full

• Captura toda la data que se encuentra almacenada dentro de una base de

datos.
• Se utiliza para recrear una base de datos en su totalidad.
• Disponible sin importar el modelo de recuperación.

INIT: Sobre escribe un archivo

existente.
3. TIPOS DE BACKUP
Backup diferencial
• Capturan, únicamente, la data que ha sido modificada desde el último backup
full.
• Disponible sin importar el modelo de recuperación.
• Cada backup diferencial contiene toda la información de los previos backups
diferenciales.
• No es un backup incremental.
3. TIPOS DE BACKUP
Backup de Transaction Log

• Solo puede ser ejecutado sobre bases de datos con modelo de recuperación
Full o Bulk-Logged.
• Solo podrán ejecutarse después que se haya completado un full backup.
• Los Log Backups contienen el active log.
• SQL Server realiza un backup de todas las transacciones hasta que se
encuentra con una transacción abierta.
4. ESTRATEGIAS DE BACKUP
El diseño de una estrategia de backup y restore, incluye:

1. Analizar requerimientos del negocio para recuperación de información, para

cada base de datos dentro de una organización.
2. Categorizar las bases de datos por criterios de recuperación.
• Bases de datos críticas con elevada actividad.
• Bases de datos críticas con baja actividad.
• Bases de datos no críticas.
3. Asignar un modelo de recuperación por cada categoría.
4. Especificar los tipos de backup requeridos por cada categoría.
5. Especificar una política de frecuencia de backups para cada categoría.
6. Determinar las políticas de seguridad para cada categoría.
7. Documentar la estrategia de backups.
8. Crear validaciones de backups y políticas de pruebas.
9. Crear una política de notificación de backups fallidos.
4. ESTRATEGIAS DE BACKUP
Estrategia de Full Diario

• La base de datos es pequeña.

• La base de datos sufre pocas modificaciones de datos.

FULL FULL FULL

LUNES MARTES MIÉRCOLES ……..

4. ESTRATEGIAS DE BACKUP
Estrategia de Full Diario más transaction log

• La base de datos es pequeña/mediana.

• La base de datos sufre muchas modificaciones de datos.
• Se requiere recuperar la base de datos hasta el punto de falla.

FULL FULL
Log Log Log

LUNES 9am 12m 5pm MARTES ……..

4. ESTRATEGIAS DE BACKUP
Estrategia de Full y diferencial

• La base de datos es grande.

• Solo un pequeño porcentaje de la BD es modificado.
DIFERENCIAL
DIFERENCIAL
DIFERENCIAL

FULL

LUNES MARTES MIÉRCOLES JUEVES ……..

4. ESTRATEGIAS DE BACKUP
Estrategia de Full más diferencial más transaction log

• La base de datos es mediana/grande.

• La base de datos sufre muchas modificaciones de datos.
• Se requiere recuperar la base de datos hasta el punto de falla.
DIFERENCIAL

DIFERENCIAL

FULL
Log Log Log Log Log Log

LUNES 9am 12m 5pm MARTES 9am 12m 5pm MIÉRCOLES …..
5. COMPRESIÓN DE BACKUP
Ventaja
Al comprimir un respaldo, éste tomará menos tiempo en ejecutarse y menos accesos a
los discos (IO).

Desventaja
Alto costo en CPU, por lo que se recomienda tener cuidado al generar el respaldo, ya que
podría afectar a otras operaciones que se estén realizando en el mismo equipo durante la
ejecución.
5. COMPRESIÓN DE BACKUP
Habilitar compresión de backup

Propiedades de la instancia en Management Studio:

5. COMPRESIÓN DE BACKUP
Ejecutar un backup con compresión

Ventana de copia de seguridad de Management Studio, opciones de backup.

5. COMPRESIÓN DE BACKUP
5. COMPRESIÓN DE BACKUP
Demostración de compresión de backup

En esta demostración, podrá ver cómo se comprime un backup de

SQL Server.
6. RESPALDANDO UNA BASE DE DATOS
Y EL REGISTRO DE TRANSACCIONES
Ejecutar un backup full

Utilizando un dispositivo de backup.

Con inicialización para sobre escritura.

6. RESPALDANDO UNA BASE
DE DATOS Y EL REGISTRO DE
TRANSACCIONES
Ejecutar un backup diferencial

Utilizando una ubicación en un disco físico.

6. RESPALDANDO UNA BASE
DE DATOS Y EL REGISTRO DE
TRANSACCIONES
Ejecutar un backup de transaction log
Utilizando un dispositivo de backup.

Backup del tail log.

6. RESPALDANDO UNA BASE
DE DATOS Y EL REGISTRO DE
TRANSACCIONES
Demostración de backup

En esta demostración, podrá ver cómo se ejecutan los diferentes

backups de una base de datos.
7. RESPALDO DE BASE DE
DATOS EN LA NUBE
Pasos para respaldar una base de datos en la nube

Crea una cuenta de almacenamiento en Azure.

• El proceso crea una llave automáticamente.
• Crea una credencial en SQL server.
• Utilizando la llave de la cuenta de
almacenamiento de Azure.

Realiza la copia de seguridad.

• Utilizando destino URL.
1. Realiza la recuperación de la base de datos.
• Utilizando origen URL.
7. RESPALDO DE BASE DE
DATOS EN LA NUBE
Realiza la copia de seguridad, utilizando destino URL
Cuenta de almacenamiento

Contenedor

Realiza la restauración, utilizando destino URL

7. RESPALDO DE BASE DE
DATOS EN LA NUBE
Demostración de backup en la nube

En esta demostración, podrá ver cómo se ejecuta un backup en la

nube.
8. ADMINISTRACIÓN DE COPIAS
DE RESPALDO
Medios:

• Cinta.
• Disco (local o de red).

Garantizar integridad de backup:

• Copias de respaldo espejadas (Mirror, solo edición Enterprise).

• Checksum en el backup y en el restore.
8. ADMINISTRACIÓN DE COPIAS
DE RESPALDO
Estrategia de prueba de recuperación

• RESTORE VERIFYONLY

• Prueba de restauración:
a. Verificar cuáles son los archivos contenidos en el backup.
b. Restaurar la base de datos de prueba en una ubicación alternativa, en estado de
recuperación.
8. ADMINISTRACIÓN DE COPIAS
DE RESPALDO
Estrategia de prueba de recuperación

c. Recuperar la base de datos de prueba, pero con acceso restringido.

8. ADMINISTRACIÓN DE COPIAS
DE RESPALDO
Estrategia de prueba de recuperación

d. Ejecutar una verificación de la base de datos de prueba.

e. Eliminar la base de datos de prueba.

9. COPIA DE RESPALDO DE
BASES DE DATOS DEL
SISTEMA
master
• Backup luego de ejecutar ciertas instrucciones o procedimientos almacenados
del sistema, SQL Server modifica automáticamente la base de datos master.
• Realiza una copia de seguridad de la base de datos master, cuando ejecute
una de las operaciones siguientes:
− Creación, modificación o eliminación de bases de datos de usuario.
− Creación de objetos a nivel de servidor.
− Creación, modificación o eliminación de inicios de sesión.

msdb
Backup luego de modificarla. Msdb contiene información sobre jobs, alertas y
operadores usados por el Agente SQL Server.
NOTA: se recomienda establecer backups periódicos automáticos de estas bases de datos del sistema para
asegurarse que todos los cambios estén respaldados.
10. RESTAURACIÓN DE BASES
DE DATOS
Accelerated Database Recovery

• Nueva característica desde en SQL SERVER 2019

• La recuperación acelerada de bases de datos está disponible en SQL Server

2022 (16.x) y Azure SQL Database.

• La recuperación acelerada de bases de datos mejora considerablemente la

disponibilidad de la base de datos, especialmente, en presencia de
transacciones de larga duración.
10. RESTAURACIÓN DE BASES
DE DATOS
Demostración de Accelerated Database Recovery

En esta demostración, podrá ver la mejora al setear una base de

datos en Accelerated Database Recovery.
10. RESTAURACIÓN DE BASES
DE DATOS
Restore - Full Backup

• Restaura la base de datos completa desde un backup full.

• Si la base de datos ya existe en la instancia, sobrescribe la base de datos con
el mismo nombre.
• Si no existe, crea los datafiles y filegroups antes de realizar el restore.

WITH RECOVERY: la base de datos se coloca online y se aceptan

transacciones.
WITH NORECOVERY: la base de datos o filegroup permanece en estado
RESTORING. Es posible restaurar backups adicionales, tal como, diferencial o
10. RESTAURACIÓN DE BASES
DE DATOS
Restore – Backup Diferencial

• Para restaurar un backup diferencial, primero debe restaurar un backup full

con la opción NORECOVERY
10. RESTAURACIÓN DE BASES
DE DATOS
Restore – Backup Transaction Log

• Restauran la base de datos a determinado punto del tiempo.

• Aplica solo para los modelos de recuperación Completo (Full) y de Registro
Masivo (Bulk Logged).
• Ejemplo con backup full, diferencial y log.
10. RESTAURACIÓN DE BASES
DE DATOS
Restore – Backup Transaction Log

• Ejemplo con backup full y dos backup de log:

10. RESTAURACIÓN DE BASES
DE DATOS
Demostración de backup

En esta demostración, podrá ver cómo se restaura una base de

datos.
11. RESTAURACIÓN DE BASES
DE DATOS DEL SISTEMA
Pasos para restaurar las bases de datos del sistema

1. Restaure la base de datos master. Si no existe ningún backup válido

de la master, se deberán crear manualmente los datos.

2. Restaure la base de datos msdb. Se debe restaurar la base de datos

msdb después de hacer una restauración de la master. Cuando se vuelve a
generar la base de datos master, la base de datos msdb se elimina y, a
continuación, se vuelve a crear; por consiguiente, se pierde toda la
información.

3. Si se han hecho modificaciones a la model, se restaura la base de

datos model desde el backup más reciente.
11. RESTAURACIÓN DE BASES
DE DATOS DEL SISTEMA
Pasos para restaurar las master

1. Inicie SQL Server en modo de usuario único. Desde la ventana de comandos

del sistema, ubicarse en la carpeta de instalación de SQL Server y escriba el
siguiente comando:

2. O añada el parámetro –m en las propiedades del servicio desde

Configuration Manager, en la ficha Parámetros de Inicio.

3. Restaure la base de datos master desde la copia de seguridad más reciente,

a través de SQL CMD.
11. RESTAURACIÓN DE BASES
DE DATOS DEL SISTEMA
Demostración de restauración de la master

En esta demostración, podrá ver cómo se restaura la base de

datos master.
EJERCICIO Nº 1: DISEÑO DE
UNA ESTRATEGIA DE COPIA DE
RESPALDO
En este laboratorio, podrá:

• Diseñar una estrategia de copia de respaldo.

EJERCICIO Nº 2: RESPALDO Y
RESTAURACIÓN DE UNA BASE DE
DATOS DE USUARIO
En este laboratorio, podrá:

• Respaldar y restaurar una base de datos de usuario on premise

y off premise.
EJERCICIO Nº 3: RESPALDO Y
RESTAURACIÓN LA BASE DE
DATOS MÁSTER
En este laboratorio, podrá:

• Respaldar y restaurar la base de datos master.

TAREA: PROFUNDIZAR LOS CONCEPTOS
DE RECUPERACIÓN DE DESASTRES

Al finalizar la tarea, podrá:

• Aplicar los conocimientos adquiridos en clase a las bases de

datos que administra en su ambiente de trabajo.
• Ampliar conocimientos sobre políticas de retención de copias de
seguridad.
LECTURAS ADICIONALES
Se sugiere revisar los siguientes enlaces para profundizar en los conceptos
tratados en el presente capítulo:

• Copia de seguridad de base de datos

[Link]
k-up-database-general-page?redirectedfrom=MSDN&view=sql-server-ver16

• BACKUP
[Link]
irectedfrom=MSDN&view=sql-server-ver16

• Crear una copia de seguridad completa de base de datos

[Link]
te-a-full-database-backup-sql-server?redirectedfrom=MSDN&view=sql-server-
ver16
LECTURAS ADICIONALES
Se sugiere revisar los siguientes enlaces para profundizar en los conceptos
tratados en el presente capítulo:

• Copias de seguridad diferenciales

[Link]
erential-backups-sql-server?redirectedfrom=MSDN&view=sql-server-ver16

• Copias de seguridad de registros de transacciones

[Link]
saction-log-backups-sql-server?redirectedfrom=MSDN&view=sql-server-ver16

• Copia de seguridad de SQL Server en Microsoft Azure

[Link]
server-managed-backup-to-microsoft-azure?redirectedfrom=MSDN&view=sql
-server-ver16
RESUMEN
En este capítulo, usted aprendió a:

• Identificar las diferentes formas de respaldar una base de datos,

además de diseñar estrategias para un adecuado respaldo de
información.
• Restaurar una base de datos en función a las estrategias de
respaldo de bases, aprovechando las capacidades de Azure para
almacenar y gestionar las copias de respaldo.