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

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

Резервное копирование — это процесс создания копии данных на внешнем носителе. Оно предназначено для последующего восстановления данных в случае их повреждения или разрушения.

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

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

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

Особенности резервного копирования следующие:

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

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

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

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

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

Есть несколько способов создания резервной копии MS SQL. Для разовых операций подойдет графический инструмент Microsoft SQL Management Studio. Для автоматизации — Powershell или cmd. Данные операции применяются к любым базам, как для 1С, так и любых других приложений.

С помощью графического интерфейса

Открываем MS SQL Management Studio. Кликаем правой кнопкой мыши по базе, для которой хотим сделать резервную копию -   Задачи  -  Создать резервную копию :

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

После завершения процесса мы увидим сообщение «Резервное копирование базы ... успешно завершено».

С помощью командной строки (cmd)

Данный способ удобно использовать для автоматизации резервного копирования. Более того, команды подходят как для Windows, так и Linux. Выполняется при помощи утилиты sqlcmd.

Синтаксис:

• В данном примере мы подключаемся к локальному SQL серверу под учетной записью  sa  с паролем   my_pass   и делаем резервную копию баз   work1  и  work2 . Резервные копии размещаем по пути  D:\Backup\MSSQL . Имя файлов резервных копий  work1_.bak  и  work2_.bak
• Некоторые опции могут не работать, в зависимости от используемой версии Microsoft SQL Server.
• Для автоматизации скрипта необходимо создать задание в планировщике, чтобы скрипт запускался по расписанию.

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

Хорошей практикой является создание разных типов копий:

• Полное копирование — резервирование всей базы. Выполняется командой, рассмотренной выше, например:

В примере идет подключение к локальному серверу под пользователем  sa  с паролем  my_pass   и делается полная копия базы  work1 ; сама копия сохраняется в виде файла  D:\Backup\MSSQL\bak_full.bak .

2 . Разностное (дифференциальное) — резервирование базы данных с момента создания последней полной копии. Выполняется командой для резервного копирования с добавлением опции  DIFFERENTIAL :

3.Инкрементальное или копирование логов. Выполняется Transact-SQL:

3. С помощью Powershell

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

Для выполнения команды, сначала импортируем модуль:

Также как и для cmd, данный скрипт можно поместить в планировщик для запуска по расписанию.

Срок действия резервного набора данных

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

Задать параметр можно в основном окне при создании резервной копии :

Путь расположения резервных копий

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

Кликаем правой кнопкой мыши по корневому разделу Microsoft SQL Server и выбираем свойства:

• Переходим в раздел  Параметры баз данных   (1) - в подразделе «Места хранения, используемые базой данных по умолчанию» мы увидим путь до места размещения резервных копий (2), который можно поменять кнопкой справа (3):

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

Полное (Full Backup)

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

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

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

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

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

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

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

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