Hacer copias de seguridad y restaurar bases de datos de SQL Server

Se aplica a:SQL Server

Este artículo describe los beneficios de hacer copias de seguridad de bases de datos de SQL Server, introduce términos básicos de copia de seguridad y restauración, y aborda estrategias de copia de seguridad y consideraciones de seguridad para SQL Server.

El componente de copia de seguridad y restauración de SQL Server proporciona una salvaguarda esencial para los datos críticos almacenados en tus bases de datos de SQL Server. Para minimizar el riesgo de pérdida catastrófica de datos, haz copias de seguridad regulares de tus bases de datos para preservar modificaciones en tus datos. Una estrategia bien planificada de copias de seguridad y restauración ayuda a proteger las bases de datos frente a la pérdida de datos causada por muchos tipos de fallos. Pon a prueba tu estrategia restaurando un conjunto de copias de seguridad y luego recuperando tu base de datos, para estar listo para responder ante un desastre.

Además del almacenamiento local, SQL Server también soporta copias de seguridad y restauración desde Azure Blob Storage. Para más información, consulte Copia de seguridad y restauración de SQL Server con Azure Blob Storage. Para archivos de base de datos almacenados mediante Azure Blob Storage, SQL Server 2016 (13.x) proporciona la opción de usar instantáneas de Azure para copias de seguridad casi instantáneas y restauraciones más rápidas. Para obtener más información, consulte Copias de seguridad mediante instantáneas de archivos de bases de datos en Azure. Azure ofrece también una solución de copia de seguridad de clase empresarial para las instancias de SQL Server que se ejecutan en máquinas virtuales de Azure. Una solución de copia de seguridad totalmente administrada admite Grupos de disponibilidad Always On, retención a largo plazo, recuperación a un momento dado y administración y supervisión centrales. Para más información, consulte Acerca de la copia de seguridad de SQL Server en máquinas virtuales de Azure.

Por qué realizar copias de seguridad

• Hacer copias de seguridad de tus bases de datos de SQL Server, ejecutar procedimientos de restauración de pruebas en tus copias de seguridad y almacenar copias de seguridad en un lugar seguro y fuera del sitio te protege de una posible pérdida catastrófica de datos. Las copias de seguridad son la única forma de proteger los datos.

  Con las copias de seguridad válidas de una base de datos puede recuperar los datos en caso de que se produzcan errores, por ejemplo:

  • Errores de medios.

  • Errores de usuario, por ejemplo, eliminar una tabla por error.

  • Errores de hardware, por ejemplo, una unidad de disco dañada o la pérdida permanente de un servidor.

  • Desastres naturales. Utilizando SQL Server Backup a Azure Blob Storage, puedes crear una copia de seguridad fuera del sitio en una región diferente a la de tu ubicación local, para usarla si un desastre natural afecta a tu ubicación local.

• Además, las copias de seguridad de una base de datos son útiles para tareas administrativas rutinarias, como copiar una base de datos de un servidor a otro, configurar grupos de disponibilidad Always On, crear reflejo de la base de datos y archivar.

Glosario de términos de copia de seguridad

Estrategias de copias de seguridad y restauración

Debes personalizar las estrategias de copia de seguridad y restauración según tu entorno y los recursos disponibles. Una recuperación fiable requiere una estrategia de respaldo y restauración. Una estrategia bien diseñada equilibra los requisitos empresariales para la máxima disponibilidad y mínima pérdida de datos con el coste de mantener y almacenar copias de seguridad.

Una estrategia de copia de seguridad y restauración contiene una parte de copia de seguridad y una parte de restauración. La parte de copia de seguridad define el tipo y la frecuencia de las copias de seguridad, el tipo y la velocidad del hardware que requieren, cómo probar las copias de seguridad y dónde y cómo almacenar el soporte de copia de seguridad (incluyendo consideraciones de seguridad). La parte de restauración define quién es responsable de realizar restauraciones, cómo realizar restauraciones para alcanzar tus objetivos de disponibilidad de bases de datos y pérdida mínima de datos, y cómo probar restauraciones.

