Невозможно восстановить журнал или разностную резервную копию так как нет файлов готовых к накату

Обновлено: 24.01.2025

Такая фигня: средствами MS SQL настроен бэкап базы: полный - раз в неделю (Вс, 3:05), разностный - раз в день (ежедневно 6:35), журнал транзакций - каждые 15 минут.

Что ни делаю, с разностным бэкапом получается ерунда, а именно:

Захожу каждый день в Задачи текущей базы, Восстановить.

Смотрю какие бэкапы есть, а там есть Полный бэкап и журнал транзакций.

Так вот, там записи: Полный сделан в 5:20 ЕЖЕДНЕВНО! 5:20. У меня не это время нет настроек. Тем более нет ежедневных расписаний для полного бэкапа.

А дальше, после полного, идут журналы транзакций.

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

"Выполняется от имени пользователя: WORKGROUP\ХХХХХХ.Не удается выполнить разностное резервное копирование для базы данных "ЙЙЙЙЙ", так как не существует ее текущей резервной копии. Произведите полное резервное копирование базы данных, выполнив инструкцию BACKUP DATABASE без параметра WITH DIFFERENTIAL. [SQLSTATE 42000] (Ошибка 3035) BACKUP DATABASE прервано с ошибкой. [SQLSTATE 42000] (Ошибка 3013). Шаг завершился с ошибкой."

Сегодня на ночь отключил выполнение полного бэкапа. Утром захожу, а там в 5:20 опять полный!

Пытаюсь сделать разностный относительно этого полного, опять не дает.

При этом сами бэкапы нормальные, рабочие, из них всё нормально восстанавливается.

Дальше: запускаю ПРИНУДИТЕЛЬНО полный бэкап. После него запускаю ПРИНУДИТЕЛЬНО разностный. Всё запускается и цепочка бэкапов создается!

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

Полез в инет. На сайте мелкомягких есть заметка по ВинСерв 2008, там написано, что бэкап самого сервера может рвать цепочку бэкапов.

Но, во первых, это относилось именно к ВинСерв 2008, а во вторых я отключал бэкап сервака на пару дней, но проблема осталась.

Помогите, плиз. В чем может быть дело?

если это так, то зачем вам острые ощущения с инкрементальными бекапами.

делайте полные бекапы и жмите их архиватором, если места впритык.

Да, бэкап делается быстро.

Места тоже в принципе хватает, хотя его много не бывает. Но, бэкап быстро разрастается, когда он каждый день полный. Когда его создаешь с нуля, то он вообще 1,3 гб, но уже через несколько дней 4,5. К тому же у меня бэкапы каждый день сливаются в облако, чтобы я его мог иметь всегда на локальной, моей машине (это база 1С). И есть разница качать 1,3 Гб или 5.

Но, это ж непорядок, что так работает? Есть же какая-то причина.

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

Ну а причину-то такого поведения как найти? Никто не сталкивался?

Четверьг:
Да, бэкап делается быстро.
Места тоже в принципе хватает, хотя его много не бывает. Но, бэкап быстро разрастается, когда он каждый день полный. Когда его создаешь с нуля, то он вообще 1,3 гб, но уже через несколько дней 4,5. К тому же у меня бэкапы каждый день сливаются в облако, чтобы я его мог иметь всегда на локальной, моей машине (это база 1С). И есть разница качать 1,3 Гб или 5.
Но, это ж непорядок, что так работает? Есть же какая-то причина.

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

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

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

разрастается база - посмотрите как у вас настроен shrink(можно его делать перед бекапом) и какой режим логов транзакций(в большинстве случаев достаточно выбрать simple mode и логи сами будут подчищаться). база и бекапы уменьшатся.

"И есть разница качать 1,3 Гб или 5." - запакуйте бекап, сжатие будет раз в 7 или больше, т.е. 5G / 7 = 714Mb

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

Дык так же может полететь и полный.

А где это смотреть? Это то?

Просто когда создавал расписания, то выбирал св-ва бэкапов и сразу по ним сохранял скрипты бэкапа (для агента). А сейчас я особо и не вижу нигде содержимого этих самых заданий. Где-то они хранятся, но только я не знаю где именно. Вижу только сами задания, в Агенте, но не их содержимое.

