Устранение SQL Server резервного копирования и восстановления

В этой статье содержатся решения распространенных проблем, которые могут возникнуть во время Microsoft SQL Server операций резервного копирования и восстановления, а также ссылки на дополнительные сведения об этих операциях.

Оригинальная версия продукта:   SQL Server
Исходный номер КБ:   224071

Операции резервного копирования и восстановления принимают длительное время

Операции резервного копирования и восстановления являются интенсивными. Пропускная способность резервного копирования и восстановления зависит от того, насколько хорошо оптимизирована подсистема I/O для обработки объема I/O. Если вы подозреваете, что операции резервного копирования остановлены или слишком долго завершались, можно использовать один или несколько из следующих методов для оценки времени завершения или отслеживания хода операции резервного копирования или восстановления:

  • Журнал SQL Server содержит сведения о предыдущих операциях резервного копирования и восстановления. Эти сведения можно использовать для оценки времени, необходимого для восстановления базы данных в текущем состоянии. Ниже приводится пример вывода из журнала ошибок:

    RESTORE DATABASE successfully processed 315 pages in 0.372 seconds (6.604 MB/sec)
    
  • В SQL Server 2016 и более поздних версиях вы можете использовать XEvent backup_restore_progress_trace для отслеживания хода операций резервного копирования и восстановления.

  • Вы можете использовать столбец percent_complete sys.dm_exec_requests для отслеживания хода операций резервного копирования и восстановления во время полета.

  • Данные о пропускной способности резервного копирования и восстановления можно измерить с помощью счетчиков мониторинга пропускной способности устройства "Bytes/sec" и "Backup/Restore throughput/sec". Дополнительные сведения см. в SQL Server, Объект резервного устройства.

  • Используйте сценарий estimate_backup_restore, чтобы получить оценку времени резервного копирования.

  • Обратитесь к how it works: What is Restore/Backup Doing?   В этом блоге содержится представление о текущем этапе операций резервного копирования или восстановления.

Что нужно проверить

  1. Проверьте, возникли ли какие-либо из известных проблем, перечисленных в следующей таблице. Рассмотрите возможность реализации изменений или применения исправлений и методов, которые обсуждаются в соответствующих статьях.

    База знаний или ссылки Книги Online Разъяснение и рекомендуемые действия
    Оптимизация резервного копирования и восстановления производительности в SQL Server В разделе Книги Online используются различные методы, которые можно использовать для повышения производительности операций резервного копирования и восстановления. Например, вы можете назначить SE_MANAGE_VOLUME_NAME специальную привилегию Windows учетной записи, которая работает SQL Server, чтобы обеспечить мгновенную инициализацию файлов данных. Это может привести к значительному приросту производительности.
    2920151 рекомендуемые hotfixes и обновления для Windows Server 2012 на основе R2 неудачных кластеров

    2822241 Windows 8 и Windows Server 2012 обновления: апрель 2013 г.
    Текущие системные откаты могут включать исправления известных проблем на уровне системы, которые могут привести к ухудшению производительности таких программ, как SQL Server. Установка этих обновлений поможет предотвратить такие проблемы.
    2878182 FIX: процессы режима пользователя в приложении не работают на серверах, работающих Windows Server 2012

    Операции резервного копирования являются интенсивными и могут быть затронуты этой ошибкой. Примени это исправление, чтобы предотвратить эти проблемы.
    309422 Как выбрать антивирусное программное обеспечение для работы на компьютерах с SQL Server Антивирусное программное обеспечение может удерживать блокировки файлов .bak. Это может повлиять на производительность операций резервного копирования и восстановления. Следуйте указаниям в этой статье, чтобы исключить файлы резервного копирования из проверки вирусов.
    2820470 сообщение об ошибке при попытке получить доступ к общей папке, которая больше не существует в Windows Обсуждается проблема, которая возникает при попытке получить доступ к общей папке, которая больше не существует в Windows 2012 и более поздних версиях.
    967351 сильно фрагментарный файл в томе NTFS может не вырастать за пределы определенного размера Обсуждается проблема, возникают при сильной фрагментации файловой системы NTFS.
    304101 программа резервного копирования неуспешна при резервном копировании большого объема системы
    2455009 FIX: медленная производительность при восстановлении базы данных, если в журнале транзакций SQL Server 2005 г., в SQL Server 2008 г. или в SQL Server 2008 г. R2 Наличие множества виртуальных файлов журналов может повлиять на необходимое время для восстановления базы данных. Это особенно актуально на этапе восстановления операции восстановления. Сведения о других возможных проблемах, которые могут быть вызваны присутствием многих VLF, см. в публикации Database operations take a long time to complete, or they trigger errors when the transaction log has numerous virtual log files.
    Операция резервного копирования или восстановления в сетевом расположении медленна Изолируйте проблему в сети, пытаясь скопировать файл аналогичного размера в расположение сети с сервера, который работает SQL Server. Проверка производительности.
  2. Дополнительные указатели о причинах проблемы SQL Server в журнале Windows и журнале событий.

  3. Если вы используете сторонние программы или планы обслуживания баз данных для одновременных резервных копий, подумайте, следует ли менять расписания, чтобы свести к минимуму раздор на диске, на который пишутся резервные копии.

  4. Работайте с администратором Windows, чтобы проверить обновления прошивки для вашего оборудования.