Una estrategia eficaz de copia de seguridad y restauración requiere una planificación, implementación y pruebas cuidadosas. Se requieren pruebas. No tienes una estrategia de copia de seguridad hasta que restauras con éxito las copias de seguridad en todas las combinaciones incluidas en tu estrategia de restauración y pruebas cada base de datos restaurada para comprobar la consistencia física. Considera varios factores, entre ellos:

• Los objetivos de la organización con respecto a las bases de datos de producción, especialmente los requisitos de disponibilidad y protección de los datos contra la pérdida o los daños.

• La naturaleza de cada una de las bases de datos: el tamaño, los patrones de uso, la naturaleza del contenido, los requisitos de los datos, etc.

• Restricciones en los recursos, como hardware, personal, espacio para almacenar medios de copia de seguridad, la seguridad física del medio almacenado, etc.

Mejores prácticas recomendadas

No concedas a las cuentas que realizan operaciones de respaldo o restauración más privilegios de los necesarios. Para obtener más información, consulta copia de seguridad y restauración para consultar los detalles específicos de los permisos. Cifra copias de seguridad de bases de datos y, si es posible, comprimelas .

Utiliza extensiones de archivo consistentes para facilitar la identificación y gestión de copias de seguridad. SQL Server no requiere ni impone estas extensiones, pero la coherencia ayuda en tareas operativas como configurar exclusiones antivirus para archivos de respaldo. Para obtener más información, consulte Configuración del software antivirus para que funcione con SQL Server.

• Los archivos de copia de seguridad de la base de datos deberían tener la .BAK extensión.
• Los archivos de copia de seguridad de registros deben tener la extensión .TRN.

Utilizar almacenamiento separado

Coloca tus copias de seguridad en una ubicación física separada o en un dispositivo distinto de los archivos de la base de datos. Cuando el disco físico que almacena tus bases de datos falla o se bloquea, la recuperación depende de tu capacidad para acceder al disco separado o dispositivo remoto que almacenó las copias de seguridad. Puedes crear varios volúmenes o particiones lógicas desde la misma unidad de disco física. Revisa detenidamente la partición del disco y la disposición de volúmenes lógicos antes de elegir una ubicación de almacenamiento para las copias de seguridad.

Elección del modelo de recuperación adecuado

Las operaciones de copias de seguridad y restauración se producen en el contexto de un modelo de recuperación. El modelo de recuperación es una propiedad de la base de datos que controla la forma en que se administra el registro de transacciones. Por lo tanto, el modelo de recuperación de una base de datos determina qué tipos de escenarios de copia de seguridad y restauración soporta la base de datos, y el tamaño de sus copias de seguridad en los registros de transacciones. Normalmente, en las bases de datos se usa el modelo de recuperación simple o el modelo de recuperación completa. Puede complementar el modelo de recuperación completo cambiando al modelo de recuperación de registro masivo antes de las operaciones de carga masiva. Para obtener una introducción a estos modelos de recuperación y cómo afectan a la administración del registro de transacciones, consulte el registro de transacciones.

La mejor elección de modelo de recuperación de bases de datos depende de los requisitos de tu empresa. Para evitar la administración del registro de transacciones y simplificar la realización de copias de seguridad y restauración, utilice el modelo de recuperación simple. Para minimizar la exposición a la pérdida de trabajo a costa de la sobrecarga administrativa, use el modelo de recuperación completa. Para minimizar el efecto en el tamaño del registro durante operaciones de registro masivo y al mismo tiempo permitir la recuperación de esas operaciones, se utiliza el modelo de recuperación con registro en bloque. Para información sobre el efecto de los modelos de recuperación en la copia de seguridad y restauración, consulte Resumen de Copia de seguridad (SQL Server).

Diseñar la estrategia de copia de seguridad