BACKUP DATABASE [ЙЙЙЙ] TO DISK = N'D:\MSSQL\Backup\ЙЙЙ' WITH RETAINDAYS = 14, NOFORMAT, NOINIT, NAME = N'ЙЙЙЙ', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
declare @backupSetId as int
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 = N'D:\MSSQL\Backup\ЙЙЙЙ' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO "И есть разница качать 1,3 Гб или 5." - запакуйте бекап, сжатие будет раз в 7 или больше, т.е. 5G / 7 = 714Mb

У меня поставлено сжатие средствами СКЛа. Это не то? Жать просто в зип? База от этого не полетит?

Четверьг:
Дык так же может полететь и полный.

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

у меня уже не осталось MSSQL серверов, так что негде посмотреть. называется "Simple Recovery Model"

У меня поставлено сжатие средствами СКЛа. Это не то? Жать просто в зип? База от этого не полетит?

про "сжатие средствами СКЛа" ничего не скажу, я бекапил без сжатия, чтобы бекап проходил быстрее и меньше напрягать MSSQL сервер, затем по крону запускал батничек, который 7zip-ом паковал бекапы в зип, одна база - один архив (попутно ставил и пароль на архивы). бекап - это обычный файл(ы), при упаковке/распаковке с ним ничего не случиться.

Четверьг:
Когда его создаешь с нуля, то он вообще 1,3 гб, но уже через несколько дней 4,5.

Тем самым Ваш файл с бэкапом будет перезаписываться.

Лично я усечение (shrink) базы не практикую, зачем мне лишняя фрагментация. Глядишь нагрузка на диски снизится.

Кроме того, если и регулярно бэкапить логи транзакци, то файл логов при Вашем размере базы сильно не разрастется. И переживать об этом не стоит.

Ставить режим simple для базы 1с, не самая лучшая идея, мало ли кто то сильно накосячит (особенно в конфигураторе) и нужно будет откатить базу на некоторое время назад (например 15 минут), а в режиме simple это не возможно (толь восстановление на момент создание полного бэкапа).

Sujcnm:
Ставить режим simple для базы 1с, не самая лучшая идея, мало ли кто то сильно накосячит (особенно в конфигураторе) и нужно будет откатить базу на некоторое время назад (например 15 минут), а в режиме simple это не возможно (толь восстановление на момент создание полного бэкапа).

тут другой вопрос, почему кто то способный накосячить имеет доступ к конфигуратору? лучше лечить причину, а не следствия при помощи откатов на 15 минут. (я бы не стал сильно надеятся на такие откаты, можно одно вылечить, а другое залечить)

если нужно внести потенциально опасные изменения("в конфигураторе" или еще как-то), то желательно клацнуть пару кнопок и сделать полный бекап, по времени у ТС это занимает 20 секунд и дальше крутите, что хотите. :)

admak:
тут другой вопрос, почему кто то способный накосячить имеет доступ к конфигуратору?

А причем тут "кто то", тут самих 1Сков хватает.

Не могу назвать дату, но года 2 назад 1Сники выпустили обновление ЗУП 2,5 ( и при определенных условиях, а именно наличия каких-то проводок) обновление конфигурации приводило к полной не работоспособности конфигурации.

Был случай, 1С зависла на ровном месте, в момент обновления.

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

Если сможете вылечить причину (кудрявые руки разработчиков 1С), то с меня ящик пива.

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

При модели simple - восстановление только с того момента как создана.

При модели full - позволяет восстановить на нужный момент времени (благодаря логам которые автор делает через каждые 15 мин), при условии, что бэкап и база хранятся на разных дисках, а еще лучше на разных машинах. (ну это прописная истина)

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

Спасательный круг на случай если сервер БД решит прилечь и откажется просыпаться.

Каждый сам выбирает, что ему важнее , скорость и простота бэкапирования или надежность.

admak:
по времени у ТС это занимает 20 секунд и дальше крутите, что хотите.

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

Кроме того, со временем база разрастется и к этому вопросу придется вернуться.

26.02.2020

SQL Server

комментариев 12

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

Для примера, воспользуемся 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

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

4 Резервное копирование журнала транзакций базы данных.

5 Резервное копирование файловых групп базы данных.

Восстановление из резервных копий

6 Восстановление из полной резервной копии.

7 Восстановление из разностной резервной копии.

8 Восстановление журнала транзакций.

9 Восстановление файловых групп.

10 Восстановление системных баз данных.

Создадим нашу тестовую базу данных “sbase”, модель восстановления - полная:

Создание резервных копий

1Резервное копирование системных баз данных.

Список системных баз: master, model, msdb, tempdb.

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

Model: Используется в качестве шаблона для создаваемых баз данных. Резервное копирование необходимо при изменении настройки самой базы model.

