Solucionar problemas de operaciones de copia de seguridad y restauración de SQL Server

Versión del producto original: SQL Server
Número de KB original: 224071

Resumen

Este artículo le ayuda a solucionar problemas comunes de las operaciones de copia de seguridad y restauración de SQL Server. Estos problemas incluyen el rendimiento lento de la copia de seguridad o restauración, los errores de compatibilidad de versiones, los trabajos de copia de seguridad del grupo de disponibilidad AlwaysOn, los errores de medios, los errores de permisos, las copias de seguridad de VDI de terceros y VSS, los errores de seguimiento de cambios y las restauraciones de base de datos cifradas. El artículo también incluye una sección de preguntas más frecuentes y vínculos a temas de referencia para SQL Server copia de seguridad y restauración.

Las operaciones de copia de seguridad y restauración tardan mucho tiempo

Las operaciones de copia de seguridad y restauración tienen un consumo intensivo de E/S. El rendimiento de copia de seguridad y restauración depende del nivel de optimización del subsistema de E/S subyacente para controlar el volumen de E/S. Si sospecha que las operaciones de copia de seguridad se detienen o tardan demasiado tiempo en finalizar, use uno o varios de los métodos siguientes para calcular el tiempo de finalización o para realizar un seguimiento del progreso de una operación de copia de seguridad o restauración:

  • El registro de errores de SQL Server contiene información sobre las operaciones anteriores de copia de seguridad y restauración. Puede usar estos detalles para calcular el tiempo necesario para realizar copias de seguridad y restaurar la base de datos en su estado actual. A continuación se muestra una salida de ejemplo del registro de errores:

    RESTORE DATABASE successfully processed 315 pages in 0.372 seconds (6.604 MB/sec)

  • En SQL Server 2016 y versiones posteriores, use el backup_restore_progress_trace XEvent para realizar un seguimiento del progreso de las operaciones de copia de seguridad y restauración.

  • Utilice la percent_complete columna de sys.dm_exec_requests para seguir el progreso de las operaciones de copia de seguridad y restauración en curso.

  • Mida el rendimiento de la copia de seguridad y la restauración mediante los contadores Device throughput Bytes/sec y Backup/Restore throughput/sec del monitor de rendimiento. Para obtener más información, vea SQL Server, Backup Device Object.

  • Use el script de estimate_backup_restore para obtener una estimación de los tiempos de copia de seguridad.

  • Consulte Cómo funciona: ¿qué hace la restauración/copia de seguridad?. En esta entrada de blog se proporciona información sobre la fase actual de las operaciones de copia de seguridad o restauración.

Investigación del rendimiento lento de la copia de seguridad o restauración

  1. Compruebe si tiene cualquiera de los problemas conocidos de la tabla siguiente y considere la posibilidad de aplicar las correcciones o procedimientos recomendados pertinentes.

    Vínculo de Knowledge Base Explicación y acciones recomendadas
    Copia de seguridad y restauración de bases de datos de SQL Server Trata los procedimientos recomendados que pueden mejorar el rendimiento de la copia de seguridad y restauración. Por ejemplo, conceda el SE_MANAGE_VOLUME_NAME privilegio a la cuenta de Windows que ejecuta SQL Server para permitir que la inicialización instantánea de archivos acelere las operaciones de archivos de datos.
    Configuración del software antivirus para trabajar con SQL Server El software antivirus puede contener bloqueos en .bak archivos, lo que puede afectar al rendimiento de las operaciones de copia de seguridad y restauración. Siga las instrucciones de este artículo para excluir los archivos de copia de seguridad de los exámenes de virus.
    Una operación de copia de seguridad o restauración en una ubicación de red es lenta Aísle el problema a la red copiando un archivo de tamaño similar en la ubicación de red desde el servidor que ejecuta SQL Server y compruebe el rendimiento.

  2. Compruebe el registro de errores de SQL Server y el registro de eventos de Windows para buscar mensajes de error que indiquen la causa del problema.

  3. Si usa planes de mantenimiento de bases de datos o software de terceros para realizar copias de seguridad simultáneas, considere la posibilidad de cambiar las programaciones para minimizar la contención en la unidad en la que se escriben las copias de seguridad.

  4. Trabaje con el administrador de Windows para comprobar si hay actualizaciones de firmware del hardware.

Errores al restaurar copias de seguridad en versiones anteriores de SQL Server

Síntomas

