Настроить резервное копирование базы sql

Резервное копирование и восстановление базы данных в 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

Этот вид бекапа не может служить “базой” для дифференциальных резервных копий и для копий журнала транзакций. 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 скрипт, который выполняет ту же последовательность действия для восстановления базы данных, что и мастер (скрипт был сгенерирован мастером из примера выше).

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-Ex blog

Новости сайта «Упражнения SQL», статьи и переводы

5 способов сделать резервные копии в SQL Server

В прошлый раз мы обсуждали 5 типов резервных копий. Сейчас я хочу представить вам пять способов сделать бэкап в SQL Server. Я не смогу продемонстрировать все доступные опции каждого из этих шести методов. Здесь много чего есть даже для такой простой темы как бэкапы.

Метод 1: Использование графического интерфейса в SSMS для создания бэкапа

Для тех, кому нравится SSMS GUI, есть хорошие новости. SQL Server Management Studio предоставляет довольно простой метод для резервирования базы данных. Подключившись к экземпляру SQL Server, содержащего базу данных, для которой вы хотите сделать бэкап, выполните щелчок левой кнопкой на значке «+» рядом с папкой Databases. Затем щелчок правой кнопкой на имени базы данных, которую вы хотите резервировать, выбрать Tasks, затем выбрать Backup в выпадающем меню.

Вы попадете на страницу General Backup Menu page в SSMS. Здесь вы можете получить доступ к множеству настроек, относящихся к создаваемому бэкапу.

В выпадающем списке “Backup type” вы можете выбрать тип создаваемого бэкапа — полный, дифференциальный или журнала.

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

В разделе Destination (назначение) вы выбираете, где будет создан бэкап — диск (по умолчанию) или, если выбрать из списка “URL”, то на Azure. При выборе Disk вам предлагается место и имя для бэкапа. Этим местом будет каталог по умолчанию для бэкапов, указанный при установке SQL Server. Если вас не устраивает это место, просто нажмите “Remove” (удалить), а потом “Add” (добавить) для выбора места, которое вы хотите использовать. В меню “Add” можно использовать общие пути.

Раздел Media Options на “Select a Page” позволяет выбрать такие варианты, как хотите ли вы добавить этот бэкап к имеющемуся набору или начать заново.

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

В разделе Reliability (надежность) вы можете установить опции “Verify backup when finished” (проверить бэкап по завершению) и “Perform checksum before writing to media.” (посчитать контрольную сумму перед записью на носитель). Эти опции увеличат время создания бэкапа, но помогут с проверкой его целостности по время записи.

В Backup Options меню “Select a page” есть одна очень важная особенность, которую следует отметить.

Здесь имеется опция, связанная со сжатием бэкапа. В более старых версиях SQL Server, например, 2005 и 2008 эта опция была доступна только для Enterprise Edition. Начиная с SQL Server 2008R2, она доступна в Standard Edition. Чтобы сделать использование сжатия по умолчанию для всех ваших бэкапов, просто выполните нижеприведенный код на вашем SQL Server. Затем, когда вы перейдете к этой опции в графическом интерфейсе SSMS, просто оставьте её установленной в “Use the default server setting.” Вам захочется сэкономить пространство, которое предлагает сжатие. Зачем использовать больше пространства на вашем отдельном хранилище бэкапов, чем это необходимо? Я имею в виду, что вы храните свои резервные копии где-то еще, а не на SQL Server, верно?!

Установив необходимые опции, просто щелкните «ОК», и SQL Server сделает вам бэкап. Вы можете также щелкнуть опцию “Script” наверху окна мастера, чтобы SQL Server показал код T-SQL, который будет исполнен. Вы сможете сохранить его в качестве примера для дальнейшего использования.

Метод 2: Использование T-SQL для создания резервной копии на SQL Server

T-SQL — проверенный и надежный метод резервного копирования баз данных. При использовании T-SQL доступно больше опций для создания бэкапов, чем при использовании графического интерфейса. Большинство этих опций являются более продвинутыми. Очень базовый пример команды backup, которая создает полную резервную копию, представлен ниже. Затем следуют примеры дифференциального бэкапа и бэкапа журнала.

Стоит отметить два параметра Buffer Count и maxtransfersize. Вы можете поэкспериментировать с этими параметрами T-SQL, чтобы ускорить создание бэкапов. Значение Buffer Count управляет числом буферов ввода/вывода, которые используются для обработки бэкапа, а maxtransfersize отвечает за то, сколько данных перемещается за один раз.

Ниже я предоставил 3 примера моих тестов, выполненных на домашнем ПК. Исходные данные buffercount и maxtransfersize были получены с помощью установки флагов 3605 и 3213 с последующим обращением к журналу ошибок после выполнения первого бэкапа. После чего я просто экспериментировал со значениями. Имейте в виду, что слишком сильное увеличение числа буферов может вызвать ошибку нехватку памяти.

Как вы можете видеть начальная пропускная способность составляла 219,412Мб/с, а прошедшее время для этой части было 39 секунд. Это были настройки по умолчанию SQL Server.

Увеличение числа буферов до 8 увеличило пропускную способность до 258,653Мб/с, и время выполнения упало примерно на 6 секунд. Сочетание второго изменения с размером maxtransfersize 4Мб увеличило пропускную способность до 270,095 и еще сократило время на 1,4 секунды. Я скинул 8 секунд времени бэкапа. Это была небольшая база данных размером около 14Гб. Для бОльших баз данных увеличение пропускной способности может дать значительную экономию времени.

Метод 3: Использование Powershell для создания резервных копий

Если вы не используете Powershell с SQL Server, то это того стоит. Если вы не используете модуль DBATools с SQL Server, получите его сейчас. PowerShell может делать фантастические, чудесные вещи, а DBATools может сделать для вас мощные, удивительные вещи во всем, что связано с SQL Server. Ниже простой пример использования команды DBATools Backup-DbaDatabase для создания полного бэкапа. Эта команда имеет полный набор опций, включая резервирование всех баз данных на SQL Server, если не передавать параметр -Database. Проверьте это прямо сейчас.

Метод 4: Использование планов обслуживания для создания резервных копий

Тут я лишь поделюсь с вами несколькими мыслями относительно использования планов обслуживания. Во-первых, планы обслуживания (Maintenance Plans) представляют собой еще один метод с графическим интерфейсом для настройки резервных копий. В этом отношении они простой способ «указать и щелкнуть» для обработки хранения резервных копий, о чем мы еще не говорили. Во-вторых, в силу природы этого метода, который позволяет вам выбрать Backups как вариант плана, а затем пройти по шагам каждую часть мастера процесса, Maintenance Plans может стать общим подходом для ИТ-профессионалов, вышедших из системных администраторов. Например, нет необходимости знать или понимать опции, представленные в мастере SSMS Backup.

До версии SQL Server 2016 бэкапам в рамках планов обслуживания был присущ ряд недостатков. А именно, отсутствие нижеперечисленных опций.

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

Источник