Msdb: Содержит сведения о заданиях и для агента сервера MS SQL Server. копирование необходимо делать каждый раз при добавлении задания для агента сервера MS SQL Server.

Tempdb: Хранит временные данные например для транзакций. Уничтожается и создается при перезапуске экземпляра MS SQL Server. Резервное копирование делать нет смысла.

Создадим новый запрос:

Выполним следующий запрос:

BACKUP DATABASE master

BACKUP DATABASE model

BACKUP DATABASE msdb

Как видим, на диск ‘C’ было произведено успешное резервное копирование системных баз данных.

2Полное резервное копирование базы данных.

Включает в себя файлы данных и журнал транзакций. По сути является базой данных на момент создания резервной копии базы данный MS SQL Server.

Включает в себя:

Резервное копирование данных в базе.

Резервное копирование изменений, возникающих во время резервного копирования

Резервное копирование транзакций, не зафиксированных в журнале транзакций.

Способ 1(Графический интерфейс):

Выберем «создать резервную копию»

Указываем куда копировать и модель – полная.

Способ 2(Запрос SQL):

BACKUP DATABASE sbase

3 Разностное резервное копирование базы данных.

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

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

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

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

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

--Создадим таблицу test

CREATE TABLE test(

INSERT INTO test (id,name)

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

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

--Делаем полный бэкап

BACKUP DATABASE sbase

--Добавим еще данные

INSERT INTO test (id,name)

BACKUP DATABASE sbase

А вот и результат:

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

4 Резервное копирование журнала транзакций базы данных.

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

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

В процессе выполняются следующие действия:

Создается копия журнала транзакций от последнего резервного копирования лога до конца текущего.

Очищаются части журнала транзакций до начала активной части и отбрасываются сведения в неактивной части.

По аналогии с полным и разностным копированием запускаем задачу, но тип выбираем – лог транзакций:

Или с помощью запроса:

BACKUP LOG sbase

5 Резервное копирование файловых групп базы данных.

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

Файлы журналов не входят в состав файловых групп.

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

Пример полного копирования:

По аналогии с другими видами копирования запускаем мастер:

Тоже, только запросом:

BACKUP DATABASE sbase

Про восстановление можно почитать на русском языке :

Стадия копирования данных: копирование всех страниц данных, журнала и индекса с резервного носителя в файлы базы данных.

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

Стадия отката: производит откат незафиксированных транзакций и делает базу данных доступной для пользователей. После завершения стадии отката восстановление последующих резервных копий становится невозможным. Затем в процессе восстановления база данных переводится в активный режим.

Режим WITH RECOVERY включает и стадию повтора, и стадию отката и восстанавливает базу данных. Более поздние резервные копии восстановить невозможно. Это значение по умолчанию.Если набор данных наката не был восстановлен в достаточной степени, чтобы обеспечить согласованность с базой данных, стадия отката выполнена быть не может. Компонент Database Engine выдает ошибку и прекращает восстановление. Если весь набор данных наката согласован с базой данных, то выполняется восстановление, после чего базу данных можно перевести в режим в сети.

6 Восстановление из полной резервной копии.

Или с помощью запроса:

RESTORE DATABASE sbase

7 Восстановление из разностной резервной копии.

В начале восстанавливается полная копия(например в прошлом шаге мы это уже сделали), а далее восстановим разностную копию.

Графический интерфейс аналогичен с предыдущим примером за исключением типа выбираемой копии, а запрос будет таков на примере наших разностных копий:

RESTORE DATABASE sbase

WITH FILE = 1, NORECOVERY, REPLACE

RESTORE DATABASE sbase

WITH FILE = 1, RECOVERY

8 Восстановление журнала транзакций.

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

Графический вариант интуитивно понятен, будет продемонстрирован только SQL запрос:

Для того, чтоб все отработало корректно, вернемся разностному бэкапу 2, и после него накатим журнал транзакций:

RESTORE DATABASE sbase

WITH FILE = 1, NORECOVERY, REPLACE

RESTORE DATABASE sbase

WITH FILE = 1, NORECOVERY, REPLACE

RESTORE LOG sbase

WITH FILE = 1, NORECOVERY

9 Восстановление файловых групп.

Графический вариант показан не будет, он довольно интуитивен, запрос SQL:

WITH PARTIAL, RECOVERY, REPLACE

Так как мы восстанавливали только часть базы – файловую группу, то мы использовали параметр «PARTIAL».

10 Восстановление системных баз данных.

Если экземпляр SQL сервера доступен, то системные базы восстанавливаются согласно приведенной таблице:

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

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

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

Запускаем экземпляр сервера в однопользовательском режиме: выключим и включим экземпляр сервера с параметром запуска /m, введя в командной строке Windows (CMD):

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

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

Решение

Сначала приведем общий алгоритм по созданию резервной копии:

1) Определяем для каких баз данных нужно сделать резервную копию
2) Проверяем каждую выбранную базу данных на целостность
3) Создаем для каждой выбранной базы данных резервную копию (полную или разностную (дифференциальную), или журнала транзакций)
4) Проверяем полученные резервные копии
5) Сжимаем журналы транзакций отработанных баз данных (по необходимости)