No se puede restaurar una copia de seguridad de SQL Server en una versión anterior de SQL Server que la versión que creó la copia de seguridad. Por ejemplo, no se puede restaurar una copia de seguridad realizada en una instancia de SQL Server 2022 en una instancia de SQL Server 2019. De lo contrario, aparece el siguiente mensaje de error:

Error 3169: se realizó una copia de seguridad de la base de datos en un servidor que ejecuta la versión %ls. Esta versión no es compatible con este servidor, que utiliza la versión %ls. Restaure la base de datos en un servidor que admita la copia de seguridad, o utilice una copia de seguridad que sea compatible con este servidor.

Resolution

Use el método siguiente para copiar una base de datos hospedada en una versión posterior de SQL Server a una versión anterior de SQL Server.

Nota:

En el procedimiento siguiente se supone que tiene dos instancias de SQL Server denominadas SQL_A (versión superior) y SQL_B (versión inferior).

  1. Descargue e instale la versión más reciente de SQL Server Management Studio (SSMS) tanto en SQL_A como en SQL_B.
  2. En SQL_A, siga estos pasos:
    1. Haga clic con el botón derecho en <YourDatabase>>Tasks>Generate Scripts (Generar scripts) y seleccione la opción para generar scripts en toda la base de datos y todos los objetos de base de datos.
    2. En la pantalla Set Scripting Options, seleccione Advanced y, a continuación, seleccione la versión de SQL_B en General>Script para la versión de SQL Server. A continuación, seleccione la opción para guardar que mejor se adapte a sus necesidades y continúe con el asistente.
    3. Use la utilidad del programa de copia masiva (bcp) para copiar datos de tablas diferentes.
  3. En SQL_B, siga estos pasos:
    1. Use los scripts generados en el servidor SQL_A para crear el esquema de la base de datos.
    2. En cada tabla, deshabilite las restricciones de clave externa y los desencadenadores. Si la tabla tiene columnas de identidad, habilite la inserción de identidad.
    3. Use bcp para importar los datos que exportó en el paso anterior en las tablas correspondientes.
    4. Una vez finalizada la importación de datos, habilite las restricciones y desencadenadores de clave externa y deshabilite la inserción de identidad para cada una de las tablas modificadas en el paso c.

Este procedimiento normalmente funciona bien para bases de datos pequeñas a medianas. En el caso de bases de datos más grandes, pueden producirse problemas fuera de memoria en SSMS y otras herramientas. Considere la posibilidad de usar SQL Server Integration Services (SSIS), la replicación u otras opciones para copiar una base de datos de una versión posterior a una versión anterior de SQL Server.

Para más información sobre cómo generar scripts para una base de datos, consulte Creación de un script de base de datos con la opción Generar script.

Problemas con trabajos de copia de seguridad en grupos de disponibilidad Always On

Síntomas

Se producen problemas que afectan a trabajos de copia de seguridad o planes de mantenimiento en entornos de grupo de disponibilidad AlwaysOn.

Resolution

  • De forma predeterminada, la preferencia de copia de seguridad automática está configurada como Preferir secundaria. Esta configuración especifica que las copias de seguridad se producen en una réplica secundaria, a menos que la réplica principal sea la única réplica en línea. No puede realizar copias de seguridad diferenciales de la base de datos con esta configuración. Para cambiar esta configuración, use SSMS en la réplica principal actual y vaya a la página Preferencias de copia de seguridad en Propiedades del grupo de disponibilidad.
  • Si usa un plan de mantenimiento o trabajos programados para generar copias de seguridad de las bases de datos, cree los trabajos para cada base de datos de disponibilidad en cada instancia de servidor que hospede una réplica de disponibilidad para el grupo de disponibilidad.

Para obtener más información sobre las copias de seguridad en un entorno AlwaysOn, consulte los siguientes artículos:

Errores multimedia al restaurar una base de datos desde una copia de seguridad

Síntomas

Los mensajes de error que indican un problema de archivo suelen apuntar a un archivo de copia de seguridad dañado. Los errores siguientes son ejemplos de problemas que podría surgir si un conjunto de copia de seguridad está dañado:

3241: La familia de medios del dispositivo '%ls' está formada incorrectamente. SQL Server no puede procesar esta familia de medios.

3242: El archivo del dispositivo '%ls' no es un conjunto de copia de seguridad válido de formato de cinta de Microsoft.

