Операции резервного копирования операции с файлами такие как alter database add file

Обновлено: 07.07.2024

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

Файл базы данных

Базы данных SQL Server имеют три типа файлов:

Файл основных данных
Основной файл данных является отправной точкой базы данных. Помимо системы хранения и пользовательских данных, в файле основных данных также хранится такая информация, как путь, имя и размер всех файлов вспомогательных данных в базе данных и файл журнала повторов. SQL Server получает информацию о других файлах данных и восстанавливает файлы журналов, читая основной файл данных. Эта функция аналогична управляющему файлу Oracle. Каждая база данных имеет файл основных данных. Рекомендуемое расширение файла для файла основных данных .mdf.
Вторичный файл данных
Все остальные файлы данных, кроме основного файла данных, являются вторичными файлами данных, которые обычно хранят только пользовательские данные. Некоторые базы данных могут не содержать никаких вторичных файлов данных, в то время как некоторые базы данных содержат несколько вторичных файлов данных. Рекомендуемое расширение файла для вторичных файлов данных .ndf.
Лог-файл
Файл журнала содержит всю информацию журнала, используемую для восстановления базы данных. В каждой базе данных должен быть хотя бы один файл журнала, разумеется, их может быть несколько. Рекомендуемое расширение файла для файлов журнала .ldf.

SQL Server не требует использования расширений файлов .mdf, .ndf и .ldf, но их использование может помочь идентифицировать различные типы и использование файлов.
В SQL Server местоположения всех файлов в базе данных записываются в главном файле и главной базе данных базы данных. В большинстве случаев ядро базы данных SQL Server использует информацию о расположении файлов в базе данных master. Однако в следующих случаях ядро базы данных использует информацию о расположении файла главного файла для инициализации элемента местоположения файла в главной базе данных:

При использовании оператора CREATE DATABASE с опцией FOR ATTACH или FOR ATTACH_REBUILD_LOG для присоединения базы данных.
При обновлении с SQL Server 2000 или 7.0.
При восстановлении основной базы данных.

Группа файлов базы данных

Для облегчения распространения и управления объекты базы данных и файлы могут быть разделены на группы файлов вместе. Файловая группа SQL Server состоит из несколькихДата файлысочинение.
Файловая группа SQL Server - это основная файловая группа и пользовательская файловая группа, которые соответствуют системному табличному пространству и пользовательскому табличному пространству в базе данных Oracle соответственно.

основная группа файлов
Основная группа файлов содержит основной файл данных и любые другие файлы, которые явно не назначены другим группам файлов. Все страницы системных таблиц размещены в основной файловой группе. Подобно системному табличному пространству базы данных Oracle, первичная файловая группа не может быть удалена, и ее имя первичное также является фиксированным и не может быть изменено.
Пользовательская группа файлов
Пользовательская файловая группа - это любая файловая группа, указанная с помощью ключевого слова FILEGROUP в операторе CREATE DATABASE или ALTER DATABASE.

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

Файл не может быть членом нескольких групп файлов. Таблицы, индексы и данные больших объектов могут быть связаны с указанными группами файлов. В этом случае все их страницы будут выделены для группы файлов, или таблица и индекс будут разделены. Данные секционированных таблиц и индексов делятся на блоки, и каждый блок может быть помещен в отдельную файловую группу в базе данных.
В базе данных SQL Server запрещается удалять группу файлов, содержащую таблицу или индекс. Это отличается от Oracle. В Oracle, если табличное пространство содержит данные, когда табличное пространство удаляется с помощью отбрасывания табличного пространства , Вы можете добавить пункт включения содержимого.
Файловая группа в каждой базе данных назначается файловой группой по умолчанию. Если при создании таблицы или индекса файловая группа не указана, предполагается, что все страницы выделены из файловой группы по умолчанию. Только одна файловая группа может быть файловой группой по умолчанию одновременно. Если файловая группа по умолчанию не указана, основная файловая группа используется в качестве файловой группы по умолчанию. Члены предопределенной роли базы данных db_owner могут переключать файловую группу по умолчанию из одной файловой группы в другую.

Правила разработки для файлов и групп файлов
Следующие правила применяются к файлам и группам файлов:

