take tail log backup before restore что это

Резервное копирование и восстановление базы данных в MS SQL Server

В этой статье мы рассмотрим, как настроить резервное копирование баз данных в Microsoft SQL Server, покажем, как восстановить базу данных из резервной копии с помощью SQL Server Management Studio и Transact-SQL. Первая часть статьи посвящена теоретическим аспектам резервного копирование в SQL, во второй на примере мы покажем, как настроить регулярное резервное копирование базы данных MS SQL с помощью плана обслуживания и восстановить базу из резервной копии на примере установленного Microsoft SQL Server 2019.

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

Следует понимать, что с помощью механизмов резервного копирования невозможно добиться резервирования данных в реальном времени. Для этой цели используются другие технологии высокой доступности SQL Server – группы доступности Always On, зеркалирование баз данных или репликация.

Типы резервного копирования SQL Server

Полное (Full Backup)

Полное резервное копирование делает копию всей базы данных, включая все объекты и данные системных таблиц. Полная резервная копия не будет усекать (truncate) журнал транзакций. Это основной тип резервных копий, который требуется выполнять перед другими типами резервных копий.

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

Если модель восстановления базы SQL данных установлена как “Полная”, то при восстановлении бекапа вы можете указать параметр “STOPAT”, где указывается время (до секунды) на котором нужно остановить восстановление данных. Например, сотрудник внёс некорректные данные в 14:46:07, с помощью параметра STOPAT вы можете восстановить данные на момент 14:46:06

Дифференциальное

Дифференциальное или разностное резервное копирование — это копирование только тех данных, которые появились с момента последней полной резервной копии.

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

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

Например, если полная резервная копия весит 300 GB, а дифференциальная спустя час работы 5 GB, то спустя сутки это будет 120 GB, что делает использование данного типа копий нерациональным.

Журнал транзакций

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

Восстанавливая журнал транзакций, вы также можете указать параметр STOPAT, как и в восстановлении полной резервной копии.

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

Tail-Log

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

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

Copy-only

За исключением этих нюансов – ничем не отличается от обычной полной копии.

Частичная резервная копия

Partial backup этот тип резервной копии используется для того, чтобы снять копии с read-only файловых групп. На практике используется редко.

Резервное копирование файлов и файловых групп

Используется для снятия резервных копий определенных файлов или файловых групп.

Модели восстановления базы данных SQL Server

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

Простая модель восстановления

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

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

При использовании этой модели восстановления, следующий функционал SQL Server недоступен:

Полная модель восстановления

Полная модель восстановления хранит все транзакции в журнале транзакций до усечения журнала (посредством снятия резервной копии журнала).

Это самая “надежная” модель восстановления, при аварийном сбое можно вы сможете восстановить все транзакции, кроме тех, которые не успели завершиться при аварии.

Эта модель нуждается в обслуживании журналов транзакций (регулярные резервные копии), иначе журналы займут всё дисковое пространство.

Восстановление с неполным протоколированием (bulk logged)

Эта модель, также, как и полная, записывает все транзакции в журнал транзакций, за исключением таких операций как:

В остальном эта модель работает аналогично полной модели восстановления.

Настройка резервного копирования SQL Server с помощью плана обслуживания

Планы обслуживания SQL Server это самый распространенный способ настройки регулярного резервного копирования.

Рассмотрим настройку резервного базы данных на SQL Server копирования по плану:

Укажите имя плана и выберите режим “Separate schedules for each task”.

Выберите операции, которые нужно сделать в этом плане обслуживания:

Используйте следующую последовательность операций:

Выберите базу данных SQL Server, которую нужно бэкапить и выберите расписание.

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

Укажите сколько будут храниться резервные копии (например, 14 дней).

Нажмите Next и аналогично создайте расписание резервного копирования для журнала транзакций.

Опционально можно указать файл для ведения лога плана обслуживания.

Завершение настройки плана обслуживания SQL Server.

Выполните план обслуживания вручную и проверьте журнал.

Как вы видите была создана полная резервная копия базы данных SQL Server и следом копия журнала транзакций. На этом настройка резервного копирования закончена.

Восстановление базы данных SQL Server из резервной копии

Теперь рассмотрим, как восстановить базы данных SQL Server из резервной копии. Для восстановления базы можно использовать графическую консоль SQL Server Management Studio или язык T-SQL.

Восстановление резервной копии с помощью SQL Server Management Studio

Запустите SSMS, щелкните по разделу Database и выберите пункт Restore Database.

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

Для примера, воспользуемся Point-In-Time восстановлением и выберем момент, на который мы хотим восстановить базу данных. Нажмите Timeline.

Выберите опцию “Close existing connections to destination database”, если ваша база данных находится в статус Online

Нажмите ОК. После этого база данных восстановится на выбранный момент времени.

Восстановление базы данных MS SQL Server с помощью T-SQL

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

USE [master]
ALTER DATABASE [TestDatabase2] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
BACKUP LOG [TestDatabase2] TO DISK = N’E:\MSSQL15.NODE2\MSSQL\Backup\TestDatabase2_LogBackup_2020-02-17_15-39-43.bak’ WITH NOFORMAT, NOINIT, NAME = N’TestDatabase2_LogBackup_2020-02-17_15-39-43′, NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY, STATS = 5
RESTORE DATABASE [TestDatabase2] FROM DISK = N’E:\MSSQL15.NODE2\MSSQL\Backup\full.bak’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [TestDatabase2] FROM DISK = N’E:\MSSQL15.NODE2\MSSQL\Backup\trans.bak’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [TestDatabase2] FROM DISK = N’E:\MSSQL15.NODE2\MSSQL\Backup\trans.bak’ WITH FILE = 2, NOUNLOAD, STATS = 5, STOPAT = N’2020-02-17T15:38:23′
ALTER DATABASE [TestDatabase2] SET MULTI_USER
GO

В данном случае база данных переводится в SINGLE_USER, но нужно быть аккуратным с этим параметром, так как в некоторых ситуациях вы можете закрыть себе доступ, если кто-то откроет сессию раньше вас.

Дальше выполняется tail-log бекап, затем восстанавливается полный бекап и следом восстанавливаются бекапы журнала транзакций. Обратите внимание на параметр STOPAT, база данных восстановиться на момент 15:38:23

Рекомендации и best practice по резервному копированию SQL Server

Источник

Резервные копии заключительного фрагмента журнала (SQL Server)

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

ПРИМЕЧАНИЕ. Не для всех сценариев восстановления требуется резервная копия заключительного фрагмента журнала. Резервная копия заключительного фрагмента журнала не нужна, если точка восстановления содержится в более ранней резервной копии журнала. Кроме того, резервная копия заключительного фрагмента журнала не требуется при перемещении или замещении (перезаписи) базы данных, при котором не нужно восстанавливать ее на определенный момент времени после создания ее последней резервной копии.

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

Рекомендуется формировать резервную копию заключительного фрагмента журнала в следующих сценариях.

Если база данных находится в режиме «в сети» и следующим действием над базой данных должна быть операция восстановления, то прежде необходимо выполнить резервное копирование заключительного фрагмента журнала. Во избежание ошибок для базы данных в режиме «в сети» необходимо использовать параметр WITH NORECOVERY инструкции BACKUP Transact-SQL.

Если база данных, работающая в режиме «вне в сети», не запускается и необходимо восстановить базу данных, то в первую очередь необходимо выполнить резервное копирование заключительного фрагмента журнала. Так как в это время никакие транзакции не выполняются, параметр WITH NORECOVERY использовать не обязательно.

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

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

В следующей таблице представлена сводка параметров BACKUP NORECOVERY и CONTINUE_AFTER_ERROR.

Резервные копии заключительного фрагмента журнала с неполными метаданными

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

Если резервная копия заключительного фрагмента журнала содержит неполные метаданные, то параметр has_incomplete_metadata в таблице backupset принимает значение 1. Кроме того, выходной аргумент HasIncompleteMetadataинструкции RESTORE HEADERONLY принимает значение 1.

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

Связанные задачи

Инструкции по восстановлению резервной копии журнала транзакций см. в разделе Восстановление резервной копии журнала транзакций (SQL Server).

Источник

Восстановление базы данных из резервной копии в MS SQL Server 2012

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

0. Оглавление

1. Восстановление базы данных

Подключаемся к MS SQL Server c помощью программы «SQL Server Management Studio». В Microsoft Windows Server 2012 R2 ее можно найти в списке всех программ.

В Microsoft Windows Server 2008 R2 в меню «Пуск» (Start) — «Microsoft SQL Server 2012» — «Среда SQL Server Management Studio».

Вводим адрес сервера или его псевдоним, данные для авторизации и нажимаем «Соединить» (Connect).

Слева, в обозревателе объектов (Object Explorer), раскрываем вкладку «Базы данных» (Server Oblects), находим в списке базу данных из которой (или в которую) необходимо восстановить данные, кликаем по ней правой кнопкой мыши, затем в появившемся контекстном меню выбираем «Задачи» (Tasks) — «Восстановить» (Restore) — «База данных…» (Database…)

Запустится мастер восстановления базы данных (Restore Database). Выбираем базу источник (Source for restore), при этом мастер попробует автоматически подобрать последовательность файлов резервных копий для восстановления базы на текущий момент времени.

Если же требуется загрузить данные из конкретного файла или устройства резервного копирования, то необходимо установить соответствующий переключатель в положение «Устройство» (From device) и вручную указать источник для восстановления.

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

Нажав кнопку «Временная шкала…» (Timeline) можно указать время на которое необходимо восстановить данные. При имеющейся копии журнала транзакций время восстановления можно выбрать с точностью до секунды (или имеющегося checkpoint’а в журнале транзакций).

Очень важно (!) также помнить о том, что если восстановление данных осуществляется в информационную базу отличную от той с которой производилось резервное копирование (т. е. необходимо скопировать базу данных) то на вкладке «Файлы» (Files) необходимо указать путь к файлам этой информационной базы.

На вкладке «Параметры» (Options) можно указать дополнительные параметры резервного копирования. В частности:

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

2. Просмотр информации о событиях резервного копирования и восстановления для базы данных

Для того чтобы узнать, когда производилось создание резервных копий конкретной базы данных, а также восстановление базы данных из резервной копии, можно воспользоваться стандартным отчетом «События резервного копирования и восстановления» (Backup and Restore Events). Для формирования данного отчета необходимо в Обозревателе объектов (Server Oblects) кликнуть правой кнопкой мыши по соответствующей базе данных, в контекстном меню выбрать «Отчеты» (Reports) — «Стандартный отчет» (Standart Reports) — «События резервного копирования и восстановления» (Backup and Restore Events).

Сформировавшийся отчет содержит в себе следующие данные:

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

Смотрите также:

Ниже приведена пошаговая инструкция, показывающая как добавить новую базу данных в Microsoft SQLServer 2012 (в более старых редакциях, например в Microsoft SQL Server 2008 R2, набор действий аналогичен). Запускаем…

Системная база данных tempdb служит рабочим пространством для хранения временных объектов, таких как временные таблицы, промежуточные результаты вычислений, временные хранимые процедуры, результаты буферов и сортировки, внутренние объекты, создаваемые компонентой Database…

Ниже будет подробно рассказано о том, как создать резервную копию базы данных в MS SQL Server 2012. В младших версиях (например в MS SQL Server 2008) алгоритм получения резервной копии…

Источник

Создание резервной копии журнала транзакций

В этом разделе описано, как создать резервную копию журнала транзакций в SQL Server с помощью среды SQL Server Management Studio, Transact-SQLили PowerShell.

Перед началом

Ограничения

Инструкция BACKUP не разрешена в явных и неявных транзакциях. Явными транзакциями являются транзакции, для которых явно назначаются запуск и остановка.

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

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

По умолчанию каждая успешная операция резервного копирования добавляет запись в журнал ошибок служб SQL Server и в журнал системных событий. Если создание резервной копии журналов производится очень часто, сообщения об успешном завершении накапливаются очень быстро. Это приводит к увеличению журналов ошибок, затрудняя поиск других сообщений. Если работа существующих скриптов не зависит от этих записей, то их можно отключить с помощью флага трассировки 3226. См. статью Флаги трассировки (Transact-SQL).

Permissions

Нужные разрешения BACKUP DATABASE и BACKUP LOG назначаются по умолчанию членам предопределенной роли сервера sysadmin и предопределенным ролям базы данных db_owner и db_backupoperator. Перед началом убедитесь в наличии необходимых разрешений.

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

После подключения к соответствующему экземпляру компонента Компонент SQL Server Database Engineв обозревателе объектов разверните дерево сервера, щелкнув его имя.

Раскройте узел Базы данных и в зависимости от типа восстанавливаемой базы данных выберите пользовательскую базу данных или раскройте узел Системные базы данных и выберите системную базу данных.

В списке База данных проверьте имя базы данных. При необходимости можно выбрать другую базу данных из списка.

Выберите Журнал транзакций в списке Тип резервного копирования.

(Необязательно) Выберите вариант Копировать только резервные копии, чтобы создать резервную копию только для копирования. Резервная копия только для копирования — это резервная копия SQL Server, которая не зависит от последовательности создания традиционных резервных копий SQL Server. См. статью Резервные копии только для копирования (SQL Server).

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

Укажите, когда истекает срок действия резервного набора данных.

Чтобы задать срок действия резервного набора данных, выберите пункт После (параметр по умолчанию) и введите срок действия набора в днях с момента его создания. Это значение может быть задано в диапазоне от 0 до 99 999 дней. Значение 0 означает, что срок действия резервного набора данных не ограничен.

Чтобы указать дату истечения срока действия резервного набора данных, выберите пункт На и введите дату истечения срока действия резервного набора данных.

Чтобы удалить носитель резервной копии, выберите его и нажмите кнопку Удалить. Чтобы просмотреть содержимое носителя резервной копии, выберите его и щелкните Содержимое.

Создать резервную копию в существующем наборе носителей

Для этого параметра выберите вариант Добавить в существующий резервный набор данных или Перезаписать все существующие резервные наборы данных. См. статью Наборы носителей, семейства носителей и резервные наборы данных (SQL Server).

(Необязательно) Выберите Проверить имя набора носителей и срок действия резервного набора данных, чтобы при выполнении операции резервного копирования проверялся срок действия набора носителей и резервного набора данных.

(Необязательно) Введите имя в текстовом поле Имя набора носителей. Если имя не указано, создается набор носителей с пустым именем. Если имя набора носителей указано, то для носителя (ленточного или дискового) проверяется совпадение введенного и существующего имени.

Если оставить имя носителя пустым и установить рядом с ним флажок для проверки, имя носителя при успешном завершении также станет пустым.

Создать резервную копию в новом наборе носителей и удалить все существующие резервные наборы данных

Для этого параметра введите имя в текстовом поле Имя нового набора носителей и при необходимости введите описание набора носителей в текстовом поле Описание нового набора носителей. См. статью Наборы носителей, семейства носителей и резервные наборы данных (SQL Server).

В разделе Надежность можно установить следующие флажки.

Проверить резервную копию после завершения.

Рассчитать контрольную сумму перед записью на носитель и (необязательно) Продолжить при ошибке контрольной суммы.

В разделе Журнал транзакций можно установить следующие флажки.

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

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

Резервное копирование заключительного фрагмента журнала выполняется после сбоя, чтобы предотвратить потерю сделанной работы. Резервное копирование активного журнала (резервное копирование заключительного фрагмента журнала) следует выполнять как после сбоя, так и перед началом восстановления базы данных, а также при сбое базы данных-получателя. Выбор этого параметра равносилен применению параметра NORECOVERY в инструкции BACKUP LOG языка Transact-SQL.

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

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

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

Выполните инструкцию BACKUP LOG для создания резервной копии журнала транзакций, указав следующее:

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

Устройство резервного копирования, на которое записывается резервная копия журнала транзакций.

Использование PowerShell

Источник