3243: La familia de medios del dispositivo "%ls" se creó con la versión %d.%d del formato de cinta de Microsoft. SQL Server admite la versión %d.%d.

Causa

Estos problemas pueden producirse debido a problemas que afectan al hardware subyacente (discos duros, almacenamiento de red, etc.) o debido a un virus o malware. Revise los registros de eventos del sistema de Windows y los registros de hardware para detectar errores notificados y tome las medidas adecuadas (por ejemplo, actualizar el firmware o corregir problemas de red).

Resolution

  • Use la instrucción RESTORE HEADERONLY para comprobar la copia de seguridad.
  • Para reducir la aparición de estos errores de restauración, habilite la opción Backup CHECKSUM cuando ejecute una copia de seguridad para evitar la copia de seguridad de una base de datos dañada. Para obtener más información, consulte Posibles errores multimedia durante la copia de seguridad y restauración (SQL Server) .
  • También puede habilitar el indicador de seguimiento 3023 para activar una suma de comprobación al realizar copias de seguridad con herramientas de copia de seguridad. Para obtener más información, consulte Configuración del servidor: valor predeterminado de la suma de comprobación de copia de seguridad.
  • Para solucionar estos problemas, busque otro archivo de copia de seguridad utilizable o cree un nuevo conjunto de copia de seguridad. Microsoft no ofrece ninguna solución que pueda ayudar a recuperar datos de un conjunto de copia de seguridad dañado.
  • Si un archivo de copia de seguridad se restaura correctamente en un servidor, pero no en otro, pruebe a copiar el archivo entre los servidores. Por ejemplo, pruebe robocopy, en lugar de una operación de copia normal. Investigue si el archivo se cambia durante la operación de copia en la red o en el dispositivo de almacenamiento de destino.

Se produce un error en las copias de seguridad debido a problemas de permisos

Síntomas

Al intentar ejecutar operaciones de copia de seguridad de base de datos, se produce uno de los siguientes errores.

  • Escenario 1: al ejecutar una copia de seguridad desde SQL Server Management Studio, se produce un error en la copia de seguridad y se devuelve el siguiente mensaje de error:

    La copia de seguridad ha fallado para el servidor <Server name>. (Microsoft.SqlServer.SmoExtended)
    System.Data.SqlClient.SqlError: no se puede abrir el dispositivo de copia de seguridad "<nombre> del dispositivo". Error del sistema operativo 5(Acceso denegado.). (Microsoft.SqlServer.Smo)

  • Escenario 2: Las copias de seguridad programadas fallan y generan un mensaje de error que se registra en el historial del trabajo que falló y que es similar al siguiente:

    Executed as user: <Owner of the job>. ....2 for 64-bit  Copyright (C) 2019 Microsoft. All rights reserved.    
Started:  5:49:14 PM  Progress: 2021-08-16 17:49:15.47    
Source: {GUID}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  
Error: 2021-08-16 17:49:15.74     
Code: 0xC002F210     
Source: Back Up Database (Full) Execute SQL Task     
Description: Executing the query "EXECUTE master.dbo.xp_create_subdir N'C:\backups\D..." failed with the following error: "xp_create_subdir() returned error 5, 'Access is denied.'". 
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Causa

Cualquier escenario puede producirse si la cuenta de servicio de SQL Server no tiene permisos de lectura y escritura en la carpeta en la que se escriben las copias de seguridad. Las instrucciones de copia de seguridad se pueden ejecutar como parte de un paso de trabajo o manualmente desde SQL Server Management Studio. En cualquier caso, se ejecutan en el contexto de la cuenta de inicio del servicio SQL Server. Por lo tanto, si la cuenta de servicio no tiene los privilegios necesarios, obtendrá los mensajes de error indicados anteriormente.

Resolution

Para comprobar los permisos actuales de la cuenta de servicio de SQL Server en una carpeta, vaya a la pestaña Seguridad de las propiedades de la carpeta, seleccione Avanzadas y, a continuación, use la pestaña Acceso efectivo. Para obtener más información, consulte Dispositivos de copia de seguridad.

Se produce un error en las operaciones de copia de seguridad o restauración de terceros

SQL Server proporciona una interfaz de dispositivo de copia de seguridad virtual (VDI). Esta API permite a los proveedores de software independientes integrar SQL Server en sus productos para admitir operaciones de copia de seguridad y restauración. Estas API están diseñadas para proporcionar fiabilidad y rendimiento, y para admitir toda la gama de funciones de copia de seguridad y restauración de SQL Server, incluidas las capacidades de instantáneas y copia de seguridad en caliente.

Pasos comunes de solución de problemas

  • En todas las versiones admitidas de SQL Server, se crea y aprovisiona un inicio de sesión denominado NT SERVICE\SQLWriter durante la instalación. Compruebe que este inicio de sesión existe en SQL Server y forma parte del rol de servidor sysadmin en la instancia de la que se realiza una copia de seguridad. Además, compruebe que el servicio SQL Server VSS Writer se ha iniciado y que su cuenta de inicio de sesión está establecida en Sistema local.

  • Compruebe que SqlServerWriter figure en la lista al ejecutar el comando VSSADMIN LIST WRITERS en un símbolo del sistema con privilegios elevados en el servidor que ejecuta SQL Server. El escritor debe estar presente y en el estado Stable para que las copias de seguridad de VSS finalicen correctamente.

  • Para obtener más información, compruebe los registros del software de copia de seguridad y del sitio de soporte técnico del proveedor.

    Síntomas o escenario Referencia
    Descripción del funcionamiento de la copia de seguridad de VDI Cómo funciona: SQL Server: recursos de copia de seguridad de VDI (VSS)
    Número de bases de datos de las que se puede realizar una copia de seguridad simultáneamente Cómo funciona: ¿Cuántas bases de datos se pueden realizar copias de seguridad simultáneamente?

Se produce un error en las copias de seguridad cuando el seguimiento de cambios está habilitado

Síntomas

Es posible que se produzca un error en las copias de seguridad al habilitar el seguimiento de cambios en la base de datos. Es posible que vea un error similar al siguiente:

Error: 3999, Gravedad: 17, Estado: 1.
<Marca de tiempo> spid <spid> No se pudo vaciar la tabla de confirmaciones en el disco en dbid 8 debido al error 2601. Vea el registro de errores para obtener más información.

Resolution

Si encuentra este problema en una versión compatible de SQL Server, instale la actualización acumulativa más reciente para la versión. Para conocer las correcciones históricas y de fondo, consulte los siguientes artículos:

Errores al restaurar copias de seguridad de bases de datos cifradas

Síntomas

Se producen problemas al restaurar copias de seguridad de bases de datos protegidas por cifrado de datos transparente (TDE).

Resolution

Para solucionar el problema, consulte Traslado de una base de datos protegida por TDE a otra SQL Server.

Preguntas más frecuentes sobre SQL Server copia de seguridad y restauración

¿Cómo puedo comprobar el estado de una operación de copia de seguridad?

Use el script estimate_backup_restore para calcular los tiempos de copia de seguridad.

¿Qué debo hacer si SQL Server conmuta por error en medio de una copia de seguridad?

Reinicie la operación de restauración o copia de seguridad por reinicio de una operación de restauración interrumpida (Transact-SQL).

¿Puedo restaurar copias de seguridad de bases de datos de versiones anteriores en versiones más recientes y viceversa?

No se puede restaurar una copia de seguridad de SQL Server mediante una versión de SQL Server anterior a la versión que creó la copia de seguridad. Para obtener más información, consulte soporte de compatibilidad con RESTORE.

¿Cómo puedo comprobar las copias de seguridad de la base de datos de SQL Server?

Consulte los procedimientos documentados en sentencias RESTORE - VERIFYONLY (Transact-SQL).

¿Cómo puedo obtener el historial de copia de seguridad de las bases de datos en SQL Server?

Consulte Cómo obtener el historial de copia de seguridad de las bases de datos en SQL Server.

¿Puedo restaurar copias de seguridad de 32 bits en servidores de 64 bits y viceversa?

Sí. El formato de almacenamiento en disco de SQL Server es el mismo en entornos de 64 bits y de 32 bits. Por lo tanto, las operaciones de copia de seguridad y restauración funcionan en entornos de 64 y 32 bits.

¿Cómo se realiza una copia de seguridad y restauración de una base de datos protegida mediante cifrado de datos transparente (TDE)?

Realice una copia de seguridad de la base de datos, el certificado de servidor de la clave de cifrado de la base de datos y la clave privada del certificado. Para restaurar la copia de seguridad en otra instancia, restaure primero el certificado de servidor (con su clave privada) en la master base de datos de la instancia de destino y, a continuación, restaure la copia de seguridad de la base de datos de usuario. Para obtener instrucciones paso a paso, consulte Traslado de una base de datos protegida por TDE a otra SQL Server.

¿Funciona la compresión de copia de seguridad en bases de datos habilitadas para TDE?

Sí. A partir de SQL Server 2016, la compresión de copias de seguridad funciona en bases de datos con TDE habilitado al especificar un valor de MAXTRANSFERSIZE superior a 65536 (64 KB) en la instrucción BACKUP. Sin esa configuración, la copia de seguridad se ejecuta sin comprimir incluso cuando se solicita compresión. Para obtener más información, consulte Compresión de copia de seguridad.

¿Cómo interactúan las copias de seguridad de VDI y VSS con las réplicas secundarias del grupo de disponibilidad AlwaysOn?

Las copias de seguridad basadas en VSS (instantáneas) realizadas mediante el servicio SQL Writer solo son compatibles con la réplica principal. En las réplicas secundarias, solicite una copia de seguridad completa de tipo copy-only mediante el cliente VDI, ya que no se admiten copias de seguridad completas de VSS en una réplica secundaria. Para obtener más información, consulte Secundarias activas: Copia de seguridad en réplicas secundarias (grupos de disponibilidad AlwaysOn).

Sugerencias para solución de problemas generales

  • Conceda permisos de lectura y escritura a la cuenta de servicio de SQL Server en la carpeta donde se escriben copias de seguridad. Para más información, consulte el artículo sobre los permisos para la copia de seguridad.
  • Compruebe que la carpeta donde escribe copias de seguridad tiene suficiente espacio para las copias de seguridad de la base de datos. Use el sp_spaceused procedimiento almacenado para obtener una estimación aproximada del tamaño de copia de seguridad de una base de datos.
  • Use la versión más reciente de SSMS para evitar problemas conocidos relacionados con la configuración del plan de mantenimiento y del trabajo.
  • Realice una ejecución de prueba de los trabajos para comprobar que las copias de seguridad se crean correctamente. Agregue lógica para comprobar las copias de seguridad.
  • Si planea mover bases de datos del sistema de un servidor a otro, consulte Mover bases de datos del sistema.
  • Si ve errores intermitentes de copia de seguridad, compruebe si la actualización más reciente de la versión de SQL Server corrige el problema. Para obtener más información, consulte SQL Server versiones y actualizaciones.
  • Para programar y automatizar copias de seguridad para las ediciones SQL Server Express, consulte Programación y automatización de copias de seguridad de bases de datos de SQL Server en SQL Server Express.

Temas de referencia para SQL Server copia de seguridad y restauración

En la tabla siguiente se enumeran los temas que se van a revisar para las tareas específicas de copia de seguridad y restauración.

Artículo Descripción
BACKUP (Transact-SQL) Responde a preguntas básicas sobre las copias de seguridad y proporciona ejemplos de diferentes tipos de operaciones de copia de seguridad y restauración.
Dispositivos de copia de seguridad (SQL Server) Referencia para comprender los dispositivos de copia de seguridad, realizar copias de seguridad en un recurso compartido de red, Azure Blob Storage y tareas relacionadas.
Modelos de recuperación (SQL Server) Aborda en detalle los modelos de recuperación simple, completo y de registro masivo, y explica cómo el modelo de recuperación afecta a las copias de seguridad.
Copia de seguridad y restauración de bases de datos del sistema (SQL Server) Trata las estrategias y consideraciones al trabajar en operaciones de copia de seguridad y restauración para bases de datos del sistema.
Introducción a la restauración y recuperación (SQL Server) Trata cómo afectan los modelos de recuperación a las operaciones de restauración. Revise este artículo si tiene preguntas sobre cómo afecta el modelo de recuperación de una base de datos al proceso de restauración.
Administración de los metadatos cuando una base de datos pasa a estar disponible en otro servidor Consideraciones que se deben tener en cuenta al mover una base de datos o tener problemas que afectan a los inicios de sesión, el cifrado, la replicación, los permisos, etc.
Copias de seguridad del registro de transacciones (SQL Server) Presenta conceptos sobre cómo realizar copias de seguridad y restaurar (aplicar) los registros de transacciones en los modelos de recuperación completos y registrados de forma masiva. Explica cómo realizar copias de seguridad rutinarias del registro de transacciones para recuperar datos.
Copia de seguridad administrada de SQL Server en Microsoft Azure Presenta la copia de seguridad administrada y los procedimientos relacionados.

Contenido relacionado

  • Last updated on