Файл или файловая группа не могут использоваться несколькими базами данных. Например, никакая другая база данных не может использовать файлы sales.mdf и sales.ndf, которые содержат данные и объекты в базе данных продаж.
Файл может быть членом только одной группы файлов.
Файлы журнала транзакций не могут принадлежать какой-либо файловой группе.

Площадь (протяженность)

Экстент - это единица, которая выделяет место для хранения таблицы или индекса, а также является основной единицей управления пространством.
В SQL Server размер экстента представляет собой фиксированные 8 последовательных страниц данных, 64 КБ, что означает, что в базе данных SQL Server имеется 16 областей на МБ. При создании файловой группы нельзя указывать размер экстента, аналогичный условиям автоматического выделения или единообразного размера в Oracle, поэтому SQL Server менее гибок.

SQL Server выделяет экстенты для таблиц не так, как в Oracle. Чтобы сделать выделение пространства эффективным, SQL Server не будет выделять все области таблицам, которые содержат небольшой объем данных, поэтому SQL Server не будет выделять экстенты для пустых таблиц, и выделение расширений будет отложено до тех пор, пока записи не будут добавлены в таблицу.
SQL Server имеет два типа зон:

Смешанная область (смешанный экстент): смешанная область используется несколькими таблицами или индексами и может использоваться несколькими объектами. Каждая из восьми страниц в зоне может принадлежать другому объекту.
Единая степень: Единая степень принадлежит одному объекту. Все 8 страниц в зоне могут быть предназначены только для одной таблицы или индекса.

Обычно первые 8 страниц данных, выделенных для таблицы или индекса, размещаются в смешанной зоне, а последующие страницы данных - в объединенной зоне. Этот метод отличается от Oracle. Зона Oracle может быть назначена только одной таблице или индексу. Несколько объектов не могут быть общими, или можно сказать, что Oracle имеет только один тип объединенной зоны в SQL Server.

Управление файловыми группами и файловыми группами SQL Server

<add_or_modify_files> :: = </ add_or_modify_files>: укажите файлы, которые будут добавлены, удалены или изменены.
имя_базы_данных: имя базы данных, которую нужно изменить.
ДОБАВИТЬ ФАЙЛ: Добавить файл в базу данных.
TO FILEGROUP : укажите группу файлов, в которую нужно добавить указанный файл.
ADD LOG FILE: Добавьте файл журнала, который будет добавлен в указанную базу данных.
УДАЛИТЬ ФАЙЛ логическое_файл_имя: удалите описание логического файла и удалите физический файл из экземпляра SQL Server. Если файл не пуст, файл не может быть удален.
логическое_файл_имя: логическое имя, используемое при обращении к файлу в SQL Server.
MODIFY FILE: укажите файл, который необходимо изменить. Если указан SIZE, новый размер должен быть больше текущего размера файла.
Чтобы изменить логическое имя файла данных или файла журнала, укажите имя логического файла для переименования в предложении NAME и укажите новое логическое имя файла в предложении NEWNAME. Например:

Чтобы переместить файлы данных или файлы журналов в новое место, укажите текущее логическое имя файла в предложении NAME и укажите новый путь и имя файла операционной системы (физическое) в предложении FILENAME. Например:

