Выполнение полного восстановления базы данных (модель полного восстановления)

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

• Убедитесь в том, что в папке, в которую записывают резервные копии, необходимо SQL Server для учетной записи службы чтения и записи. Дополнительные сведения см. в «Разрешениях для резервного копирования».
• Убедитесь, что папка, в которой пишутся резервные копии, будет иметь достаточно места для размещения резервных копий баз данных. Вы можете использовать сохраненную процедуру для получения приблизительной оценки размера резервного копирования для определенной базы данных.
• Всегда используйте последнюю версию 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	Вводится управляемое резервное копирование и связанные процедуры.

Аннотация

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

В SQL Server Express пользователи могут использовать один из следующих методов:

Используйте SQL Server Management Studio или Azure Data Studio. Дополнительные сведения о том, как использовать эти средства для обеспечения базы данных, просмотрите следующие ссылки:

• Используйте сценарий Transact-SQL, который использует семейство команд BACKUP DATABASE. Дополнительные сведения см. в резервной копии (Transact-SQL).

В этой статье описывается, как использовать сценарий Transact-SQL совместно с task Scheduler для автоматизации резервного копирования SQL Server Express баз данных на плановой основе.

Примечание

Это относится только к SQL Server, а не к SQL Server Express LocalDB.

Зачем это нужно

Грамотная отладка резервного копирования баз данных (БД) очень важна. Она помогает сохранить БД в случае каких-либо сбоев (программных, технических и т.д.), повреждений или разрушений в месте хранения

Особенно важно делать копии баз данных владельцам собственных онлайн-проектов, размещенных их на хостинге у провайдера. Бэкап станет «спасательным кругом» на тот случай, если будут утрачены важные данные

Специалисты рекомендуют настраивать регулярное копирование, используя различные его типы. Лучше всего сохранять копии за последние семь дней.

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

Отсоединение базы данных

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

Базу данных невозможно отсоединить в следующих случаях.

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

  Примечание

  Если невозможно использовать процедуру sp_replicationdboption, можно удалить репликацию, выполнив процедуру sp_removedbreplication.

• Имеется моментальный снимок базы данных.

  Перед отсоединением базы данных необходимо удалить все моментальные снимки. Дополнительные сведения см. в разделе Удаление моментального снимка базы данных (Transact-SQL).

  Примечание

  Невозможно отсоединить или присоединить моментальный снимок базы данных.

• Эта база данных является частью группы доступности AlwaysOn.

  База данных не может быть отсоединена, пока она не будет удалена из группы доступности. Дополнительные сведения см. в разделе Удаление базы данных — источника из группы доступности Always On.

• База данных находится в сеансе зеркального копирования.

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

  

  

  

  

  

  

  

  

  

  

• База данных помечена как подозрительная. Подозрительную базу данных невозможно отсоединить. Для отсоединения ее необходимо перевести в аварийный режим. Дополнительные сведения о переводе базы данных в аварийный режим см. в разделе ALTER DATABASE (Transact-SQL).

• База данных является системной базой данных.

Резервное копирование, восстановление и отсоединение

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

Реакция на ошибки отсоединения

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

1. Заново присоедините все файлы, связанные с базой данных, а не только первичный файл.

2. Исправьте неполадку, ставшую причиной сообщения об ошибке.

3. Отсоедините базу данных повторно.

С помощью командной строки (cmd)

Данный способ удобно использовать для автоматизации резервного копирования. Более того, команды подходят как для Windows, так и Linux. Выполняется при помощи утилиты sqlcmd.

Синтаксис:

sqlcmd -S <server> -U <user> -P <password> -Q «BACKUP DATABASE TO DISK = N'<file path>’ <options>»

Пример готового скрипта

@echo off
set dd=%DATE:~0,2%
set mm=%DATE:~3,2%
set yyyy=%DATE:~6,4%
set curdate=%dd%-%mm%-%yyyy%
set username=sa
set password=my_pass
set db=work1
sqlcmd -S localhost -U %username% -P %password% -Q «BACKUP DATABASE TO DISK = N’D:\Backup\MSSQL\%db%_%curdate%.bak’ WITH NOFORMAT, NOINIT, NAME = N’%db%-full’, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10»
set db=work2
sqlcmd -S localhost -U %username% -P %password% -Q «BACKUP DATABASE TO DISK = N’D:\Backup\MSSQL\%db%_%curdate%.bak’ WITH NOFORMAT, NOINIT, NAME = N’%db%-full’, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10»