Далее приведем пример реализации данного выше алгоритма.

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

В первом столбце указывается идентификатор базы данных, в FullPathBackup содержится полный путь для создания полных резервных копий (например, 'диск:\. \'), а в DiffPathBackup и LogPathBackup полные пути для создания разностных резервных копий и резервных копий журналов транзакций соответственно. Если столбец DiffPathBackup или LogPathBackup будет пустым, то база данных не будет участвовать в создании разностной резервной копии или резервной копии журнала транзакций соответственно.

Также можно создать представление на основе этой таблицы:

Представление для настроек резервного копирования

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

Теперь создадим представление, которое выводит информацию по файлам БД из системного представления sys.master_files:

Для создания полных резервных копий реализуем хранимую процедуру:

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

Аналогично реализуются хранимые процедуры по созданию разностных резервных копий и резервных копий журналов транзакций:

Процедура по созданию разностных резервных копий БД

Т к проверка целостности БД – достаточно ресурсоемкая задача, то для повышения быстродействия можно не проверять на целостность БД перед созданием разностной резервной копии БД.

Процедура по созданию резервных копий журналов транзакций

Т к резервная копия журнала транзакций обычно делается достаточно часто, а проверка целостности БД – достаточно ресурсоемкая задача, то обычно не проверяют целостность БД перед созданием резервной копии журнала транзакций.

Также необходимо помнить о том, что периодически нужно делать полные резервные копии БД master, msdb и model.

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

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

Наиболее общий подход:

1) Создание полной резервной копии 1 раз в день
2) Создание разностных резервных копий каждые 2-4 часа
3) Создание резервных копий журнала транзакций каждые 5-60 минут

Также важно помнить о том, что обычно базы данных участвуют в системе отказоустойчивости и быстрой доступности. И если в последнем используются резервные копии журналов транзакций, то важно не мешать этому процессу (т е нельзя допускать создания резервных копий журнала транзакций БД разными процессами, т к тогда будет потеряна последовательность для восстановления из этих резервных копий).

Здесь были приведены примеры последовательной обработки каждой БД. Но в производстве вполне возможно распараллелить обработку, делая несколько резервных копий одновременно. Это можно сделать разными способами. Например, вызовом следующей хранимой процедуры:

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

Теперь приведем общий алгоритм по восстановлению базы данных из созданных ранее резервных копий (в другой или тестовой среде):

1) Определяем какие базы данных нужно восстановить, а также место нахождения резервных копий
2) Восстанавливаем базы данных
3) Проверяем целостность восстановленных баз данных

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

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

Здесь назначения колонок аналогичны назначениям колонок для таблицы [srv].[BackupSettings] с той лишь разницей, что по полному пути будут не создаваться резервные копии, а браться существующие для восстановления.

Данная таблица нужна, чтобы определить полные названия файлов восстанавливаемой БД для последующего переноса (например, [SourcePathRestore]='Логическое имя файла' и [TargetPathRestore]= 'диск:\. \Физическое имя файла', а [Ext]= 'Расширение файла'.

На самом деле здесь можно определить логические имена файлов БД по следующему запросу:

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

Теперь приведем пример реализации хранимой процедуры по восстановлению БД из полной резервной копии с последующей проверкой на целостность данных:

Процедура восстановления БД по полным резервным копиям

Здесь, чтоб определить, какую полную резервную копию нужно восстанавливать, берётся имя файла, которое формируется следующим образом:
<название БД>_Full_backup_<год>_<номер_месяца_в_году>_<номер_дня_в_месяце>.bak

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

Просмотреть последние сделанные резервные копии БД можно с помощью следующего представления:

Представление по последним сделанным резервным копиям БД

Результат

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

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

Читайте также: