Особенности резервного копирования баз данных MS SQL

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

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

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

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

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

Способы создания

В MS SQL Server резервные копии можно создавать несколькими способами. Рассмотрим используемые инструменты:

MS SQL Management Studio

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

Для этого необходимо:

1. Открыть MS SQL Management Studio. Выбрать БД, которая будет копироваться и кликнуть по ней правой кнопкой мыши. Выбрать Задачи, после чего – Создать резервную копию.
2. Откроется окошко, в котором необходимо оставить полный тип копий и прописать путь к резервному файлу. Если возникла необходимость – путь можно изменить, удалить, создать новый. Файл можно сохранить как на локальном диске, так и на сетевом.
3. После успешного окончания процесса появится уведомление об этом.

Командная строка (sqlcmd)

Создание бэкапов с помощью командной строки sqlcmd используется для автоматизированного копирования любых данных. Может применяться в Windows и Linux.

Для данного способа понадобится утилита sqlcmd:

sqlcmd -S <server> -U <user> -P <password> -Q "BACKUP DATABASE [<database>] TO DISK = N'<file path>' <options>"

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

PowerShell

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

Для бэкапа необходимо импортировать модуль import-module sqlps –DisableNameChecking.

Можно воспользоваться синтаксисом: Backup-SqlDatabase -ServerInstance <имя SQL сервера> -Database <имя базы> -BackupFile <путь к файлу с резервной копией>

Как и в предыдущем способе, для запуска скрипта по графику, его следует размещать в планировщике.

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

В Microsoft SQL Server принята практика разных типов резервного копирования. Пользователям доступно:

• полное – делается резервная копия всей БД. Может выполняться различными способами;
• дифференциальное или разностное – осуществляется копирование данных с того момента, когда осуществлялось ее последнее полное резервирование. Осуществляется специальной командой с добавлением опции DIFFERENTIAL;
• логов или инкрементальное.

Рассмотрим их подробнее.

Полное копирование (Full Backup)

В SQL Server под полным копированием (Full Backup) понимают создание полной резервной БД, включая все данные и объекты системных таблиц. Полный бэкап не усекает (truncate) журнала транзакций. Она является основным типом создания бэкапов, которое должно предшествовать любому типами резервирования.

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

Для полного копирования данных действует ряд ограничений:

• Если оно производилось поздними версиями SQL Server, то такие копии невозможно восстановить в более ранних версиях.
• В транзакциях, как в явных, так и неявных, инструкция BACKUP недопустима.

Советы специалистов:

• С ростом объемов БД полный бэкап может требовать не только большего времени на выполнение операции, но и увеличения места на дисковом пространстве. Потому для больших баз данных рекомендуется проводить не только полное, но и дифференциальное копирование. Но стоит помнить, что прежде чем запускать его либо начинать формирование резервных копий журнала транзакций, следует осуществить хотя бы один полный бэкап данных.
• Системная хранимая процедура sp_spaceused поможет узнать размеры полного бэкапа базы данных.
• Все операции копирования, завершившиеся удачно, заканчиваются добавлением записи в журналы: системных событий и ошибок служб SQL Server. Частое формирование таких копий ведет к накапливанию подобных записей. Это чревато увеличением размеров журналов и затрудняет поиск иных сообщений. Потому, в тех случаях, когда записи журналов не влияют на эффективность скриптов, их можно вообще отключить. Для этого следует воспользоваться флажком трассировки 3226.

Вопросы безопасности:

• Свойству TRUSTWORTHY в процессе создания резервной копии базы будет присвоено значение OFF.
• В версиях, начиная с SQL Server 2012 (11.x) и более поздних, не поддерживаются параметры PASSWORD и MEDIAPASSWORD. Но восстановление копий с паролями доступно.

Для пользователей, имеющих роли sysadmin, db_owner, db_backupoperator, по умолчанию доступны BACKUP DATABASE и BACKUP LOG. Помешать выполнению резервирования могут проблемы как с владельцем, так и с разрешениями у физических файлов на устройстве.

Полное копирование в SSMS

Создавая задачу в MSSQL Server Management Studio, можно сформировать соответствующий скрипт T-SQL BACKUP. Для этого следует нажать кнопку Скрипт, а затем указать его назначение.

1. Когда в SQL Server компонент Database Engine подключится к нужному экземпляру Microsoft, следует развернуть в обозревателе дерево сервера.
2. Если нужна пользовательская база данных, то следует развернуть узел База данных. Если потребуется обозначить системную БД – развернуть соответствующий узел.
3. Отобрав базу данных, которая будет копироваться, щелкнуть на ней мышкой, выбрать Задачи и затем команду Создать резервную копию… .
4. В появившемся окошке отобранная БД будет приведена в виде раскрывающегося списка. При необходимости, она может быть изменена на любую другую базу данных.
5. В раскрывшемся списке Тип резервной копии следует выбрать необходимый вариант. По умолчанию выбран тип Полная.
6. В разделе Компонент резервного копирования выбрать База данных.
7. Произвести проверку, куда сохраняются файлы по умолчанию в разделе Назначение (в папке ../mssql/data).

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

Для просмотра содержимого, созданного ранее целевого объекта копирования, его необходимо выделить и кликнуть Содержимое. А для удаления такого объекта – кликнуть Удалить.

• При необходимости следует проверить все параметры, которые доступны на страницах параметров: носителя и резервного копирования.
• Кликните ОК для запуска процесса
• При успешном завершении бэкапа необходимо закрыть появившееся диалоговое окно, кликнув ОК.

Когда полный бэкап БД сделан, можно выполнять иные типы резервирования: дифференциальные и журналов транзакций. Когда резервное копирование выполняется на url-адрес на странице Параметры носителя, недоступным будет параметр Перезаписать носитель.

Полное копирование с помощью T-SQL

Для выполнения полного бэкапа базы данных с помощью Transact-SQL, необходимо выполнить соответствующие инструкции BACKUP DATABASE и указать:

• наименование БД для формирования копии;
• устройство, куда будут сохраняться скопированные данные.

Базовая структура синтаксиса T-SQL для создания полной копии БД выглядит следующим образом:

BACKUP DATABASE database TO backup_device [ , …n ] [WITH with_options [ , …o ] ] ;

Расшифровка:

• database – база данных, которая будет копироваться;
• backup_device [ , …n ] – показывает список устройств, которые будут использованы для резервирования. Может указываться как физическое (когда используются параметры DISK/ TAPE), так и логическое устройство (если оно определено);
• [WITH with_options [ , …o ] ] – указывает необходимое число параметров, o.

При необходимости следует указать нужное количество параметров WITH.

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

Для форматирования носителя копии используется параметр FORMAT в следующем предложении:

FORMAT [ , MEDIANAME = { media_name | @ media_name_variable } ] [ , MEDIADESCRIPTION = { text | @ text_variable } ]

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

Однако при использовании в инструкции BACKUP предложения FORMAT следует проявить крайнюю осторожность. Неправильное использование этого предложения удалит все наборы, которые ранее были созданы и хранились на носителе.

Полное копирование с помощью PowerShell

Чтобы создать полный бэкап в PowerShell, следует использовать командлет (упрощенную команду) Backup-SqlDatabase. Чтобы отметить, что эта резервная копия является полной, необходимо задать параметр BackupAction вместе с Database (оно применяется по умолчанию). Этот параметр не относится к обязательным при полном резервировании БД.

Для использования PowerShell понадобится специальный модуль SqlServer. Выполнение команды Get-Module-Name SqlServer поможет выяснить – установлен модуль либо нет. Для установки модуля понадобится в сеансе PowerShell на правах администратора выполнить команду Install-Module-Name SqlServer.

Открывая PowerShell из SSMS, чтобы подключиться к SQL Server, не потребуются учетных данных. В них нет необходимости, т.к. для подключения между PowerShell и SQL Server используются учетные данные пользователя в SQL Server Management Studio.

Дифференциальное (разностное) копирование

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

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

Советы по использованию дифференциальных бэкапов базы данных:

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

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

• полное осуществляется раз в определенное количество дней;
• разностное – каждый раз спустя определенное количество часов.

В том случае, когда оборот данных в день довольно высок, вышеприведенный план окажется неприменим, т.к. его реализация будет занимать много места на дисковом пространстве. К примеру, если вес полного бэкапа составляет 280 GB, а дифференциального, спустя 60 минут, – 4 GB, то по истечению суток вес дифференциальных наборов уже составит 96 GB. Потому, в приведенном примере использование дифференциального резервирования не оправданно.

Другие виды

Кроме вышеперечисленных, существуют иные виды копирования:

• При резервном копировании журнала транзакций выполняется копирование всех транзакций, которые имели место после выполнения предыдущего резервного копирования. После этого происходит урезание журнала, для освобождения дисковое пространство. Этот вид копирования относится к инкрементальному. Для полной модели восстановления необходима вся последовательность резервных наборов, начиная с полной.
• Бэкап Tail-Log, хоть и выделен отдельно, по сути является простой операцией, описанной в предыдущем пункте с опцией NORECOVERY. Его следует выполнять накануне восстановления копии журнала транзакций. Это позволит не упустить те транзакции, которые происходили после крайнего копирования до текущего момента.
• Бэкап Copy-only (создание резервной копии только для копирования) не является базовой ни для разностных, ни для копий журнала транзакций. Создание Copy-only применяется лишь когда необходимо снять полную резервную копию, не задев текущей цепочки резервных копий. Именно эти нюансы и отличают Copy-only от стандартного полного бэкапа данных.
• Частичное резервное копирование Partial backup применяется не часто. Его основная функция – копирование групп файлов read-only.
• Также применяется резервное копирование отдельных файлов и или их групп.

Срок действия

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

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

Путь расположения

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

1. На корневом разделе SQL Server кликнуть правой кнопкой мышки и выбрать Свойства.
2. Перейти к разделу Параметры баз данных. В нем опуститься к подразделу Места хранения, используемые базой данных по умолчанию. Именно здесь можно увидеть прописанный путь к месту размещения резервных наборов. Его можно изменить, воспользовавшись кнопкой (…), расположенной справа.

Общие советы

1. Чтобы проверить успешность бэкапа, можно воспользоваться опцией проверки контрольной суммы BACKUP WITH CHECKSUM. Но для больших баз данных применение опции проблематично, т.к. она способна сильно загрузить систему.
2. Бэкапы не следует выполнять на физические диски, где хранятся данные либо журнал транзакций.
3. При использовании версий MS SQL Server 2008 и выше, рекомендуется выполнять сжатие копий БД средствами SQL.
4. Копии лучше сохранять несколько дней. Если какая-нибудь окажется поврежденной, наличие старой будет как нельзя кстати. По истечении этого времени ненужные файлы с расширением bak следует удалить.
5. Перед выполнением копирования рекомендуется проверять базы, воспользовавшись DBCC CHECKDB. Это позволит своевременно получить сообщение о возникновении проблем.
6. Периодически следует обновлять статистику и реорганизацию индексов БД.

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

Также приглашаем на специальный курс по MS SQL в Otus.