os_file_name: для стандартных (ROWS) групп файлов это путь и имя файла, используемые операционной системой при создании файла.
'filestream_path': для группы файлов FILESTREAM FILENAME указывает путь, в котором будут храниться данные FILESTREAM.
memory_optimized_data_path: Для групп файлов, оптимизированных для памяти, FILENAME будет указывать путь, где будут храниться данные, оптимизированные для памяти. Атрибуты SIZE, MAXSIZE и FILEGROWTH не применяются к оптимизированным для памяти группам файлов.
FILEGROWTH: используется для указания размера каждого файла. Если определенное значение не указано, по умолчанию используется значение 1 МБ. Если указано значение 0, файл данных не может автоматически увеличиваться. В качестве единицы измерения можно использовать МБ, КБ, ГБ, ТБ или процент (%). Значение по умолчанию - МБ. Если указан%, то размер приращения - это определенный процент от размера файла при росте. Указанный размер округляется до ближайшего кратного 64 КБ.
OFFLINE: отключить файл и сделать все объекты в группе файлов недоступными.
<add_or_modify_filegroups> :: = </ add_or_modify_filegroups>: добавление, изменение или удаление файловых групп в базе данных.
СОДЕРЖИТ FILESTREAM: указывает, что файловая группа хранит большие двоичные объекты FILESTREAM (BLOB) в файловой системе.
CONTAINS MEMORY_OPTIMIZED_DATA: укажите группу файлов для хранения данных оптимизации памяти в файловой системе. В каждой базе данных может быть только одна группа файлов MEMORY_OPTIMIZED_DATA. При создании таблицы, оптимизированной для памяти, файловая группа не может быть пустой, и она должна содержать хотя бы один файл.
REMOVE FILEGROUP filegroup_name: удалите файловую группу filegroup_name, чтобы удалить файловую группу из базы данных. Если файловая группа не пуста, ее нельзя удалить. Сначала удалите все файлы из группы файлов.
MODIFY FILEGROUP filegroup_name: Изменить файловую группу.
ПО УМОЛЧАНИЮ: Измените группу файлов базы данных по умолчанию на имя_группы файлов. В качестве файловой группы по умолчанию может быть только одна файловая группа в базе данных.
AUTOGROW_SINGLE_FILE: Когда файлы в файловой группе соответствуют порогу автоматического роста, увеличивается только файл. Это значение по умолчанию.
AUTOGROW_ALL_FILES: если файлы в файловой группе достигают порога автоматического роста, все файлы в файловой группе растут.
<filegroup_updatability_option>: установить атрибуты только для чтения или чтения / записи для файловой группы.
READ_ONLY | READONLY: указанная группа файлов доступна только для чтения. Объекты в нем не могут быть обновлены. Основная группа файлов не может быть установлена только для чтения. Чтобы изменить этот статус, вы должны иметь эксклюзивный доступ к базе данных.
Поскольку базы данных только для чтения не позволяют изменять данные, произойдет следующее:
При запуске системы автоматическое восстановление будет пропущено.
Невозможно сжать базу данных.
не будет заблокирован в базе данных только для чтения. Это может ускорить запрос.

A. Добавить группу файлов, состоящую из двух файлов, в базу данных
В следующем примере создается группа файлов Test1FG1 в базе данных AdventureWorks2012, а затем добавляются два файла по 5 МБ в группу файлов.

Б. Добавьте два файла журнала в базу данных.

C. Удалить файлы из базы данных

D. Изменить файлы
Размер файла, добавленного в следующем примере. Команда ALTER DATABASE MODIFY FILE может увеличить размер файла, поэтому, если вам нужно уменьшить размер файла, вам нужно использовать DBCC SHRINKFILE.

В этом примере размер файла сжатых данных составляет 100 МБ, а затем укажите размер в этом размере.

E. переместить файл в новое место
Далее в качестве примера используется файл данных E: \ t1dat2.ndf в данных AdventureWorks в C: \ t1dat2.ndf, чтобы проиллюстрировать процесс перемещения файла данных.
Сначала переведите базу данных в автономный режим:

Переместите E: \ t1dat2.ndf в C: \ t1dat2.ndf в операционной системе:

Измените запись этого пути к файлу в базе данных:

Наконец, верните базу данных в оперативный режим:

Затем запросите физический путь к файлу t1dat2:

F. Сделать файловую группу файловой группой по умолчанию
В следующем примере Test1FG1 становится файловой группой по умолчанию. Затем группа файлов по умолчанию сбрасывается в группу файлов PRIMARY. Обратите внимание, что PRIMARY должен быть разделен скобками или кавычками.

Запрос информации назначенного экстента указанной таблицы

В SQL Server вы можете использовать команду dbcc exteninfo для запроса информации о выделенном экстенте таблицы.

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

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

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

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

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

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

Ограничения параллелизма во время резервного копирования. SQL Server использует оперативный процесс резервного копирования, что позволяет создавать резервную копию базы данных во время ее использования. Во время резервного копирования можно производить большинство операций. Например, во время создания резервной копии разрешены инструкции INSERT, UPDATE и DELETE. При попытке приступить к выполнению операции резервного копирования во время создания или удаления файла базы данных выполнение операции резервного копирования будет отложено до завершения создания или удаления либо до истечения времени ожидания.

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

Операции управления файлами, такие как инструкция ALTER DATABASE с параметром ADD FILE или с параметром REMOVE FILE.

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

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

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

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

Модели восстановления

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Та часть журнала, которая содержит активные транзакции и используется для восстановления данных называется активной частью журнала. Она начинается с номера, который называется минимальным номером восстановления (MinLSN).