Проблемы, влияющие на восстановление базы данных между различными SQL Server версиями

Резервное SQL Server не может быть восстановлено в более ранней версии SQL Server, чем версия, в которой была создана резервная копия. Например, нельзя восстановить резервное копирование, которое взято на экземпляре 2019 SQL Server 2019 г. в экземпляр 2017 SQL Server 2017 г. Это приведет к следующему сообщению об ошибке:

Ошибка 3169. База данных была создана на сервере с запущенной версией %ls. Эта версия несовместима с этим сервером, на котором запущена версия %ls. Либо восстановим базу данных на сервере, который поддерживает резервное копирование, либо используйте резервное копирование, совместимое с этим сервером.

Используйте следующий метод для копирования базы данных, которая содержится в более поздней версии SQL Server более ранней версии SQL Server.

Примечание

Следующая процедура предполагает, что у вас есть SQL Server экземпляры с именем SQL_A (более высокая версия) и SQL_B (более низкая версия).

  1. Скачайте и установите последнюю версию SQL Server Management Studio (SSMS) как SQL_A, так и SQL_B.
  2. В SQL_A выполните следующие действия:
    1. Щелкните правой кнопкой мыши <yourDatabase > Tasks > Generate Scripts и выберите вариант сценария всей базы данных и всех объектов базы данных.
    2. На экране Set Scripting Options выберите Расширенный, а затем выберите версию SQL_B в соответствии с общим > скриптом для SQL Server Версии. Кроме того, выберите вариант, который лучше всего работает для вас, чтобы сохранить созданные сценарии. Затем продолжайте мастер.
    3. Используйте утилиту массовой программы копирования (BCP) для копирования данных из разных таблиц.
  3. В SQL_B выполните следующие действия:
    1. Используйте скрипты, созданные на сервере SQL_A, чтобы создать схему базы данных.
    2. На каждой из таблиц отключай все внешние ограничения и триггеры. Если в таблице есть столбцы удостоверений, введите вставку удостоверения.
    3. Используйте bcp для импорта данных, экспортируемых на предыдущем этапе, в соответствующие таблицы.
    4. После завершения импорта данных включаем внешние ограничения и триггеры и отключаем вставку удостоверений для каждой из таблиц, затронутых в шаге c.

Эта процедура обычно хорошо работает для баз данных малых и средних размеров. Для больших баз данных проблемы с памятью могут возникать в SSMS и других средствах. Чтобы создать копию базы данных из более поздней версии в более рановую версию SQL Server, следует использовать службы интеграции SQL Server (SSIS),репликацию или другие параметры.

Дополнительные сведения о том, как создавать скрипты для базы данных, см. в тексте Script a database by using the Generate Scripts option.

Резервное копирование проблем с заданиями в средах Always On

Если возникают проблемы, влияющие на работу резервного копирования или планы обслуживания в средах Always On, обратите внимание на следующее:

  • По умолчанию для автоматического резервного копирования задается значение Prefer Secondary. Это указывает, что резервное копирование должно происходить на вторичной реплике , за исключением случаев, когда основная реплика является единственной репликой в Интернете. С помощью этого параметра нельзя принимать дифференциальные резервные копии базы данных. Чтобы изменить этот параметр, используйте SSMS текущую первичную реплику и перейдите на страницу Параметры резервного копирования в статье Свойства группы доступности.
  • Если для создания резервных копий баз данных используется план обслуживания или запланированные задания, обязательно создайте рабочие места для каждой базы данных доступности на каждом экземпляре сервера, на котором размещена реплика доступности для группы доступности.

Дополнительные сведения о резервном копировании в среде Always On см. в следующих темах:

Если вы получаете сообщения об ошибках, которые указывают на проблему с файлом, это является симптомом поврежденного файла резервного копирования. Примеры ошибок, которые могут возникнуть при поврежденном наборе резервного копирования, включают, но не ограничиваются следующими:

    1. Семейство мультимедиа на устройстве "%ls" неправильно сформировано. SQL Server не удается обработать эту семейство мультимедиа.
    1. Файл на устройстве "%ls" не является допустимым набором резервного копирования microsoft Tape Format.
    1. Семейство мультимедиа на устройстве "%ls" было создано с помощью версии Microsoft Tape Format %d.%d. SQL Server поддерживает версию %d.%d.

