Резервное копирование SQL 2014

Меня недавно спросили:

«Я использую восстановление базы данных с опцией WITH STATS, которая показывает процент восстановления базы данных, но когда процент восстановления доходит до 100%, то процесс может ещё долго висеть на 100%, что происходит в этот момент?»

Я не смог сразу ответить на этот вопрос, поэтому решил провести исследование. Первым делом я настроил extendev events (XEvent) и запустил восстановление БД WideWorldImporters sample database. После восстановления я получил следующий вывод в SSMS:

Transact-SQL

Когда я посмотрел на события в XEvent, то увидел следующее:

После восстановления БД до 100% есть ещё несколько событий. Давайте рассмотрим этапы восстановления БД и их влияние на время восстановления.

Эта восстановления БД

1. Создание файла БД
2. Копирование всех страниц данных из backup
3. Создание файла логов
4. Копирование блоков данных в файл лога из backup
5. Запуск Recovery БД

Сделаю предположение, что большинство людей использует Instant File Initialization, поэтому не будем останавливаться на 1 шаге, так как с этой возможностью создание файла на файловой системе происходит быстро.

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

Я решил проверить моё предположение с помощью доработки XEvent и увеличения размера БД до 10 Гб файла данных и 5 Гб файл лога (реальных данных всего 13 Мб). После запуска нового восстановления я получил следующий результат:

Обратите внимание, что процент восстановление указывает количество байт, которое было восстановлено и когда восстановление дошло до 100% мы получили наши 13 Мб (реальный размер данных). Но самое важно для нас — это шаги «Waiting for Log zeroing» и «Log Zeroing is complete”. Если посмотреть внимательно, то мы обнаружим, что эти шаги заняли значительно больше времени (около 2-х минут), чем процесс восстановления, выраженный в процентах (секунды).

Из данного эксперимента можно сделать несколько выводов:

1. Заполнение файла лога нулями и есть та причина, по которой восстановление так долго висит на 100%
2. Проценты восстановления показывают только восстановленный объём файла данных.

Надеюсь раскрытие этой загадки SQL Server было вам интересно.

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

Что угрожает данным на сервере

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

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

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

Как правильно сделать бэкап

В первую очередь важно настроить автоматический бэкап. Актуальность данных имеет определяющее значение. Задача – восстановить утраченные данные от вчерашнего дня, а не полугодичной давности. Каждый бэкап нужно хранить минимум 14 дней. Это тот срок, на протяжении которого у каждого сотрудника будет возможность перепроверить все и установить, что испортилось или пропало. Наиболее предпочтительный способ – дисковые хранилища. Они отличаются высокой скоростью работы и надежностью. Но важно учитывать, что подобные свойства характерны при наличии RAID.

В идеале стоит организовать удаленную площадку, куда будут попадать резервные копии. Абсолютно любой бизнес на каждом шагу поджидают неприятные сюрпризы. Наличие копий на удаленной площадке – спокойствие и дополнительная мера предосторожности. Поскольку резервное копирование серверов – это всегда существенная нагрузка, планировать бэкап необходимо на время минимальной активности с целью предотвращения просадок производительности основной системы. Стоит учитывать, что большой поток данных сильно нагружает сеть. Копирование данных возможно не по LAN, а по SAN. Будет разумным решением настроить передачу данных между СХД и библиотекой напрямую.

Установка Windows Server backup

Чтобы установить средство для бэкапа Windows Server backup, необходимо выполнить следующие простые действия:

1. Открываем «Диспетчер устройств».
2. Выбираем «Установка ролей и компонентов», жмем «Далее».
3. Устанавливаем переключатель в положении «Выбор сервера из пула серверов».
4. Выбираем сервер, на который будет установлено средство бэкапа.
5. Жмем на вкладку «Выбор ролей сервера» и жмем «Далее»,
6. Затем «Система архивации данных Windows Server» и снова «Далее».
7. Нажимаем «Установить» на вкладке «Подтверждение установки компонентов».
8. Дожидаемся завершения процесса инициализации.
9. Выходим из окна мастера установки.

Так были установлены необходимые компоненты для резервного копирования.

Как восстановить данные из резервной копии?

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

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

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

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

Другие полезные советы

Всегда старайтесь избавляться от дублей. На множествах машин хранится одна и та же информация, нередко дублируются, к примеру, почтовые рассылки – письма и вложения. Снэпшоты никогда не помешают. Такой инструмент, как VMware ESXi предоставляет в личное распоряжение функционал, создающий образы виртуальных машин без прекращения их функционирования прямо на лету. Такой образ бэкапится в виде одного файла, но есть программы с более обширным и разнообразным набором инструментов. Например, продукты от Symantec предоставляют пользователя продвинутые возможности. Они позволяют с установленной периодичностью восстанавливать определенный объект. Это может быть письмо электронной почты, что особенно удобно и полезно, если внутри виртуалки работал почтовый сервер.

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

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

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

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

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

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

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

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

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

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

• Резервные копии не должны храниться на том же диске, что и ваш SQL Server. Это правило касается любых резервных копий. При выходе из строя основного дискового массива вы должны иметь доступ к вашим резервным копиям. Если позволяют ресурсы, лучше хранить резервные копии сразу на нескольких разрозненных массивах.
• Процесс резервного копирования должен минимально влиять на работу пользователей. Полные резервные копии лучше делать тогда, когда пользовательская активность на сервере минимальна.
• Регулярно проверяйте целостность резервных копий и проводите тестовые восстановления. Вы всегда должны быть уверены, что ваши бекапы валидны и готовы к восстановлению в любое время.
• Заранее рассчитайте время, необходимое для полного восстановления при аварии. Часто в базах хранится критически важная для бизнеса информация, поэтому ваш руководитель должен знать минимальное время, которое потребуется для восстановления после аварии. Если даже вас об этом не спрашивают, лучше заранее уведомить об этом, чтобы в случае аварии не возникло недопонимания.

Самородов Федор Анатольевич: Как ускорить восстановление больших баз данных в SQL Server

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

А что если я скажу, что время восстановления БД можно сократить более чем вдвое при помощи одной простейшей опции?

Давайте проведём эксперимент. Вот база данных размером 100 ГБ, под завязку заполненная данными.

Все данные этой базы лежат в единственном файле.

Я сделаю резервную копию этой базы и затем восстановлю базу из резервной копии. И резервное копирование и восстановление я провожу самым обыкновенным способом без каких-либо хитроумных настроек.

А теперь посмотрите внимательно на выдачу инструкции RESTORE DATABASE:

Сама команда восстановления сообщает, что она выполнялась 3331 секунду — это примерно 55 минут. Но с точки зрения внешнего наблюдателя восстановление базы заняло больше двух часов! Получается, что 55 минут сервер занимался непосредственно восстановлением БД, а ещё час двадцать занимался неизвестно чем.

На самом деле это время сервер потратил на выделение места под восстанавливаемую базу. Сложно поверить, но это заняло в полтора раза больше времени, чем полезный процесс. Вас устраивает, что КПД дисковой подсистемы при восстановлении всего 40%?

Если нет, то давайте зайдём в локальную политику безопасности и предоставим учётной записи, под которой работает SQL Server разрешение «выполнение задач по обслуживанию томов» (в английской версии — «perform volume maintenance tasks»). Чтобы это подействовало необходимо перезапустить службу SQL Server. Теперь сервер сможет гораздо быстрее выделять место на диске для резервных копий, восстановления баз, а также при расширении файлов баз и журналов.

Повторим эксперимент с восстановлением стогигабайтной базы:

Теперь база действительно восстанавливается за 55 минут — это почти в два с половиной раза быстрее, чем при настройках по умолчанию!

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

Подробнее об этом Вы сможете узнать на курсах SQL Server

MS SQL Express лишен агента, при помощи которого можно выполнять задачи по расписанию, но можно воспользоваться и стандартными средствами Windows.

Очень часто для небольших проектов хватает и Express версии SQL сервера. Одна из проблем –это то, что у Express версии нет SQL Agent службы, при помощи которой можно делать некоторые задачи по расписанию. Взамен можно использовать SQLCMD и стандартный Scheduled Tasks от Windows. Первое, что нужно сделать, это написать скрипт, который создавал бы для нас необходимые бекапы. Для его генерации можно воспользоваться MS Management Studio (ее тоже можно скачать для Express версии) и на окне создания бекапа нажать не OK, а «Script Actions to …”.

Я же использую обычно в таких задачах следующий скрипт:

DECLARE @pathName NVARCHAR(512) SET @pathName = ‘D:\Backup\db_backup_’ + Convert(varchar(8), GETDATE(), 112) + ‘.bak’ BACKUP DATABASE TO DISK = @pathName WITH NOFORMAT, NOINIT, NAME = N’db_backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

Этот скрипт создает бекап с именем файла db_backup_YYYYDDMM.bak где YYYYDDMM – это текущая дата. Дата в имени файла позволит нам создавать каждый день бекап в новом файле. Запустите и проверьте что бекап действительно создается такой какой вам и нужен. Этот скрипт сохраняем в какой-нибудь папке под именем schedule.sql, предположим c:\sheduled tasks\. В этой же папке создадим исполняемый файл backup.bat, следующего содержания:

sqlcmd -S SEVERNAME -U UserName -P Password -i schedule.sql 7z a -tzip D:\Backup \db_backup_%date%.zip -i! D:\Backup\db_backup_*.bak del d:\Backup\db_backup_*.bak

Где меняем SERVERNAME – имя сервера, UserName – имя пользователя, Password – пароль пользователя, schedule.sql – имя сохраненного скрипта. Вторая и третья строка батника архивирует бекап в zip файл и удаляет сам файл бекапа. Для того чтобы работала архивация необходимо установить архиватор 7z и прописать полные пути до исполняемого файла 7z.exe либо положить 7z.exe и 7z.dll в ту же папку, где располагаются скрипты. Теперь можем запустить исполняемый файл backup.bat и проверить проработает ли он так как нужно. Последний шаг это записать schedule в задачи windows. Запускаем Task Scheduler из меню Пуск, либо набираем в командной строке taskschd.msc. В разных версиях Windows это выглядит по разному, да и информацию о том как сделать задачу можно прочитать в помощи Windows. Основное – это запускать задачу от имени пользователя с достаточными правами на используемые папки. При помощи таких действий можно так же запрограммировать и любые другие задачи. В скрипте schedule.sql можно перед бекапом вызвать какие-либо необходимые процедуры, может переиндексирование или сжатие базы данных.