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

  • Artículo

Se aplica a:SQL Server

En este artículo se describen las ventajas de realizar copias de seguridad de las bases de datos de SQL Server, los términos de copias de seguridad y restauración básicos, y se presentan las estrategias de copias de seguridad y restauración para SQL Server, así como consideraciones de seguridad para las copias 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, vea 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 obtener más información, vea Copias de seguridad de instantánea de archivos para archivos de base de datos de 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 obtener más información, vea Azure Backup para SQL Server en máquina virtual 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]
El proceso de creación de una copia de seguridad [forma nominal] mediante la copia de los registros de datos de una base de datos de SQL Server o los registros de su registro de transacciones.

copia de seguridad [nombre]
Copia de los datos de que se 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, vea 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 los registros de transacciones que incluye todos los registros no guardados 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. Existen tres modelos de recuperación: simple, completa y por medio de registros de operaciones masivas. El modelo de recuperación de la base de datos determina sus requisitos de copias de seguridad y restauración.

restore
Proceso de varias fases que copia todos los datos y páginas del registro desde una copia de seguridad de SQL Server especificada a una base de datos especificada y, a continuación, pone al día todas las transacciones registradas en la copia de seguridad mediante la aplicación de los cambios registrados para poner los datos al día.

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 las copias de seguridad y la 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 de disponibilidad máxima de los datos y la pérdida mínima de datos, al tiempo que se tiene en cuenta el costo de mantenimiento y almacenamiento de las 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 necesaria, cómo se prueban las copias de seguridad, y dónde y cómo se almacenan 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 llevar a cabo las operaciones de restauración, cómo se deben realizar para satisfacer sus 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. Es necesario realizar pruebas: no tendrá una estrategia de copia de seguridad hasta que haya restaurado correctamente las copias de seguridad en todas las combinaciones incluidas en su estrategia de restauración y haya probado la base de datos restaurada en busca de 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 datos frente a pérdidas o 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 de los recursos, como hardware, personal, espacio para almacenar los medios de copia de seguridad, seguridad física de los medios almacenados, etc.

Mejores prácticas recomendadas

No se debe conceder más privilegios de los necesarios a las cuentas que realizan operaciones de copia de seguridad o restauración. 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 tanto, el modelo de recuperación de una base de datos determina qué tipos de copias de seguridad y qué escenarios de restauración se admiten para la base de datos, así como el tamaño de 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. El modelo de recuperación completa puede aumentarse cambiando al modelo de recuperación optimizado para cargas masivas de registros antes de las operaciones masivas. Para ver una introducción sobre estos modelos de recuperación y cómo afectan a la administración del registro de transacciones, vea El registro de transacciones (SQL Server).

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 el riesgo de pérdida de trabajo, a costa de una sobrecarga de trabajo administrativo, utilice 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 las copias de seguridad y restauración, vea Información general de copia de seguridad (SQL Server).

Diseñar la estrategia de copia de seguridad

Una vez seleccionado un modelo de recuperación que cumpla los requisitos de su empresa para una base de datos específica, debe planear e implementar una estrategia de copias de seguridad. 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 prevé un período de poca actividad, se recomienda programar las copias de seguridad de bases de datos completas en dicho 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 optar por desechar las copias de seguridad antiguas debido a limitaciones de recursos de almacenamiento, valore si será necesaria la capacidad de recuperación hasta ese punto en el pasado.

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. Para calcular el tamaño de la copia de seguridad completa de la base de datos, use el procedimiento almacenado del sistema sp_spaceused . 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 están ejecutando, por lo que se puede efectuar al mismo tiempo que otras operaciones periódicas. 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 de 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.

Probar las copias de seguridad

No tendrá una estrategia de restauración hasta que compruebe las copias de seguridad. Es muy importante comprobar cuidadosamente la estrategia de copia de seguridad de cada una de las bases 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. También se recomienda que, una vez restaurada 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 el medio de copia de seguridad no se ha dañado.

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

Usa las opciones de comprobación proporcionadas por las utilidades de copia de seguridad (comando BACKUP de T-SQL, planes de mantenimiento de SQL Server, su solución o software de copia de seguridad, etc.). Para obtener un ejemplo, vea [RESTORE VERIFYONLY] (../t-sql/statements/restore-statements-verifyonly-transact-sql.md) Uso de características avanzadas como BACKUP CHECKSUM para detectar problemas con el medio de copia de seguridad en sí. Para más información, vea Errores posibles de medios durante 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.

Supervisar el 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. Si surgen problemas con cualquiera de las operaciones, puede usar el evento extendido backup_restore_progress_trace para supervisar el progreso en directo. Para más información sobre los eventos extendidos, vea Eventos extendidos.

Advertencia

El uso del evento extendido backup_restore_progress_trace puede provocar un problema de rendimiento y consumir una cantidad importante 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 esssion
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

Example of back up xevent outputExample of restore xevent output

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

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

Crear copias de seguridad

Nota:

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

Usar SSMS

Usar T-SQL

Restaurar copias de seguridad de datos

Usar SSMS

Usar T-SQL

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

Usar SSMS

Usar T-SQL

Más información y recursos

Backup Overview (SQL Server)
Información general sobre restauración y recuperación (SQL Server)
BACKUP (Transact-SQL)
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
El registro de transacciones (SQL Server)
Modelos de recuperación (SQL Server)
Conjuntos de medios, familias de medios y conjuntos de copias de seguridad (SQL Server)