В простейшем случае MinLSN - это номер записи первой незавершенной транзакции. Если посмотреть на рисунок выше, то открыв синюю транзакцию мы получим MinLSN равную 321, после ее фиксации в записи 324, номер MinLSN изменится на 323, что будет соответствовать номеру зеленой, еще не зафиксированной, транзакции.

На практике все немного сложнее, например, данные закрытой синей транзакции могут быть еще не сброшены на диск и перемещение MinLSN на 323 сделает восстановление этой операции невозможной. Для того, чтобы избежать таких ситуации было введено понятие контрольной точки. Контрольная точка создается автоматически при наступлении следующих условий:

При явном выполнении инструкции CHECKPOINT. Контрольная точка срабатывает в текущей базе данных соединения.
При выполнении в базе данных операции с минимальной регистрацией, например, при выполнении операции массового копирования для базы данных, на которую распространяется модель восстановления с неполным протоколированием.
При добавлении или удалении файлов баз данных с использованием инструкции ALTER DATABASE.
При остановке экземпляра SQL Server с помощью инструкции SHUTDOWN или при остановке службы SQL Server (MSSQLSERVER). И в том, и в другом случае будет создана контрольная точка каждой базы данных в экземпляре SQL Server.
Если экземпляр SQL Server периодически создает в каждой базе данных автоматические контрольные точки для сокращения времени восстановления базы данных.
При создании резервной копии базы данных.
При выполнении действия, требующего отключения базы данных. Примерами могут служить присвоение параметру AUTO_CLOSE значения ON и закрытие последнего соединения пользователя с базой данных или изменение параметра базы данных, требующее перезапуска базы данных.

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

Усечение журнала транзакций

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

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

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

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

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

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

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

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

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

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

Резервное копирование выполнялось раз в сутки и последняя копия была создана ночью с 21-го на 22-е. Сбой происходит вечером 22-го до создания очередной копии. В этом случае нам потребуется последовательно восстановить полную и последнюю разностные копии, при этом данные за последний рабочий день будут утеряны. Если по каким-либо причинам копия от 21-го также окажется повреждена, то мы можем восстановить предыдущую копию, потеряв еще день работы, в тоже время повреждение копии за 20-е число никак не помешает успешно восстановить данные на вечер 21-го, при наличии соответствующей копии.

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

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

Процесс восстановления в этом случае будет более сложен. Прежде всего потребуется создать вручную резервную копию заключительного фрагмента журнала (показана красным), т.е. часть журнала с момента прошлого создания копии и до аварии.

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

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

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

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

С другой стороны, если одна из копий лога транзакций будет повреждена, скажем, предпоследняя, то восстановить данные мы сможем только на момент последней резервной копии + период в неповрежденной цепочке копий журналов. Например, если журналы делались в 12, 14 и 16 часов и поврежден журнал, созданный в 14 часов, то располагая суточной копией мы сможем восстановить базу до момента окончания непрерывной цепочки, т.е. до 12 часов.

26.02.2020

SQL Server

комментариев 12

В этой статье мы рассмотрим, как настроить резервное копирование баз данных в 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

Этот вид бекапа не может служить “базой” для дифференциальных резервных копий и для копий журнала транзакций. Copy-only бекап не нарушает текущую цепочку резервных копий (полный-> дифференциальный или полный -> копии журналов транзакций) и используется только в том случае, если вам нужно снять полную резервную копию, не задевая текущую цепочку бекапов.

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

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

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

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

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

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

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

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

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

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

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

Доставка журналов транзакций
Always On
Point-In-Time восстановление
Резервные копии журнала транзакций

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

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

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

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

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

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

SELECT INTO
BULK INSERT и BCP
INSERT INTO SELECT
Операции с индексами (CREATE INDEX, ALTER INDEX REBUILD, DROP INDEX)

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

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

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

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

Полная резервная копия каждые 24 часа
Копия журнала транзакций – каждые 30 минут

В SSMS (SQL Server Management Studio) перейдите в раздел Management -> Maintenance Planes и запустите -> мастер создания плана обслуживания (Maintenance Plan Wizard).

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

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

Back Up Database (Full)
Back Up Database (Transaction Log)

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

Выберите базу данных 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.

Для примера, воспользуемся 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

Читайте также: