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

Восстановление разностной резервной копии базы данных (SQL Server)

В этом разделе описано, как восстановить разностную резервную копию базы данных в SQL Server с помощью среды SQL Server Management Studio или Transact-SQL.

В этом разделе

Перед началом работы

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

Перед началом

Ограничения

Инструкция RESTORE недопустима в явной или неявной транзакции.

В SQL Serverможно восстановить пользовательскую базу данных из резервной копии базы данных, созданной с помощью SQL Server 2005 (9.x) или более поздней версии.

Предварительные требования

безопасность

Permissions

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

Использование среды SQL Server Management Studio

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

После подключения к соответствующему экземпляру компонента Microsoft Компонент SQL Server Database Engine в обозревателе объектов разверните дерево сервера, щелкнув имя сервера.

Разверните узел Базы данных. В зависимости от типа восстанавливаемой базы данных выберите пользовательскую базу данных или раскройте узел Системные базы данных и выберите системную базу данных.

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

База данных

Устройство

В списке Источник: Устройство: База данных выберите имя базы данных, из которой нужно восстановить резервные копии.

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

Дополнительные сведения о столбцах сетки Резервные наборы данных для восстановления см. в статье Восстановление базы данных (страница «Общие»).

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

Перезаписать существующую базу данных (WITH REPLACE)

Сохранить параметры репликации (WITH KEEP_REPLICATION)

Выдавать приглашение перед восстановлением каждой резервной копии

Ограничить доступ к восстановленной базе данных (WITH RESTRICTED_USER)

Дополнительные сведения об этих параметрах см. в статье Восстановление базы данных (страница «Параметры»).

Схема RESTORE WITH NORECOVERY оставляет базу данных в нерабочем состоянии и не выполняет откат незафиксированных транзакций. Можно восстановить дополнительные журналы транзакций. База данных не может быть использована, пока не будет восстановлена.

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

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

По желанию можно использовать страницу Файлы для восстановления базы данных в новом расположении. Дополнительные сведения см. в разделе Восстановление базы данных в новом расположении (SQL Server).

Нажмите кнопку ОК.

Использование Transact-SQL

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

Выполните инструкцию RESTORE DATABASE с предложением NORECOVERY, чтобы восстановить полную резервную копию базы данных, которая предшествует разностной резервной копии базы данных. Дополнительные сведения см. в разделе Как восстановить полную резервную копию.

Выполните инструкцию RESTORE DATABASE для восстановления разностной резервной копии базы данных, указав:

имя базы данных, к которой будет применена разностная резервная копия;

устройство резервного копирования, с которого происходит восстановление разностной резервной копии базы данных;

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

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

Примеры (Transact-SQL)

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

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

Источник

Восстановление резервной копии журнала транзакций (SQL Server)

В этом разделе описывается восстановление журнала транзакций из резервной копии в SQL Server с помощью среды SQL Server Management Studio или Transact-SQL.

В этом разделе

Перед началом работы

Для восстановления резервной копии журнала транзакций используется:

Перед началом

Предварительные требования

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

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

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

безопасность

Permissions

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

Использование среды SQL Server Management Studio

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

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

После подключения к соответствующему экземпляру Microsoft Компонент SQL Server Database Engine в обозревателе объектов разверните дерево сервера, щелкнув его имя.

Раскройте узел Базы данных и в зависимости от типа восстанавливаемой базы данных выберите пользовательскую базу данных или раскройте узел Системные базы данных и выберите системную базу данных.

На странице Общие в списке База данных выберите имя базы данных. Перечислены только базы данных, которые находятся в состоянии восстановления.

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

Из предыдущих резервных копий базы данных

Из файла или с ленты

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

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

Выберите один из следующих вариантов:

На момент времени

До помеченной транзакции

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

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

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

Сохранить параметры репликации (WITH KEEP_REPLICATION)

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

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

Выбор этого параметра равнозначен использованию параметра KEEP_REPLICATION в инструкции Transact-SQLRESTORE.

Выдавать приглашение перед восстановлением каждой резервной копии

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

Ограничить доступ к восстановленной базе данных (WITH RESTRICTED_USER)

Доступ к восстановленной базе данных будет только у пользователей db_owner, dbcreator или sysadmin.

Выбор этого параметра равнозначен использованию параметра RESTRICTED_USER в инструкции Transact-SQLRESTORE.

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

Восстановить готовность базы данных к работе, выполнив откат незафиксированных транзакций. Невозможно восстановить дополнительные журналы транзакций. (RESTORE WITH RECOVERY)

Этот параметр следует выбирать только в том случае, если нет файлов журнала, подлежащих восстановлению.

Оставить базу данных в нерабочем состоянии и не производить откат незафиксированных транзакций. Можно восстановить дополнительные журналы транзакций. (RESTORE WITH NORECOVERY)

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

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

Оставить базу данных в режиме «только для чтения». Отменить незафиксированные транзакции, но сохранить отмененные действия в файле, чтобы результаты восстановления могли быть отменены. (RESTORE WITH STANDBY)

При выборе этого параметра необходимо указать резервный файл.

Использование Transact-SQL

Во избежание неоднозначности в каждой инструкции WITH RECOVERY рекомендуется явное задание параметра WITH NORECOVERY или WITH RECOVERY. Это особенно важно учитывать при написании скриптов.

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

Чтобы применить резервную копию журналов транзакций, выполните инструкцию RESTORE LOG, указав при этом:

Имя базы данных, к которой будет применен журнал транзакций.

устройство резервного копирования, с которого будет восстановлена резервная копия журналов транзакций;

В этой инструкции применяется следующая основная синтаксическая конструкция:

RESTORE LOG имя_базы_данных FROM WITH NORECOVERY.

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

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

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

Восстановить базу данных в составе последней инструкции RESTORE LOG:

Подождать, а затем восстановить базу данных отдельной инструкцией RESTORE DATABASE:

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

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

Примеры (Transact-SQL)

По умолчанию для базы данных AdventureWorks2012 используется простая модель восстановления. В следующем примере для перехода на модель полного восстановления требуется изменить базу данных следующим образом:

A. Применение одной резервной копии журнала транзакций

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

Источник

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

За исключением этих нюансов – ничем не отличается от обычной полной копии.

Частичная резервная копия

Partial backup этот тип резервной копии используется для того, чтобы снять копии с read-only файловых групп. На практике используется редко.

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

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

Модели восстановления базы данных SQL Server

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

Простая модель восстановления

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

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

При использовании этой модели восстановления, следующий функционал SQL Server недоступен:

Полная модель восстановления

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

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

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

Восстановление с неполным протоколированием (bulk logged)

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

В остальном эта модель работает аналогично полной модели восстановления.

Настройка резервного копирования SQL Server с помощью плана обслуживания

Планы обслуживания SQL Server это самый распространенный способ настройки регулярного резервного копирования.

Рассмотрим настройку резервного базы данных на SQL Server копирования по плану:

Укажите имя плана и выберите режим “Separate schedules for each task”.

Выберите операции, которые нужно сделать в этом плане обслуживания:

Используйте следующую последовательность операций:

Выберите базу данных 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

Источник