Резервное копирование и восстановление базы данных в 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 Server) Backup & restore: system databases (SQL Server)

Применимо к: Applies to: SQL Server SQL Server (все поддерживаемые версии) SQL Server SQL Server (all supported versions) Применимо к: Applies to: SQL Server SQL Server (все поддерживаемые версии) SQL Server SQL Server (all supported versions)

SQL Server SQL Server поддерживает набор баз данных системного уровня, которые называются системными базами данных и жизненно важны для работы экземпляра сервера. maintains a set of system-level databases, s ystem databases, which are essential for the operation of a server instance. После каждого значительного обновления необходимо обязательно создавать резервные копии ряда системных баз данных: Several of the system databases must be backed up after every significant update. msdb, master и model. The system databases that you must always back up include msdb, master, and model. Если какая-нибудь из баз данных на экземпляре сервера использует репликацию, то необходимо также создавать резервную копию системной базы данных distribution . If any database uses replication on the server instance, there is a distribution system database that you must also back up. Резервные копии системных баз данных позволят восстановить систему SQL Server SQL Server в случае сбоя, например отказа жесткого диска. Backups of these system databases let you restore and recover the SQL Server SQL Server system in the event of system failure, such as the loss of a hard disk.

В следующей таблице перечислены все системные базы данных. The following table summarizes all of the system databases.

Системная база данных System database	Описание Description	Необходимость создавать резервные копии Are backups required?	Модель восстановления Recovery model	Комментарии Comments
master master	База данных, в которой хранятся все системные данные SQL Server SQL Server . The database that records all of the system level information for a SQL Server SQL Server system.	Да Yes	Простая Simple	Создавайте резервные копии базы данных master с такой частотой, которая необходима для адекватной защиты данных. Back up master as often as necessary to protect the data sufficiently for your business needs. Рекомендуем составить расписание регулярного резервного копирования, которое можно дополнить созданием резервных копий после значительных обновлений. We recommend a regular backup schedule, which you can supplement with an additional backup after a substantial update.
model model	Шаблон для всех баз данных, создаваемых на экземпляре SQL Server SQL Server . The template for all databases that are created on the instance of SQL Server SQL Server .	Да Yes	Настраиваемая пользователем (0-9) User configurable*	Резервные копии базы данных model создаются только в том случае, если они необходимы для предприятия (например сразу же после настройки параметров базы данных). Back up model only when necessary for your business needs; for example, immediately after customizing its database options. Рекомендация. Рекомендуется по мере необходимости создавать только полные резервные копии базы данных model. Best practice: We recommend that you create only full database backups of model, as required. Поскольку база данных model невелика и редко изменяется, создавать резервную копию журнала не обязательно. Because model is small and rarely changes, backing up the log is unnecessary.
msdb msdb	База данных, используемая агентом SQL Server SQL Server для планирования предупреждений и заданий и для записи операторов. The database used by SQL Server SQL Server Agent for scheduling alerts and jobs, and for recording operators. База данных msdb также содержит такие таблицы журнала, как таблицы резервных копий и журнала восстановления. msdb also contains history tables such as the backup and restore history tables.	Да Yes	Простая (по умолчанию) Simple (default)	Создавайте резервную копию базы данных msdb после каждого ее обновления. Back up msdb whenever it is updated.
Resource (RDB) Resource (RDB)	База данных только для чтения, которая содержит копии всех системных объектов, поставляемых с SQL Server SQL Server A read-only database that contains copies of all system objects that ship with SQL Server SQL Server	нет No	—	База данных Resource находится в файле mssqlsystemresource.mdf, в котором содержится только код. The Resource database resides in the mssqlsystemresource.mdf file, which contains only code. Поэтому SQL Server SQL Server не может создать резервную копию базы данных Resource . Therefore, SQL Server SQL Server cannot back up the Resource database. Примечание. Рассматривая файл mssqlsystemresource.mdf как двоичный файл (EXE), а не файл базы данных, для создания его резервной копии можно выполнить простое резервное копирование файла или диска. Note: You can perform a file-based or a disk-based backup on the mssqlsystemresource.mdf file by treating the file as if it were a binary (.exe) file, instead of a database file. Нельзя использовать восстановление SQL Server SQL Server для резервных копий. But you cannot use SQL Server SQL Server restore on the backups. Восстановить резервную копию файла mssqlsystemresource.mdf можно будет только вручную; при этом следует соблюдать осторожность, чтобы не перезаписать текущую базу данных Resource устаревшей или потенциально небезопасной версией. Restoring a backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version.
tempdb tempdb	Рабочая область для хранения временных или промежуточных результирующих наборов. A workspace for holding temporary or intermediate result sets. Эта база данных создается заново при каждом запуске экземпляра SQL Server SQL Server . This database is re-created every time an instance of SQL Server SQL Server is started. При отключении экземпляра сервера любые сведения, содержащиеся в базе данных tempdb , удаляются навсегда. When the server instance is shut down, any data in tempdb is deleted permanently.	нет No	Простая Simple	Создать резервную копию системной базы данных tempdb нельзя. You cannot back up the tempdb system database.
Настройка распространения Configure Distribution	База данных, которая существует только в том случае, если сервер настроен как распространитель репликации. A database that exists only if the server is configured as a replication Distributor. Эта база данных содержит метаданные и данные журнала для всех типов репликации, а также транзакции для репликации транзакций. This database stores metadata and history data for all types of replication, and transactions for transactional replication.	Да Yes	Простая Simple	Сведения о том, когда следует создавать резервные копии базы данных distribution , см. в статье Создание резервной копии и восстановление из копий реплицируемых баз данных. For information about when to back up the distribution database, see Back Up and Restore Replicated Databases.

Ограничения восстановления системных баз данных Limitations on Restoring System Databases

Системные базы данных могут быть восстановлены только из резервных копий, созданных той версией SQL Server SQL Server , которая запущена на данном экземпляре сервера. System databases can be restored only from backups that are created on the version of SQL Server SQL Server that the server instance is currently running. Например, чтобы восстановить системную базу данных на экземпляре сервера, работающего под SQL Server 2012 (11.x) SQL Server 2012 (11.x) с пакетом обновления 1 (SP1), необходимо использовать резервную копию базы данных, созданную после обновления экземпляра сервера до SQL Server 2012 (11.x) SQL Server 2012 (11.x) с пакетом обновления 1 (SP1). For example, to restore a system database on a server instance that is running on SQL Server 2012 (11.x) SQL Server 2012 (11.x) SP1, you must use a database backup that was created after the server instance was upgraded to SQL Server 2012 (11.x) SQL Server 2012 (11.x) SP1.

Для восстановления любой базы данных должен быть запущен экземпляр SQL Server SQL Server . To restore any database, the instance of SQL Server SQL Server must be running. Для запуска экземпляра SQL Server SQL Server необходимо, чтобы база данных master была доступна и хотя бы частично пригодна к использованию. Startup of an instance of SQL Server SQL Server requires that the master database is accessible and at least partly usable. Если база данных master непригодна к использованию, ее можно вернуть в нормальное состояние следующими способами. If master becomes unusable, you can return the database to a usable state in either of the following ways:

Восстановить базу данных master на основе актуальной резервной копии. Restore master from a current database backup.

Если экземпляр сервера удалось запустить, базу данных master можно восстановить из полной резервной копии. If you can start the server instance, you should be able to restore master from a full database backup.

Перестроить базу данных master с нуля. Rebuild master completely.

Если серьезное повреждение базы данных master не позволяет запустить экземпляр SQL Server SQL Server , базу данных master нужно перестроить. If severe damage to master prevents you from starting SQL Server SQL Server , you must rebuild master. Дополнительные сведения см. в разделе Перестроение системных баз данных. For more information, see Rebuild System Databases.

При перестроении базы данных master все системные базы данных также перестраиваются. Rebuilding master rebuilds all of the system databases.

В некоторых случаях для проблем, связанных с восстановлением табличного шаблона базы данных модели, может потребоваться перестроение системных баз данных или замена MDF- и LDF-файлов базы данных модели. Under some circumstances, problems recovering the model database may require rebuilding the system databases or replacing the mdf and ldf files for the model database. Дополнительные сведения см. в разделе Перестроение системных баз данных. For more information, see Rebuild System Databases.