Резервное копирование и восстановление базы данных в MS SQL Server

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

Для этого можно использовать либо встроенный в SQL Server планировщик заданий – «SQL Server Agent» (в бесплатную версию не входит), либо стандартный «Планировщик Windows» в сочетании с утилитой SQLCMD.EXE, которая позволяет выполнять запросы к SQL Server из командной строки. В планировщике необходимо создать как минимум семь заданий (по одному на каждый день недели), каждое из которых будет (раз в неделю) заменять один из семи файлов, содержащих соответствующую резервную копию базы данных.

Кроме того, файлы резервных копий рекомендуется хранить не только на жестком диске компьютера, где установлен SQL Server, но и дублировать их на ленту или жесткий диск другого компьютера в сети. Для этого можно использовать либо специальное ПО, которое позволяет делать резервные копии всего диска, либо с помощью того же планировщика копировать файлы на ленту или другой компьютер (вторым шагом).

С помощью «Планировщика Windows» (для бесплатной версии)

Чтобы создать задание в «Планировщике Windows» надо:

Запустить программу «Блокнот» (Пуск->Все программы->Стандартные->Блокнот) и ввести следующие две строки, после чего сохранить их в виде командного файла (*.BAT):

где «(local)» – имя сервера (в случае установки именованного экземпляра SQL Server надо указать имя полностью: «ИМЯ_КОМПАSQLEXPRESS»), «AltaSVHDb» – имя базы данных, «D:BACKUP AltaSVHDb_monday.bak» – имя файла для создания в нем резервной копии (будет различаться по дням недели), «BACKUP_SERVER» – имя компьютера, на который будет выполняться дополнительное копирование, «Folder» – папка на этом компьютере (к ней должен быть предоставлен общий доступ).

Запустить мастер планирования заданий (Панель управления->Назначенные задания->Добавить задание) и нажать кнопку «Далее»:

Нажать кнопку «Обзор» и указать путь к командному файлу (*.BAT), созданному на шаге a):

Указать имя для задания, выбрать вариант запуска «еженедельно» и нажать кнопку «Далее»:

Поставить галочку возле нужного дня недели, а в поле «Время начала» указать время, когда должен запускаться процесс резервного копирования (обычно это делается ночью), затем нажать кнопку «Далее»:

Ввести имя пользователя и пароль (дважды) учетной записи ОС, от имени которой будет выполняться задание, и нажать кнопку «Далее»:

Внимание! Чтобы задание успешно выполнялось необходимо предоставить указанной здесь учетной записи (домена или локального компьютера) права записи в вышеупомянутую папку «\BACKUP_SERVERFolder», а также настроить доступ к самому SQL Server.

Нажать кнопку «Готово»

Примечание. Чтобы проверить работоспособность созданного задания, необходимо в списке заданий (Панель управления->Назначенные задания) нажать правой кнопкой мыши на интересующем задании и в контекстном меню выбрать пункт «Выполнить», затем убедиться, что файл резервной копии БД успешно создался по тем путям, которые были указаны на шаге a).

С помощью «SQL Server Agent» (в бесплатную версию не входит)

Чтобы создать задание в «SQL Server Agent» надо:

Запустить утилиту SQL Server Management Studio и подключиться к серверу под учетной записью администратора.

В левой части окна нажать правой кнопкой мыши на разделе «Объекты сервера/Устройства резервного копирования» и в контекстном меню выбрать пункт «Создать устройство резервного копирования»:

В поле «Имя устройства» ввести имя, которое будет ассоциироваться с файлом резервной копии БД, при необходимости изменить путь в поле «Файл» и нажать «ОК»:

В левой части окна нажать правой кнопкой мыши на разделе «Агент SQL Server/Задания» и в контекстном меню выбрать пункт «Создать задание»:

В поле «Имя» ввести имя задания:

На странице «Шаги» нажать кнопку «Создать»:

В появившемся окне ввести имя в поле «Имя шага», проверить, что в поле «Тип» выбрано «Сценарий Transact-SQL (T-SQL)», а в поле «Команда» ввести строку:

BACKUP DATABASE AltaSVHDb TO AltaSVHDb_monday WITH INIT, NOFORMAT, SKIP, NOUNLOAD

где «AltaSVHDb» – имя базы данных, «AltaSVHDb_monday» – имя устройства резервного копирования, созданного на шаге c) (будет различаться по дням недели):

В предыдущем окне нажать кнопку «ОК», в результате на странице «Шаги» должна появиться строка:

Чтобы файл резервной копии БД сразу копировался на другой компьютер в сети необходимо повторить пункты f) – h), в окне «Создание шага задания» выбрав в поле «Тип» значение «Операционная система (CmdExec)», а в поле «Команда» указав строку:

XCOPY D:MSSQLBACKUPAltaSVHDb_monday.bak \BACKUP_SERVERFolder*.* /Y

где «D:MSSQLBACKUPAltaSVHDb_monday.bak» – путь, указанный на шаге c) (будет различаться по дням недели), «BACKUP_SERVER» – имя компьютера, на который будет выполняться копирование, «Folder» – папка на этом компьютере (к ней должен быть предоставлен общий доступ):

Примечание. Чтобы копирование файла успешно выполнялось необходимо запускать «SQL Server Agent» под учетной записью домена Windows, для которой предоставлены права записи в вышеупомянутую папку (см. также «SQL2005_installation.doc» или «SQL2008_installation.doc»), а также настроен доступ к самому SQL Server (см. раздел «Настройка прав доступа к БД», включить эту учетную запись надо в роль «sysadmin» на странице «Серверные роли», а на страницах «Сопоставление пользователей» и «Защищаемые объекты» ничего не делать).

На странице «Расписания» нажать кнопку «Создать»:

Ввести имя в поле «Имя», проверить, что в поле «Тип расписания» выбрано значение «Повторяющееся задание», а в поле «Выполняется» – «Еженедельно». Поставить галочку возле нужного дня недели (остальные снять), а в поле «Однократное задание» указать время, когда должен запускаться процесс резервного копирования (обычно это делается ночью):

В предыдущем окне нажать кнопку «ОК», в результате на странице «Расписания» должна появиться строка:

Нажать кнопку «ОК».

Примечание. Чтобы проверить работоспособность созданного задания, необходимо в разделе «Агент SQL Server/Задания» нажать правой кнопкой мыши на интересующем задании и в контекстном меню выбрать пункт «Запустить задание на шаге», в появившемся окне выбрать первый шаг данного задания и нажать «ОК». Далее появится окно отображающее ход выполнения задания. Если выполнение задания закончится с ошибкой, то подробное описание ошибки можно увидеть вызвав пункт «Просмотр журнала» того же контекстного меню.

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

1. Журнал транзакций

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

1. восстановление отдельных транзакций;
2. восстановление всех незавершенных транзакций при запуске SQL Server;
3. накат восстановленной базы данных, файла, файловой группы или страницы до момента сбоя и т.д

Рекомендации

1. Вынести на быстрый жесткий диск, чтобы при большом потоке операций не было задержек при записи.
2. Необходимо делать резервные копии журнала транзакций не реже чем каждый час.
3. После создания полной (разностной) копии базы данных, все старые журналы можно удалять, т.к. они теряют свою актуальность.
4. Внимательно следите за размером диска на котором хранятся журналы транзакций, если оно закончится, то записать новые данные в БД будет невозможно, пока не произойдет уменьшение размеров журнала транзакций или не добавиться новый дополнительный файл транзакций.
5. Журнал транзакций необходимо регулярно усекать, чтобы избежать его переполнения. UPD: Как сказал данная операция по усечению слегка сомнительна в плане производительности, т.к. при бэкапирование журнал транзакции очищается внутри и СУБД начинает писать в нем по новой. Однако у вас может возникнуть ситуация, которую описал я в своем комментарии и тогда это вам может пригодиться.
6. Возможна ситуация, когда невозможно сразу сделать усечение журнала. Они описаны в данной статье
7. Для получения информации о состоянии базы данных можно с помощью следующего запроса:

   select name,log_reuse_wait, log_reuse_wait_desc  from sys.databases

8. При необходимости можно получить информацию о последних открытых транзакциях

   DBCC OPENTRAN (Имя базы данных) WITH TABLERESULTS 

Пример SQL скрипта для выполнения резервного копирования журнала транзакции с последующим усечением файла.

BACKUP LOG [Имя базы данных] TO DISK = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLMSSQLBackup[Имя файла].bak' WITH NOFORMAT, NOINIT,  NAME = N'Журнал транзакций  Резервное копирование', SKIP, NOREWIND, NOUNLOAD,  STATS = 10 GO USE [Имя базы данных] GO DBCC SHRINKFILE (N'Имя файла лога БД' , 25) GO 

Эти же операции можно проделать с помощью SSMS

2.Разностная копия БД

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

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

Более подробно о рекомендациях по частоте созданию разностных резервных копий, можно прочитать здесь. Приведу небольшой пример из практики, почему мы стали использовать разностную копию. Со временем у нашего клиента разрослась база данных до таких размеров, что создание полной резервной копии занимало 8 часов, еще несколько месяцев и возможно к началу рабочего дня не успевало бы завершиться данная операция. После перевода на разностное резервное копирование, мы сократили время с 8 часов до 2-4 минут (в зависимости от дня недели). Раз в неделю мы делали полную копию БД. Пример SQL для создания резервной разностной копии БД с проверкой копии по завершению (отличается от полного копирования флагом DIFFERENTIAL вместо него нужно использовать NOFORMAT).

declare @pathBackup as varchar(55) set @pathBackup =  N'C:Backup[Имя файла БД]_' + REPLACE(convert(varchar,GETDATE(), 104),'.','_') + '.bak' BACKUP DATABASE [Имя базы данных] TO  DISK = @pathBackup WITH DIFFERENTIAL, NOFORMAT, INIT, NAME = N'Полная База данных Резервное копирование', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM GO declare @backupSetId as int declare @pathBackup as varchar(55) set @pathBackup =  N'C:Backup[Имя файла БД]_' + REPLACE(convert(varchar,GETDATE(), 104),'.','_') + '.bak' select @backupSetId = position from msdb..backupset where database_name=N'[Имя базы данных]'  and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'[Имя базы данных]') if @backupSetId is null  begin  raiserror(N'Ошибка верификации. Сведения о резервном копировании для базы данных "[Имя базы данных]" не найдены.', 16, 1)  end RESTORE VERIFYONLY FROM  DISK = @pathBackup WITH FILE = @backupSetId,  NOUNLOAD,  NOREWIND GO 

3.Системные базы данных

Помимо основной базы и связанных с ней файлов, я настоятельно рекомендую делать копии и системных баз данных. Начнем с того, что рассмотрим какие базы существуют в MS SQL. Их всего 5:

Название

Описание

Более подробно можете прочитать о них тут и еще вот тут. Я выбрал резервировать только 2 системные БД:

1. msdb – потому что, там хранятся настроенные задачи и другие
2. master – хранятся все произведенные настройки SQL Server.

Данная информация все равно не сильно критична и ее можно восстановить руками, но зачем тратить лишнее время, когда можно просто взять из резервной копии.

4. План бекапирования

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

• Полная копия основной БД, чаще чем раз в неделю нет необходимости
• Разностная копия основной БД, каждый день
• Копии журнала транзакций основной БД, каждый час
• Копия системной БД master, раз в неделю
• Копия системной БД msdb, раз в неделю

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

День недели	Время	Действия	Частота	Описание
По завершению данной операции идет уведомление на почту.   Если создание резервной копии прошло удачно, удаляется старая полная резервная копия все старые разностные копии все старые журналы транзакций

5. Общие рекомендации по резервному копированию

1. Используйте опцию

   BACKUP WITH CHECKSUM

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

2. Не выполняйте резервное копирование файлов на тот же физический диск, на котором хранится база данных или протокол транзакций.
3. Если вы используете MS SQL 2008 или выше, рекомендую вам использовать сжатие резервных копий средствами SQL. Следующий код включит сжатие по умолчанию:

   USE master; GO EXEC sp_configure ‘backup compression default’, '1'; RECONFIGURE WITH OVERRIDE;

4. держите резервные копии по нескольку дней на случай, если одна из них будет повреждена – старая резервная копия лучше, чем никакой.
5. Используйте DBCC CHECKDB для проверки каждой базы данных перед копированием, это своевременно предупредит вас о надвигающихся проблемах.

   DBCC CHECKDB ('Имя базы данных') WITH NO_INFOMSGS,   ALL_ERRORMSGS;

   Примечание: на практики мы использовали данную проверку, только перед выполнением полной резервной копии.

6. Выполняйте периодически обновление статистики и реорганизации индексов БД

Используем приложение

Несколько нюансов по приложению:

• Все тексты и запросы в коде вынесены в ресурсы, мне так было проще
• При вводе параметров соединения и других настроек, они сохраняются в файл. Для Express и Standart используются разные файлы (dbStandart, udExpress) в них хранится класс UserData
• Для выполнения некоторых операций могут потребоваться права администратора
• На данный момент не работает соединение с БД под доменной учетной записью
• Программа не обладает суперкрасивым интерфейсом

1. Настройка уведомления администратора

Мне было лень каждый раз заходить на сервер и проверять, сработала ли задача или произошла какая-то ошибка. Да и хотелось иметь возможность получать другие уведомления, не только о выполнения задач. Для данной цели используется DatabaseMail MS SQL (для версии Standart и выше) В своем приложение я сделал специальный раздел для автоматизации данной задачи При нажатии появится форма для заполнения информации необходимой для создания профиля рассылки писем: Приложение автоматически настроено на стандартный 25 SMTP порт для адреса с которого отправляются письма. При необходимости его можно изменить в процедуре sysmail_add_account_sp Пользователь и пароль требуются на случай, если у почтового сервиса настроена аутентификация. Имя оператора в системе указывается для того, чтобы у нас нормально создался профиль в DatabaseMail. Пишите любое название, которое будет для вас понятным. Ниже приведен пример заполненной формы. Дальше, с данного ящика от указанного оператора, к нам будут приходить уведомления об успешном выполнении операций. Выполняемые действия на данном этапе:

1. Меняются системные параметры MS SQL.
2. Создается DatabaseMail Profile
3. Активируется в SQL Agente профиль
4. Создается DatabaseMail Account
5. Добавляется DatabaseMail Account к Database Mail Profile
6. Создается DatabaseMail Operator

Более подробно описано в следующей статье и, частично, я брал отсюда. Естественно, данные действия можно выполнить с помощью SSMS.

2.Дополнительные уведомления для администратора

В программе предусмотрены 2 задачи, применяемые к БД:

1. проверка целостности БД. Для проверки базы данных использовалась стандартная процедура DBCC CHECKDB.
2. информирование о свободном месте в файловых группах.
3. Вторая задача была реализована с помощью запроса к системной таблице dbo.sysfiles
4. Вот пример данного запроса, который выполнялся к базе:

Select   NAME = left(a.NAME,15),  a.FILEID,  [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)),  [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),  [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,  FILENAME = a.FILENAME From dbo.sysfiles a 

Ответ с сервера приходит на почту администратора в виде html разметки. Данный синтаксис возможен благодаря следующей стандартной функции MS SQL FOR XML. Так же пока я искал, как преобразовать в возвращаемый результат выполнения запросов в html текст, наткнулся на следующую страницу, где человек создал целую процедуру для этих целей Настроить эти операции можно с помощью соответствующего пункта в меню программы: Появиться окно для указания почтового ящика, на который необходимо высылать html текст отчета:

3.Решение проблем при настройке DatabaseMail

В MS SQL 2008 я столкнулся с проблемой при настройке SQL Server Agent. Симптомы следующие, после настройки невозможно запустить SQL Agent. В основном это решается с помощью установки update на SQL сервер. Убедитесь, что установлен sp1, а потом можно уже ставить обновление. Если данные обновления не помогают, необходимо скачать fix. Его можно найти на данном сайте конечную ссылку не могу указать сейчас, для того что бы дойти до пакета фикса, нужно будет ответить на ряд вопросов. Если есть проблемы с модулем DatabaseMail. После настройки данного модуля с помощью приложения, необходимо зайти в SQL Agent и просмотреть журнал событий. Если там будут ошибки «невозможно подключиться к почтовому ящику». Значит есть проблема, даже если через проверку отправляется письмо.Исправляется это следующими манипуляциями:

1. Management Studio — SQL Server Agent — Properties.
2. Alert System
3. Уберите галочку с Enable mail profile
4. Нажмите OК
5. Зайдите снова и поставьте галочку
6. Перезагрузите SQL Server Agent.

Проверьте учетную запись для SQL Agent service. Если это доменная учетная запись измените ее на системную или наоборот. Все должно заработать.

4.Настраиваем резервное копирование с помощью приложения для SQL Standart:

Выбираем версию Standart. Настраиваем уведомления. (см. раздел, настройки уведомления): Соединяемся с БД, заполняя данные для соединения и указываем БД, для которой будет применяться Job: Выбираем настройку резервного копирования: Указываем пути для сохранения копий БД. Если указанные папки не существует, то программа попытается их создать (нужны соответствующие права). Нажимаем сохранить и базе настраиваются соответствующие задачи. Желательно настроить для каждого бэкапа разные папки, т.к. при удалении будут удаляться все файлы с расширением bak. (см. раздел удаление копий БД)

5.Настраиваем резервное копирование с помощью приложения для SQL Express:

Так как в SQL Express отсутствует SQL Agent, задачу по автоматизации резервного копирования пришлось решить другим путем. В указанной пользователем папке создается bat файле в котором описан SQL запрос, отвечающий за создание резервной копии. В случае необходимости можно редактировать его напрямую. По мимо этого должен работать стандартный планировщик Windows, в нем создается задача, которая будет запускать раз в сутки в указанное время. Для этого запускаем приложение. Выбираем пункт MS SQL Express: Появляется форма для заполнения параметров: Указываем где будет сохраняться наша копия, а также где будет лежать bat файл для создания копии базы (имя файла указывать не надо, оно будет задано автоматически). Далее указываем настройки соединение и время, когда необходимо запускать задачу. Единственный минус данного подхода в том, что приходится храниться в открытом виде пароль для соединения с БД.

6.Удаление задач из БД.

Если необходимо удалить все задачи из БД (например, захотели изменить пути сохранения БД). Для этого используем соответствующий пункт в меню программы. Из SQL Agent будут удалены все задачи с определенным начальным префиксом (в моем случае King):

7.Удаление копий БД

В некоторых задачах, настроено удаление старых копий БД. Для этого я использую процедуру master.dbo.xp_delete_file. Пример использования: Удалит все файлы с расширением bak из указанной папки, дата создания которых превышает 14 дней.

 EXECUTE master.dbo.xp_delete_file 0,"E:backups",N'bak',dateadd(d,-14,getdate()),0; 

И вот еще один более подробный пример и информация о том, какие параметры принимает данная функция.

Как восстанавливать резервные копии

Из-за нехватки времени модуль восстановления еще не реализован, возможно в будущем я его добавлю, а пока просто кратко опишу как можно будет восстановить базу. С помощью SQL скрипта. Для восстановления базы данных используется команда RESTORE. Если необходимо восстановить просто базу из полной копии, то достаточно выполнить следующий скрипт:

 RESTORE DATABASE [Имя базы данных] FROM DISK = 'Z:SQLServerBackupsback.bak' WITH REPLACE 

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

RESTORE DATABASE TEST_DB –восстанавливаем полную копию    FROM test_db_full    WITH NORECOVERY; GO RESTORE DATABASE TEST_DB –восстанавливаем разностную копию    FROM test_db_diff    WITH FILE = 1,    NORECOVERY; GO RESTORE LOG TEST_DB –восстанавливаем журнал транзакций №1    FROM test_db_tran_1    WITH FILE = 1,    WITH NORECOVERY; GO RESTORE LOG TEST_DB –восстанавливаем журнал транзакций №2    FROM test_db_tran_2    WITH FILE = 1,    WITH NORECOVERY; GO RESTORE DATABASE TEST_DB    WITH RECOVERY; GO 

Для восстановления БД можно использовать так же и SSMS.

В этой статье мы рассмотрим, как настроить резервное копирование баз данных в 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 IMMEDIATEBACKUP LOG [TestDatabase2] TO DISK = N'E:MSSQL15.NODE2MSSQLBackupTestDatabase2_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 = 5RESTORE DATABASE [TestDatabase2] FROM DISK = N'E:MSSQL15.NODE2MSSQLBackupfull.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5RESTORE LOG [TestDatabase2] FROM DISK = N'E:MSSQL15.NODE2MSSQLBackuptrans.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5RESTORE LOG [TestDatabase2] FROM DISK = N'E:MSSQL15.NODE2MSSQLBackuptrans.bak' WITH FILE = 2, NOUNLOAD, STATS = 5, STOPAT = N'2020-02-17T15:38:23'ALTER DATABASE [TestDatabase2] SET MULTI_USERGO

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

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

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

Используемые источники:

• https://www.alta.ru/support/51/
• https://habr.com/post/264927/
• https://winitpro.ru/index.php/2020/02/26/backup-i-vosstanovlenie-baz-dannyx-mssql/