Примечание

Для проверки резервных копий можно использовать заявление Restore Header.

Эти проблемы могут возникать из-за проблем, влияющих на оборудование (жесткие диски, сетевое хранилище и т. д.) или связанные с вирусом или вредоносными программами. Просмотрите Windows системных журналов событий и журналы оборудования для сообщений об ошибках и примите соответствующие меры (например: обновление прошивки или устранение проблем с сетью).

Чтобы предотвратить эти ошибки, варим параметр Backup CHECKSUM при запуске резервной копии, чтобы избежать резервного копирования поврежденной базы данных. Дополнительные сведения см. в дополнительных сведениях о возможныхошибках мультимедиа во время резервного копирования и восстановления (SQL Server).

Вы также можете включить флаг трассировки 3023, чтобы включить проверку при запуске резервных копий с помощью средств резервного копирования. Дополнительные сведения см. в дополнительных сведениях о том, как включить параметр CHECKSUM, если службы резервного копирования не раскрывают этот параметр.

Чтобы устранить эти проблемы, необходимо либо найти другой файл резервного копирования, который можно найти, либо создать новый набор резервного копирования. Корпорация Майкрософт не предлагает решений, которые могут помочь получить данные из поврежденного набора резервного копирования.

Примечание

Если файл резервного копирования успешно восстанавливается на одном сервере, но не на другом, попробуйте различные способы копирования файла между серверами. Например, попробуйте робокопию вместо обычной операции копирования.

Резервное копирование не удается из-за проблем с разрешениями

При попытке запуска операций резервного копирования баз данных возникает одна из следующих ошибок.

  • Сценарий 1. При запуске резервного копирования из SQL Server Management Studio резервное копирование сбой и возвращает следующее сообщение об ошибке:

    Резервное копирование сбой для сервера <Server name> . (Microsoft.SqlServer.SmoExtended)
    System.Data.SqlClient.SqlError: не удается открыть резервное устройство <device name> '. Ошибка операционной системы 5 (доступ отказано.). (Microsoft.SqlServer.Smo)

  • Сценарий 2. Плановые резервные копии сбой и создание сообщения об ошибке, которое вошел в историю работы неудалось задание, и это похоже на следующее:

    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.
    

Любой из этих сценариев может возникнуть, если у SQL Server учетной записи службы нет разрешений на чтение и запись в папку, в которую записывают резервные копии. Резервное копирование можно выполнить как в рамках задания, так и вручную из SQL Server Management Studio. В любом случае они всегда работают в контексте учетной записи SQL Server service. Поэтому, если учетная запись службы не имеет необходимых привилегий, вы получаете сообщения об ошибках, которые были отмечены ранее.

Дополнительные сведения см. в дополнительных сведениях.

Примечание

Вы можете проверить текущие разрешения учетной записи SQL службы в папке, переходя на вкладку Security в свойствах соответствующей папки, выбрав кнопку Advanced, а затем используя вкладку Эффективный доступ.

Сбой резервного копирования или восстановления операций с использованием сторонних приложений резервного копирования

SQL Server предоставляет средство виртуального интерфейса резервного копирования устройства (VDI). Этот API позволяет независимым поставщикам программного обеспечения интегрировать SQL Server в свои продукты для поддержки операций резервного копирования и восстановления. Эти API спроектированы для обеспечения максимальной надежности и производительности, а также для поддержки полного спектра функций SQL Server резервного копирования и восстановления. Это включает полный диапазон возможностей моментального снимка и горячего резервного копирования.

Общие действия по устранению неполадок

Дополнительные ресурсы

How It Works: How many databases can be backed up simultaneously?

Различные проблемы

Symptom/scenario Действия по исправлению ситуации или дополнительные сведения
Резервное копирование может привести к сбойу, если отслеживание изменений включено в базах данных и возвращает ошибки, похожие на следующие:

"Ошибка: 3999, строгость: 17, состояние: 1.

<Time Stamp> spid Не удалось смыть таблицу фиксации на <spid> диск в dbid 8 из-за ошибки 2601. Проверьте журнал ошибок, чтобы получить дополнительные сведения".


См. следующие статьи базы знаний Майкрософт:
Проблемы восстановления резервных копий зашифрованных баз данных Перемещение защищенной базы данных TDE в другое SQL Server
Попытка восстановления резервного копирования CRM Enterprise выпуске не удается в стандартном выпуске 2567984 "База данных не может быть запущена в этом выпуске SQL Server" при восстановлении Microsoft Dynamics CRM базы данных

Задаваемые SQL Server операций резервного копирования и восстановления

Как проверить состояние операции резервного копирования?

Используйте сценарий estimate_backup_restore, чтобы получить оценку времени резервного копирования.

Что делать, если SQL Server сбой в середине резервного копирования?

Перезапустите операцию восстановления или резервного копирования при перезапуске прерванной операции восстановления (Transact-SQL).

Можно ли восстановить резервные копии баз данных из более старых версий программы в более новых версиях и наоборот?

SQL Server резервное копирование не может быть восстановлено с помощью SQL Server более поздней версии, создав резервную копию. Дополнительные сведения см. в дополнительных сведениях в службе поддержки совместимости.

Как проверить резервное копирование SQL Server базы данных?

См. процедуры, задокументированные в отчете RESTORE - VERIFYONLY (Transact-SQL).

Как получить историю резервного копирования баз данных в SQL Server?

Узнайте, как получить историю резервного копированиябаз данных в SQL Server.

Можно ли восстановить 32-битные резервные копии на 64-битных серверах и наоборот?

Да. Формат SQL Server на диске одинаковый в 64-битной и 32-битной средах. Поэтому операции резервного копирования и восстановления работают в 64-битных и 32-битных средах.

Общие советы по устранению неполадок

  • Убедитесь в том, что в папке, в которую записывают резервные копии, необходимо SQL Server для учетной записи службы чтения и записи. Дополнительные сведения см. в "Разрешениях для резервного копирования".
  • Убедитесь, что папка, в которой пишутся резервные копии, будет иметь достаточно места для размещения резервных копий баз данных. Вы можете использовать сохраненную процедуру для получения приблизительной оценки размера резервного копирования sp_spaceused для определенной базы данных.
  • Всегда используйте последнюю версию SSMS, чтобы не сталкиваться с известными вопросами, связанными с конфигурацией рабочих мест и планов обслуживания.
  • Сделайте тестовый запуск заданий, чтобы убедиться, что резервные копии созданы успешно. Всегда добавляйте логику для проверки резервных копий.
  • Если вы планируете переместить системные базы данных с одного сервера на другой, просмотрите перемещение системных баз данных.
  • Если вы заметили периодические сбои резервного копирования, проверьте, возникли ли проблемы, которые уже устранены в последнем обновлении для SQL Server версии. Дополнительные сведения см. в SQL Server версии и обновления.
  • Чтобы планировать и автоматизировать резервное копирование для SQL express, см. в руб. Расписание и автоматизация резервных копий SQL Server баз данных в SQL Server Express.

Справочные темы для SQL Server операций резервного копирования и восстановления

  • Дополнительные сведения об операциях резервного копирования и восстановления см. в следующих темах в книге Online:

    "Резервное копирование и восстановление баз данных SQL Server".В этом разделе описаны понятия операций резервного копирования и восстановления для баз данных SQL Server, ссылки на дополнительные темы, а также подробные процедуры для выполнения различных задач резервного копирования или восстановления (например, проверки резервных копий и резервного копирования с помощью T-SQL или SSMS). Это родительский раздел по этому вопросу в SQL Server документации.

  • В следующей таблице перечислены дополнительные темы, которые можно просмотреть для определенных задач, связанных с операциями резервного копирования и восстановления.

    Справочные материалы Описание
    BACKUP (Transact-SQL) Предоставляет ответы на основные вопросы, связанные с резервными копиями. Приводит примеры различных видов операций резервного копирования и восстановления.
    Устройства резервного копирования (SQL Server) Предоставляет отличные ссылки для понимания различных устройств резервного копирования, резервного копирования сетевой доли, хранилища blob Azure и связанных с ними задач.
    Модели восстановления (SQL Server) Подробно описывает различные модели восстановления: Простые, полные и объемные. Предоставляет сведения о том, как модель восстановления влияет на резервные копии.
    Восстановление &: системные базы данных (SQL Server) Охватывает стратегии и обсуждает, что необходимо знать при работе с резервной копией и восстановлением системных баз данных.
    Обзор восстановления и восстановления (SQL Server) Описывает, как модели восстановления влияют на операции восстановления. Вы должны рассмотреть этот вопрос, если у вас есть вопросы о том, как модель восстановления базы данных может повлиять на процесс восстановления.
    Управление метаданными при создании базы данных на другом сервере Различные соображения, которые следует учитывать при перемещении базы данных или при каких-либо проблемах, влияющих на входы, шифрование, репликацию, разрешения и так далее.
    Работа с резервной копией журнала транзакций Представлены понятия о том, как восстанавливать и восстанавливать (применять) журналы транзакций в полных и объемных моделях восстановления. Объясняет, как принимать обычные резервные копии журналов транзакций (резервного копирования журналов) для восстановления данных.
    SQL Server Управляемое резервное копирование Microsoft Azure Вводится управляемое резервное копирование и связанные процедуры.