* в данном примере мы подключаемся к локальному SQL серверу под учетной записью sa с паролем my_pass и делаем резервную копию баз work1 и work2. Резервные копии размещаем по пути D:\Backup\MSSQL. Имя файлов резервных копий work1_<текущая дата>.bak и work2_<текущая дата>.bak* некоторые опции могут не работать, в зависимости от используемой редакции MS SQL.

Для автоматизации скрипта, создайте задание в планировщике, чтобы скрипт запускался по расписанию.

Типы резервных копий

Хорошей практикой является создание разных типов копий:

1) Полное копирование — резервирование всей базы. Выполняется командой, рассмотренной выше, например:

sqlcmd -S localhost -U sa -P my_pass -Q «BACKUP DATABASE work1 TO DISK = N’D:\Backup\MSSQL\bak_full.bak’ WITH NOFORMAT, NOINIT, NAME = N’bak-full’, SKIP, NOREWIND, NOUNLOAD, STATS = 10»

* в данном примере мы подключаемся к локальному серверу под пользователем sa с паролем my_pass и делаем полную копию базы work1; саму копию сохраняем в виде файла D:\Backup\MSSQL\bak_full.bak.

2) Разностное (дифференциальное) — резервирование базы данных с момента создания последней полной копии. Выполняется командой для резервного копирования с добавлением опции DIFFERENTIAL:

sqlcmd -S localhost -U sa -P my_pass -Q «BACKUP DATABASE work1 TO DISK = N’D:\Backup\MSSQL\bak_diff.bak’ WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N’bak-diff’, SKIP, NOREWIND, NOUNLOAD, STATS = 10»

3) Инкрементальное или копирование логов. Выполняется Transact-SQL:

sqlcmd -S localhost -U sa -P my_pass -Q «BACKUP LOG work1 TO DISK = N’D:\Backup\MSSQL\bak_log.bak’ WITH NOFORMAT, NOINIT, NAME = N’bak-log’, SKIP, NOREWIND, NOUNLOAD, STATS = 10»

* обратите внимение, команда похожа на команду для полного резервного копирования — вместо DATABASE пишем LOG.

Резервное копирование с помощью плана обслуживания

Как и в задаче резервного копирования, описанной выше, мастер планов обслуживания в SQL Server Management Studio включает URL-адрес как один из вариантов назначения, а также другие вспомогательные объекты, необходимые для резервного копирования в хранилище Azure, такие как учетные данные SQL. Дополнительные сведения см. в разделе Определение задач резервного копирования статьи .

Примечание

Для создания чередующегося набора архивации, резервных копий моментальных снимков файлов SQL Server или учетных данных SQL с использованием маркера общего доступа необходимо использовать Transact-SQL, Powershell или C#, а не задачу резервного копирования в мастере планов обслуживания.

Миграция

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

Чтобы опубликовать схему и перенести данные с помощью SSMA для Access, выполните следующие действия.

1. Если вы этого еще не сделали, выберите элемент Подключение к SQL Server и укажите сведения о подключении.

2. Опубликуйте схему. Для этого щелкните правой кнопкой мыши базу данных в области Обозреватель метаданных SQL Server и выберите пункт Синхронизировать с базой данных. Это действие опубликует схему MySQL в SQL Server.

   

   

   

   

   

   

   

   

   

   

3. Проверьте результаты сопоставления исходного и целевого проектов.

4. Перенесите данные. Для этого щелкните правой кнопкой мыши базу данных или объект, которые требуется перенести, в разделе Обозреватель метаданных Access и выберите пункт Перенести данные. Кроме того, можно выбрать вкладку Перенос данных. Чтобы перенести данные для всей базы данных, установите флажок рядом с именем базы данных. Чтобы перенести данные из отдельных таблиц, разверните базу данных, разверните узел Таблицы и установите флажок рядом с нужной таблицей. Чтобы не переносить данные из определенной таблицы, снимите флажок.

5. После завершения миграции изучите отчет о переносе данных.

6. Подключитесь к экземпляру SQL Server с помощью SQL Server Management Studio и проверьте результаты миграции, просмотрев данные и схему.

Тонкая настройка ежедневного резервного копирования базы данных 1С средствами SQL ver. 2014 (SP3) — 12.0.6024.0 (X64)

Хочу вам предложить небольшой пример, как можно реализовать резервное копирование 1С-ых баз данных средствами SQL. Данный материал не претендует на пулитцеровскую премию. Но возможно кому-то будет интересно узнать, что-то новенькое.
Данный материал для резервного копирования только одной базы данных. А именно, если у вас 20-ть баз, то вам придется создавать 20-ть планов обслуживания для каждой базы индивидуально.
(Слава разработчикам SQL, они разрешили копировать блоки из одного плана в другой, вам остается только произвести небольшую настройку для каждого скопированного блока — некоторые настройки блоков сбрасываются и выставляются значением по умолчанию и остаются неактивными)

Перемещение файлов баз данных

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

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

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

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

Дополнительные сведения см. в подразделе «Восстановление файлов и файловых групп в новое место назначения» далее в этом разделе.

Рекомендации

• Для обеспечения оптимальной производительности обновленной базы данных выполните процедуру sp_updatestats (Transact-SQL) (обновление статистики) для обновленной базы данных.

• Если база данных перемещается или копируется с одного экземпляра сервера на другой, для обеспечения однородности среды для пользователей и приложений может потребоваться повторное создание некоторых (или даже всех) метаданных базы данных: имен входа, заданий и т. д. Дополнительные сведения см. в статье Управление метаданными при обеспечении доступности базы данных на другом экземпляре сервера (SQL Server).

Резервное копирование баз данных

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

• База данных публикации на издателе.

• База данных распространителя на распространителе.

• База данных подписки на подписчике.

• Системные базы данных master и msdb на издателе, распространителе и на всех подписчиках. Резервные копии этих баз данных и копия соответствующей базы данных репликации должны быть сделаны одновременно. Например, создайте резервную копию баз данных master и msdb на издателе одновременно с резервной копией базы данных публикации. Если база данных публикации восстановлена, убедитесь, что базы данных master и msdb согласованы с базой данных публикации по параметрам и конфигурации репликации.

Если резервное копирование журналов выполняется регулярно, любые изменения, касающиеся репликации, будут заноситься в резервные копии журнала. Если не выполняется резервное копирование журналов, то необходимо выполнить это резервное копирование при каждом изменении настройки, относящейся к репликации. Дополнительные сведения см. в статье Common Actions Requiring an Updated Backup.

Использование среды SQL Server Management Studio

Присоединение базы данных

В SQL Server Management Studio обозревателе объектов Компонент SQL Server Database Engineподключитесь к экземпляру компонента и разверните его представление в SSMS.

Щелкните правой кнопкой мыши узел Базы данных и выберите команду Присоединить.

Чтобы указать присоединяемую базу данных, в диалоговом окне Присоединение баз данных нажмите кнопку Добавить, в диалоговом окне Расположение файлов базы данных выберите диск, на котором находится база данных, и разверните дерево каталогов, чтобы найти и выбрать MDF-файл, например:

Важно!
При попытке выбора базы данных, которая уже присоединена, возникает ошибка.

Базы данных для присоединения
Отобразятся сведения о выбранных базах данных.

Отображается значок, указывающий на состояние операции присоединения. Возможные значки описываются в приводимом ниже описании Состояние .
Расположение файла MDF
Отображается путь и имя выбранного MDF-файла.
Имя базы данных
Отображается имя базы данных.
Присоединить как
Необязательный параметр, указывает другое имя, под которым присоединяется база данных.
Владелец
Содержит раскрывающийся список возможных владельцев базы данных, из которого при необходимости можно выбрать другого владельца.
Состояние
Отображается состояние базы данных в соответствии со следующей таблицей.
Значок
Текст состояния
Описание
(Нет значка)
(Нет текста)
Операция присоединения не была запущена или находится в режиме ожидания для этого объекта

Это состояние по умолчанию при открытии диалогового окна.
Зеленый, указывающий направо треугольник
Выполняется
Операция присоединения была запущена, но не завершена.
Зеленый флажок
Успешно
Объект успешно присоединен.
Красный кружок с белым крестом внутри
Error
При выполнении операции присоединения возникла ошибка, и операция не была успешно завершена.
Кружок с двумя черными квадратами (слева и справа) и двумя белыми квадратами (сверху и снизу)
Остановлена
Операция присоединения не была успешно завершена, т.к. пользователь остановил операцию.
Кружок, содержащий изогнутую стрелку, указывающую в направлении против часовой стрелки
Выполнен откат
Операция присоединения была успешной, но был выполнен ее откат из-за ошибки, возникшей при вложении другого объекта.
Сообщение
Отображается пустое сообщение или гиперссылка «Файл не найден».
Добавление
Найдите необходимые основные файлы базы данных. Если пользователь выбирает mdf-файл, необходимые сведения автоматически вводятся в соответствующие поля сетки Базы данных для присоединения .
Удалить
Удаляет выбранный файл из сетки Базы данных для присоединения .
Сведения о базе данных » »
Отображаются имена файлов, которые необходимо присоединить. Чтобы проверить или изменить путь к файлу, нажмите кнопку Обзор ( … ).

Примечание
Если файл не существует, в столбце Сообщение отображается сообщение «Не найден». Если файл журнала не найден, то он существует в другом каталоге или был удален. Необходимо или обновить путь файла в сетке Сведения о базе данных таким образом, чтобы этот путь указывал на правильное расположение, или удалить файл журнала из сетки. Если MDF-файл не найден, необходимо обновить путь этого файла в сетке таким образом, чтобы этот путь указывал на правильное расположение.

Имя исходного файла
Отображается имя присоединенного файла, принадлежащего базе данных.
Тип файла
Указывается тип файла: Данные или Журнал.
Текущий путь к файлу
Отображается путь к выбранному файлу базы данных. Путь может быть изменен вручную.
Сообщение
Отображается пустое сообщение или гиперссылка «Файл не найден».

Использование Transact-SQL

Присоединение базы данных

1. Установите соединение с компонентом Компонент Database Engine.

2. На панели «Стандартная» нажмите Создать запрос.

3. Выполните инструкцию CREATE DATABASE с предложением .

   Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере производится присоединение файлов базы данных AdventureWorks2012 с ее последующим переименованием в .

   Примечание

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

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

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

Используйте следующий метод для копирования базы данных, которая содержится в более поздней версии 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. Используйте для копирования данных из разных таблиц.
3. В SQL_B выполните следующие действия:
   1. Используйте скрипты, созданные на сервере SQL_A, чтобы создать схему базы данных.
   2. На каждой из таблиц отключай все внешние ограничения и триггеры. Если в таблице есть столбцы удостоверений, введите вставку удостоверения.
   3. Используйте bcp для импорта данных, экспортируемых на предыдущем этапе, в соответствующие таблицы.
   4. После завершения импорта данных включаем внешние ограничения и триггеры и отключаем вставку удостоверений для каждой из таблиц, затронутых в шаге c.

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

Дополнительные сведения о том, как создавать скрипты для базы данных, см. в тексте

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

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

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

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

• Настройка резервных копий на вторичные реплики группы always On availability
• Offload поддерживает резервное копирование вторичных реплик группы доступности

Тонкая настройка ежедневного резервного копирования базы данных 1С средствами SQL ver. 2014 (SP3) — 12.0.6024.0 (X64)

Хочу вам предложить небольшой пример, как можно реализовать резервное копирование 1С-ых баз данных средствами SQL. Данный материал не претендует на пулитцеровскую премию. Но возможно кому-то будет интересно узнать, что-то новенькое.
Данный материал для резервного копирования только одной базы данных. А именно, если у вас 20-ть баз, то вам придется создавать 20-ть планов обслуживания для каждой базы индивидуально.
(Слава разработчикам SQL, они разрешили копировать блоки из одного плана в другой, вам остается только произвести небольшую настройку для каждого скопированного блока — некоторые настройки блоков сбрасываются и выставляются значением по умолчанию и остаются неактивными)