Copia de seguridad y restauración de bases de datos de SQL Server

Se aplica a:SQL Server

En este artículo se describen las ventajas de realizar copias de seguridad de bases de datos de SQL Server, se describen los términos básicos de copia de seguridad y restauración y se presentan las estrategias de copia de seguridad y restauración de SQL Server y las consideraciones de seguridad para la copia de seguridad y restauración de SQL Server.

En este artículo se presentan las copias de seguridad de SQL Server. Para conocer los pasos específicos para realizar copias de seguridad de bases de datos de SQL Server, consulte Crear copias de seguridad.

El componente Copia de seguridad y restauración de SQL Server proporciona una protección fundamental para proteger los datos críticos almacenados en las bases de datos de SQL Server. Para minimizar el riesgo de una pérdida de datos catastrófica, debe realizar de forma periódica copias de seguridad de las bases de datos para conservar las modificaciones realizadas en los datos. Una estrategia bien diseñada de copia de seguridad y restauración le ayuda a proteger las bases de datos frente a la pérdida de datos provocada por diversos errores. Pruebe su estrategia con la restauración de un conjunto de copias de seguridad y, después, recupere la base de datos para prepararse para dar una respuesta eficaz en caso de desastre.

Además del almacenamiento local para almacenar las copias de seguridad, SQL Server también admite la copia de seguridad y la restauración en Azure Blob Storage. Para más información, consulte Copia de seguridad y restauración de SQL Server con Microsoft 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 más información, consulte Copias de seguridad de instantáneas de archivos para archivos de base 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

• La copia de seguridad de las bases de datos de SQL Server, la ejecución de procedimientos de restauración de prueba de las copias de seguridad y el almacenamiento de las copias en una ubicación segura y fuera del sitio contribuyen a protegerse ante una pérdida de datos catastrófica. 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, quitar 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. El uso de la copia de seguridad de SQL Server en Azure Blob Storage le permite crear una copia de seguridad externa en una región diferente de la ubicación local, con lo que podrá utilizarla en caso de que un desastre natural afecte a su ubicación.

• Además, las copias de seguridad de una base de datos son útiles para fines administrativos habituales, como copiar una base de datos de un servidor a otro, configurar Grupos de disponibilidad Always On o la creación de reflejo de la base de datos y el archivo.

Glosario de términos de copia de seguridad

realizar copia de seguridad [verbo]
Proceso de creación de una copia de seguridad [sustantivo] copiando registros de datos desde una base de datos de SQL Server o registros de registro desde su registro de transacciones.

copia de seguridad [nombre]
Copia de datos que puede usar para restaurar y recuperar los datos después de un error. Las copias de seguridad de una base de datos también se pueden usar para restaurar una copia de la base de datos en una nueva ubicación.

dispositivo de copia de seguridad
Disco o dispositivo de cinta en el que se escriben las copias de seguridad de SQL Server del que se pueden restaurar. Las copias de seguridad de SQL Server también se pueden escribir en Azure Blob Storage, y el formato de URL se usa para especificar el destino y el nombre del archivo de copia de seguridad. Para más información, consulte Copia de seguridad y restauración de SQL Server con Microsoft Azure Blob Storage.

medio de copia de seguridad
Una o varias cintas o archivos de disco en los que se han escrito una o varias copias de seguridad.

copia de seguridad de datos
Copia de seguridad de datos de una base de datos completa (copia de seguridad de base de datos), una base de datos parcial (copia de seguridad parcial) o un conjunto de archivos de datos o grupos de archivos (copia de seguridad de archivos).

copia de seguridad de base de datos
Copia de seguridad de una base de datos. Las copias de seguridad completas representan la base de datos completa en el momento en que finalizó la copia de seguridad. Las copias de seguridad diferenciales solo contienen los cambios realizados en la base de datos desde la copia de seguridad completa más reciente.

copia de seguridad diferencial
Copia de seguridad de datos basada en la última copia de seguridad completa de una base de datos completa o parcial o de un conjunto de archivos de datos o grupos de archivos (base diferencial) y que solo incluye los datos que han cambiado desde dicha base.

copia de seguridad completa
Copia de seguridad completa que incluye todos los datos de una base de datos determinada o un conjunto de grupos de archivos o archivos, así como una cantidad suficiente del registro como para permitir la recuperación de datos.

copia de seguridad de registros
Copia de seguridad de registros de transacciones que incluye todos los registros de registros de los que no se ha realizado una copia de seguridad en una copia de seguridad de registros anterior (modelo de recuperación completa).

recover
Devolver una base de datos a un estado estable y coherente.

recovery
Fase de inicio de una base de datos o de una restauración con recuperación que pone la base de datos en un estado de transacción coherente.

modelo de recuperación
Propiedad de la base de datos que controla el mantenimiento del registro de transacciones de una base de datos. Hay tres modelos de recuperación: simple, completo y optimizado para cargas masivas. El modelo de recuperación de una base de datos determina sus requisitos de copia de seguridad y restauración.

restore
Proceso de varias fases que copia todas las páginas de datos y registros de una copia de seguridad de SQL Server especificada en una base de datos especificada y, a continuación, reenvía todas las transacciones que se registran en la copia de seguridad aplicando cambios registrados para que los datos se reenvíen a tiempo.

Estrategias de copias de seguridad y restauración

Las operaciones de copia de seguridad y restauración deben personalizarse para un entorno concreto y funcionar con los recursos disponibles. Por lo tanto, un uso confiable de la copia de seguridad y restauración para la recuperación requiere una estrategia de copia de seguridad y restauración. Una estrategia de copia de seguridad y restauración bien diseñada equilibra los requisitos empresariales para la máxima disponibilidad de datos y la pérdida mínima de datos, al tiempo que tiene en cuenta el costo 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 de la estrategia define el tipo y la frecuencia de las copias de seguridad, la naturaleza y la velocidad del hardware que requieren, cómo se van a probar las copias de seguridad y dónde y cómo se almacenarán los medios de copia de seguridad (incluidas las consideraciones de seguridad). La parte de restauración de la estrategia define quién es responsable de realizar restauraciones, cómo se deben realizar las restauraciones para cumplir los objetivos de disponibilidad de la base de datos y minimizar la pérdida de datos y cómo se prueban las restauraciones.

Diseñar una estrategia de copia de seguridad y restauración eficaz requiere mucho cuidado en el planeamiento, la implementación y las pruebas. Las pruebas son necesarias: no tiene una estrategia de copia de seguridad hasta que haya restaurado correctamente las copias de seguridad en todas las combinaciones incluidas en la estrategia de restauración y haya probado la base de datos restaurada para la coherencia física. Debe tener en cuenta varios factores. Entre ellas se incluyen las siguientes:

• 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

Las cuentas que realizan operaciones de copia de seguridad o restauración no deben tener más privilegios de los necesarios. Revisa la copia de seguridad y la restauración para obtener detalles de permisos específicos. Se recomienda que las copias de seguridad se cifren y, si es posible, se compriman.

Para garantizar la seguridad, los archivos de copia de seguridad deben tener extensiones que sigan las convenciones adecuadas:

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

Uso de almacenamiento independiente

Importante

Asegúrese de colocar las copias de seguridad de las bases de datos en una ubicación física o un dispositivo independiente de los archivos de la base de datos. Si la unidad física que almacena las bases de datos no funciona correctamente o se bloquea, la capacidad de recuperación depende de la capacidad de acceder a la unidad o el dispositivo remoto independiente que almacenó las copias de seguridad para realizar una restauración. Tenga en cuenta que puede crear varios volúmenes lógicos o particiones a partir de una misma unidad de disco físico. Estudie detenidamente los diseños de partición de disco y de volumen lógico 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 se admiten para la base de datos y qué tamaño serían las copias de seguridad del registro de transacciones. Normalmente, en las bases de datos se usa el modelo de recuperación simple o el modelo de recuperación completa. Puede aumentar el modelo de recuperación completa cambiando al modelo de recuperación optimizado para cargas masivas de registros antes de las operaciones masivas. 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 para la base de datos depende de sus requisitos empresariales. 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 impacto en el tamaño del registro durante las operaciones de registro masivo, a la vez que permite la recuperación de dichas operaciones, use el modelo de recuperación optimizado para cargas masivas de registros. Para obtener información sobre el efecto de los modelos de recuperación en la copia de seguridad y restauración, consulte Introducción a la copia de seguridad.

Diseñar la estrategia de copia de seguridad

Después de seleccionar un modelo de recuperación que cumpla los requisitos empresariales de una base de datos específica, debe planear e implementar una estrategia de copia de seguridad correspondiente. La estrategia de copias de seguridad óptima depende de distintos factores, de entre los cuales destacan los siguientes:

• ¿Cuántas horas al día requieren las aplicaciones acceso a la base de datos?

  Si hay un período predecible fuera del pico, se recomienda programar copias de seguridad completas de la base de datos durante ese período.

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

  Si se realizan cambios frecuentes, tenga en cuenta los siguientes aspectos:

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

  • Con el modelo de recuperación completa, debe programar copias de seguridad de registros frecuentes. 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 los cambios tengan lugar solo en una pequeña parte de la base de datos o en una grande?

  Para una base de datos grande en la que los cambios se concentran en una parte de los archivos o grupos de archivos, las copias de seguridad parciales o de archivos 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 necesitará una copia de seguridad completa de la base de datos?

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

  Asegúrese de que tiene una programación de copia de seguridad adecuada establecida en función de las necesidades de la aplicación y los requisitos empresariales. El riesgo de pérdida de datos en las copias de seguridad antiguas aumenta a medida que pasa el tiempo, a menos que tenga una manera de regenerar todos los datos hasta el momento del error. Antes de elegir eliminar las copias de seguridad antiguas debido a las limitaciones de los recursos de almacenamiento, considere si la capacidad de recuperación es necesaria hasta ahora.

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

Antes de implementar una estrategia de copias de seguridad y restauración, es necesario calcular cuánto espacio en disco usará la copia de seguridad completa. 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 y no el espacio no utilizado. Por tanto, la copia de seguridad es normalmente más pequeña que la propia base de datos. Puede calcular el tamaño de una copia de seguridad completa de la base de datos mediante el procedimiento almacenado del sp_spaceused sistema. Para obtener más información, vea sp_spaceused (Transact-SQL).

Programar copias de seguridad

La realización de una operación de copia de seguridad tiene un efecto mínimo en las transacciones que se ejecutan; por lo tanto, puede ejecutar operaciones de copia 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.

Para obtener información sobre las restricciones de simultaneidad durante la copia de seguridad, vea Información general sobre la copia de seguridad (SQL Server).

Una vez decididos los tipos de copias de seguridad necesarios y la frecuencia de realización de cada tipo de copia, se recomienda programar copias de seguridad regulares como parte de un plan de mantenimiento de la base 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. Es muy importante probar exhaustivamente la estrategia de copia de seguridad para cada una de las bases de datos mediante la restauración de 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. También se recomienda que, una vez que restaure la copia de seguridad, realice comprobaciones de coherencia de la base de datos a través de DBCC CHECKDB de la base de datos para validar que los medios de copia de seguridad no estaban dañados.

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

Use las opciones de comprobación proporcionadas por las utilidades de copia de seguridad (BACKUP comando T-SQL, Planes de mantenimiento de SQL Server, el software de copia de seguridad o la solución, etc.). Para obtener un ejemplo, vea Instrucciones RESTORE - VERIFYONLY.

Use características avanzadas como BACKUP CHECKSUM para detectar problemas con el propio medio de copia de seguridad. Para obtener más información, vea Posibles errores multimedia durante la copia de seguridad y restauración (SQL Server)

Estrategia de copia de seguridad y restauración de documentos

Se recomienda documentar los procedimientos de copia de seguridad y restauración, y mantener una copia de la documentación en su libro de documentación de procesos. También se recomienda 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 dispositivos de copia de seguridad (si los hay) y el tiempo necesario para restaurar las copias de seguridad de prueba.

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 durar bastante tiempo debido al tamaño de una base de datos y a la complejidad de las operaciones implicadas. Cuando surjan problemas con cualquiera de las operaciones, puede usar el evento extendido para supervisar el backup_restore_progress_trace progreso en directo. Para obtener más información sobre los eventos extendidos, vea Información general sobre eventos extendidos.

Advertencia

El uso del backup_restore_progress_trace evento extendido puede provocar un problema de rendimiento y consumir una cantidad significativa de espacio en disco. Úselo durante períodos de tiempo breves, tenga cuidado y pruébelo minuciosamente antes de implementarlo en producción.

-- 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

Nota:

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

Uso de SSMS

• Crear una copia de seguridad completa de base de datos (SQL Server)

• Copia de seguridad de un registro de transacciones

• Copia de seguridad de archivos y grupos de archivos

• Crear una copia de seguridad diferencial de una base de datos (SQL Server)

Uso de T-SQL

• Usar el regulador de recursos para limitar el uso de CPU mediante compresión de copia de seguridad (Transact-SQL)

• Realizar una 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 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 base de datos en el modelo de recuperación simple (Transact-SQL)

• Restauración de la base de datos al punto de error: recuperación completa

• Restaurar archivos y grupos de archivos en archivos existentes (SQL Server)

• Restaurar archivos en una nueva ubicación (SQL Server)

• Restaurar la base de datos maestra (Transact-SQL)

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)

• Restaurar una copia de seguridad de registros de transacciones (SQL Server)

• Restaurar una base de datos de SQL Server a un momento dado (modelo de recuperación completa)

Usar T-SQL

• Restaurar una base de datos de SQL Server a un momento dado (modelo de recuperación completa)

• Reinicie una operación de restauración interrumpida (Transact-SQL)

• Recuperación de una base de datos sin restaurar datos (Transact-SQL)

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)
• Instrucciones RESTORE (Transact-SQL)
• Realizar una copia de seguridad y restaurar las bases de datos de Analysis Services
• Realizar copias de seguridad de los catálogos e índices de texto completo y restaurarlos
• Hacer copias de seguridad y restaurar 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)