Después de seleccionar un modelo de recuperación que cumpla con los requisitos de tu negocio para una base de datos específica, planifica e implementa una estrategia de respaldo correspondiente. La mejor estrategia de respaldo depende de varios factores. Los siguientes factores son especialmente importantes:

• ¿Cuántas horas al día necesitan las aplicaciones para acceder a la base de datos?

  Si hay un periodo predecible fuera de las horas punta, deberías programar copias de seguridad completas de la base de datos para ese periodo.

• ¿Cuál es la probabilidad de que se produzcan cambios y actualizaciones?

  Si los cambios son frecuentes, considere:

  • Con el modelo de recuperación simple, puedes programar copias de seguridad diferenciales entre copias completas de la base de datos. Una copia de seguridad diferencial solo incluye los cambios desde la última copia de seguridad de base de datos completa.

  • Bajo el modelo de recuperación completo, puedes programar copias de seguridad frecuentes de los registros. La programación de copias de seguridad diferenciales entre copias de seguridad completas puede reducir el tiempo de restauración al disminuir el número de copias de seguridad del registro que se deben restaurar después de restaurar los datos.

• ¿Es probable que ocurran cambios solo en una pequeña parte de la base de datos o en una gran parte?

  Para una base de datos grande en la que los cambios se concentran en un subconjunto de archivos o grupos de archivos, las copias de seguridad parciales o completas pueden ser útiles. Para obtener más información, vea Copias de seguridad parciales (SQL Server) y Copias de seguridad de archivos completas (SQL Server).

• ¿Cuánto espacio en disco requiere una copia de seguridad completa de una base de datos?

• ¿Hasta qué punto en el pasado su empresa requiere que se mantengan las copias de seguridad?

  Asegúrate de contar con un calendario de copias de seguridad adecuado que se ajuste a las necesidades de la aplicación y a los requisitos empresariales. A medida que las copias de seguridad envejecen, el riesgo de pérdida de datos aumenta a menos que se disponga de una forma de regenerar todos los datos hasta el punto de fallo. Antes de deshacerte de copias de seguridad antiguas debido a limitaciones de almacenamiento, considera si necesitas poder recuperar datos de hace tanto tiempo.

Calcular el tamaño de una copia de seguridad completa de base de datos

Antes de implementar una estrategia de copia de seguridad y restauración, estima cuánto espacio en disco utiliza una copia de seguridad completa de la base de datos. La operación de copia de seguridad copia los datos de la base de datos a un archivo de copia de seguridad. La copia de seguridad contiene solo los datos reales de la base de datos, no ningún espacio no utilizado. Por tanto, la copia de seguridad es normalmente más pequeña que la propia base de datos. Para estimar el tamaño de una copia de seguridad completa de la base de datos, utiliza el sp_spaceused procedimiento almacenado del sistema. Para obtener más información, vea sp_spaceused.

Programar copias de seguridad

Una operación de respaldo tiene un efecto mínimo en las transacciones en ejecución, por lo que puedes hacer copias de seguridad durante las operaciones normales. Puede realizar una copia de seguridad de SQL Server con un efecto mínimo en las cargas de trabajo de producción.

Después de decidir qué tipos de copias necesitas y con qué frecuencia realizar cada uno, programa copias de seguridad regulares como parte de un plan de mantenimiento de bases de datos. Para obtener información acerca de los planes de mantenimiento y de cómo crearlos para las copias de seguridad de bases de datos y las copias de seguridad de registros, vea Use the Maintenance Plan Wizard.

Prueba de las copias de seguridad

No tiene una estrategia de restauración hasta que pruebe las copias de seguridad. Prueba a fondo tu estrategia de copia de seguridad para cada base de datos restaurando una copia de la base de datos en un sistema de prueba. Debe comprobar la restauración de cada tipo de copia de seguridad que pretenda utilizar. Después de restaurar la copia de seguridad, ejecute DBCC CHECKDB en la base de datos para confirmar que el medio de copia de seguridad no está dañado.

Comprobación de la estabilidad y la coherencia de los medios

Utiliza las opciones de verificación que proporcionan las utilidades de copia de seguridad (BACKUPcomando T-SQL, planes de mantenimiento de SQL Server, tu software o solución de copia de seguridad, etc.). Para ver un ejemplo, consulte RESTORE Instrucciones - VERIFYONLY.

Utiliza funciones avanzadas como BACKUP CHECKSUM para detectar problemas en el propio soporte de copia de seguridad. Para obtener más información, consulte Posibles errores de medios durante la copia de seguridad y la restauración (SQL Server).

Estrategia de copia de seguridad y restauración de documentos

Documenta tus procedimientos de copia de seguridad y restauración, y guarda una copia de la documentación en tu libro de pruebas.

También deberías mantener un manual de operaciones para cada base de datos. Este manual de operaciones debe documentar la ubicación de las copias de seguridad, los nombres de los dispositivos de copia de seguridad (si los hay) y el tiempo necesario para restaurar las copias de seguridad.

Riesgo de seguridad de restaurar copias de seguridad de orígenes que no son de confianza

En esta sección se describe el riesgo de seguridad asociado a la restauración de copias de seguridad de orígenes que no son de confianza en cualquier entorno de SQL Server, incluido el entorno local, Azure SQL Managed Instance, SQL Server en Azure Virtual Machines (VM) y cualquier otro entorno.

Por qué esto importa

La restauración de archivos de copia de seguridad de SQL (.bak) presenta un riesgo potencial si la copia de seguridad se origina en un origen que no es de confianza. El riesgo de seguridad se agrava aún más cuando un entorno de SQL Server tiene varias instancias, ya que amplía el área de amenaza. Aunque las copias de seguridad que permanecen dentro de un límite de confianza no suponen ningún problema de seguridad, restaurar una copia de seguridad malintencionada puede poner en peligro la seguridad de todo el entorno.

Un archivo malintencionado .bak puede:

• Tome el control de toda la instancia de SQL Server.
• Escale privilegios y obtenga acceso no autorizado al host subyacente o a la máquina virtual.

Este ataque se produce antes de que se puedan ejecutar scripts de validación o comprobaciones de seguridad, lo que hace que sea extremadamente peligroso. Restaurar una copia de seguridad que no es de confianza equivale a ejecutar aplicaciones que no son de confianza en un servidor o máquina virtual críticos e introducir la ejecución arbitraria de código en el entorno.

procedimientos recomendados

Siga estos procedimientos recomendados de seguridad de copia de seguridad para reducir la amenaza a los entornos de SQL Server:

• Trate la restauración de copias de seguridad como una operación de alto riesgo.
• Reduzca el área de servicio de amenazas mediante instancias aisladas.
• Permitir solo copias de seguridad de confianza: nunca restaure las copias de seguridad de orígenes desconocidos o externos.
• Permitir solo las copias de seguridad que se han mantenido dentro de un límite de confianza: asegúrese de que las copias de seguridad se originen desde dentro del límite de confianza.
• No omita los controles de seguridad para mayor comodidad.
• Habilite la auditoría de nivel de servidor para capturar eventos de copia de seguridad y restauración y mitigar la evasión de auditoría.

Supervisión del progreso con XEvent

Las operaciones de copia de seguridad y restauración pueden tardar mucho debido al tamaño de la base de datos y a la complejidad de las operaciones implicadas. Si surgen problemas con cualquiera de ambas operaciones, utiliza el evento extendido backup_restore_progress_trace para supervisar el progreso en tiempo real. Para obtener más información sobre los eventos extendidos, vea Información general sobre eventos extendidos.

-- Create the backup_restore_progress_trace extended event session
CREATE EVENT SESSION [BackupRestoreTrace] ON SERVER
ADD EVENT sqlserver.backup_restore_progress_trace
ADD TARGET package0.event_file (SET filename = N'BackupRestoreTrace')
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);
GO

-- Start the event session
ALTER EVENT SESSION [BackupRestoreTrace] ON SERVER
STATE = START;
GO

-- Stop the event session
ALTER EVENT SESSION [BackupRestoreTrace] ON SERVER
STATE = STOP;
GO

Salida de ejemplo de Evento Extendido

Más información sobre las tareas de copia de seguridad

• Creación de un plan de mantenimiento
• Creación de un trabajo del Agente SQL Server en SQL Server Management Studio
• Configuración de la programación para el trabajo del Agente SQL Server

Trabajar con dispositivos de copia de seguridad y medios de copia de seguridad

• Definir un dispositivo lógico de copia de seguridad para un archivo de disco (SQL Server)
• Definir un dispositivo lógico de copia de seguridad para una unidad de cinta (SQL Server)
• Especificar un destino de copia de seguridad de disco o cinta (SQL Server)
• Eliminar un dispositivo de copia de seguridad (SQL Server)
• Establecer la fecha de expiración en una copia de seguridad (SQL Server)
• Ver el contenido de una cinta o archivo de copia de seguridad (SQL Server)
• Visualización de los archivos de datos y de registro en un conjunto de copia de seguridad (SQL Server)
• Ver las propiedades y el contenido de un dispositivo lógico de copia de seguridad (SQL Server)
• Restaurar una copia de seguridad desde un dispositivo (SQL Server)

Creación de copias de seguridad

Para las copias de seguridad parciales o de solo copia, utiliza la instrucción Transact-SQL BACKUP con la opción PARTIAL o la opción COPY_ONLY, respectivamente.

Uso de SSMS

• Creación de una copia de seguridad completa de la base de datos
• Copia de seguridad de un registro de transacciones
• Hacer una copia de seguridad de archivos y grupos de archivos
• Crear una copia de seguridad diferencial de la base de datos (SQL Server)

Uso de T-SQL

• Utiliza Resource Governor para limitar el uso de la CPU mediante compresión de respaldo
• Copia de seguridad del registro de transacciones cuando la base de datos está dañada (SQL Server)
• Habilitar o deshabilitar sumas de comprobación de copia de seguridad durante la copia de seguridad o la restauración (SQL Server)
• Especificar la copia de seguridad o restauración para continuar o detener después del error

Restaurar copias de seguridad de datos

Uso de SSMS

• Restauración de una copia de seguridad de base de datos mediante SSMS
• Restauración de una base de datos en una nueva ubicación (SQL Server)
• Restauración de una copia de seguridad diferencial de la base de datos (SQL Server)
• Restaurar archivos y grupos de archivos (SQL Server)

Uso de T-SQL

• Restaurar una copia de seguridad de la base de datos bajo el modelo de recuperación simple
• Restauración de la base de datos al punto de error: recuperación completa
• Restaurar archivos y grupos de archivos sobre archivos existentes (SQL Server)
• Restaurar archivos en una nueva ubicación (SQL Server)
• Restaurar la base de datos maestra

Restauración de registros de transacciones (modelo de recuperación completa)

Uso de SSMS

• Restaurar una base de datos a una transacción marcada (SQL Server Management Studio)
• Restauración de una copia de seguridad del registro de transacciones (SQL Server)
• Restaurar una base de datos de SQL Server a un momento determinado (modelo de recuperación completo)

Uso de T-SQL

• Restaurar una base de datos de SQL Server a un momento determinado (modelo de recuperación completo)
• Reiniciar una operación de restauración interrumpida
• Recuperar una base de datos sin restaurar datos

Contenido relacionado

• Información general de copia de seguridad (SQL Server)
• Introducción a la restauración y recuperación (SQL Server)
• BACKUP (Transact-SQL)
• RESTORE Instrucciones (Transact-SQL)
• Realizar una copia de seguridad y restaurar las bases de datos de Analysis Services
• Hacer copias de seguridad y restaurar catálogos e índices en texto completo
• Copia de seguridad y restauración de bases de datos replicadas
• Registro de transacciones
• Modelos de recuperación (SQL Server)
• Conjuntos de medios, familias de medios y conjuntos de copia de seguridad (SQL Server)