Tail log backup что это

SQL-Ex blog

Одной из основополагающих обязанностей администратора баз данных является обеспечение доступности резервных копий (бэкапов).

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

Зачем делать бэкапы?

1. Вы хотите иметь возможность восстанавливать поврежденные данные или ошибки, которые делали люди, непосредственно меняющие данные — например, при написании и выполнении скрипта в SSMS, вне приложения, которое использует набор протестированных хранимых процедур.
2. Вы хотите обновить вашу среду разработки, чтобы иметь данные, подобные тем, которые находятся в промышленной базе данных.
3. Вы хотите иметь бэкапы, чтобы протестировать восстановление базы данных и выяснить, сколько времени это займет.
4. Вам нужны бэкапы, поскольку они часто представляют собой существенную часть процесса перехода с одного SQL Server на другой — обычно более новый или на Azure.

Полные резервные копии SQL Server (Full Backup)

Что выполняет полный бэкап? Полный бэкап создает резервную копию всей базы данных. Это подразумевает включение всей структуры базы данных и всех данных. Когда стартует полное резервирование, записывается Log Sequence Number (LSN — последовательный номер журнала), LSN записывается и при завершении полного резервирования. Этот LSN является механизмом, используемым SQL Server, чтобы знать, в каком порядке выполнялись операторы INSERT, UPDATE или DELETE. При этом наличие записанных LSN начала и окончания, как части полного бэкапа, обеспечивает согласованное с точки зрения транзакций резервное копирование, поскольку при полном резервном копировании учитываются изменения, произошедшие во время резервного копирования. Это обеспечивает обработку таких транзакций в процессе восстановления бэкапа. Эта полная резервная копия служит базовым бэкапом или отправной точкой для всех последующих дифференциальных резервных копий или бэкапов журналов. Всякий раз, когда создается новый полный бэкап, устанавливается новая точка отсчета бэкапов.

Дифференциальные резервные копии SQL Server

Что дает вам дифференциальный бэкап? Дифференциальное резервное копирование основывается на концепции разностного растрового изображения. SQL Server оперирует 8-килобайтными страницами. Эти страницы собраны в группы по 8, называемые «экстентами» (Extents). Имеется битовая карта, которая используется для пометки каждого экстента, который подвергался изменению данных с момента последнего полного резервирования. При выполнении дифференциального бэкапа именно эта битовая карта определяет, какие данные помещать в резервную копию.

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

Когда используется полная модель восстановления, мощь дифференциала может быть очень важна. Дифференциальный бэкап сокращает число резервных копий журнала, которые должны быть восстановлены. Например, если имеется полный бэкап, сделанный в ночь воскресенья, и после этого каждые 15 минут создавались журналы транзакций, и администратору нужно восстановить базу данных ко вторнику в 5:53, ему потребуется построить процесс восстановления на базе полного бэкапа и каждого журнала транзакция, начиная с вечера воскресенья! Это потребует включения множества журналов транзакций. При ежедневном дифференциале, выполняемом в 5 часов, администратору потребуется восстановить полный воскресный бэкап, один дифференциальный, созданный во вторник в 5 часов, и только те журналы транзакций, которые создавались после 5 часов вторника. Возможность отбросить все эти журналы транзакций и обуславливает мощь этого метода.
Это значительно упрощает создание и читабельность скрипта и может сохранить массу времени администратору при написании скрипта вручную. Вам следует иметь автоматизированный процесс создания скрипта резервирования, но некоторые администраторы еще не настроили его. Мы позже поговорим об автоматизации создании скрипта резервных копий.

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

Что такое бэкапы файлов и файловых групп? Для начала это более продвинутый вариант по сравнению с другими типами бэкапов. А раз это так, менее вероятно, что вам потребуется этот тип резервной копии. Однако знание того, какие варианты существуют, всегда полезно.

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

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

USE master; 
GO 
-- Создание базы данных с дефолтными 
-- файловой группой для данных, 
-- файловой группой для файлового потока 
-- файла журнала. 
-- Задан максимальный размер и его приращение 
-- для первичного файла данных 
CREATE DATABASE MyDB 
ON PRIMARY 
( NAME='MyDB_Primary', 
FILENAME= 
'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_Prm.mdf', 
SIZE=4MB, 
MAXSIZE=10MB, 
FILEGROWTH=1MB), 
-- Здесь создается отдельный файл и 
-- привязывается к файловой группе, 
-- которая несколько больше, чем контейнер. 
FILEGROUP MyDB_FG1 
( NAME = 'MyDB_FG1_Dat1', 
FILENAME = 
'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_1.ndf', 
SIZE = 1MB, 
MAXSIZE=10MB, 
FILEGROWTH=1MB), 
-- Здесь создается другой файл и 
-- привязывается к файловой группе. 
( NAME = 'MyDB_FG1_Dat2', 
FILENAME = 
'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_2.ndf', 
SIZE = 1MB, 
MAXSIZE=10MB, 
FILEGROWTH=1MB) 
LOG ON 
( NAME='MyDB_log', 
FILENAME = 
'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB.ldf', 
SIZE=1MB, 
MAXSIZE=10MB, 
FILEGROWTH=1MB); 
GO 
ALTER DATABASE MyDB 
MODIFY FILEGROUP MyDB_FG1 DEFAULT; 
GO 
-- Создается таблица в файловой группе пользователя. 
USE MyDB; 
CREATE TABLE MyTable 
( cola int PRIMARY KEY, 
colb char(8) ) 
ON MyDB_FG1; 
GO

Резервные копии журналов транзакций в SQL Server

Что такое бэкап журнала транзакций? Бэкап журнала транзакций будет создавать резервную копию журнала транзакций SQL Server, чтобы изменения данных, хранящиеся в нем, можно было воспроизвести в процессе восстановления. И здесь упомянутая ранее концепция Log Sequence Number является ключевой. Поскольку транзакции происходят в определенном порядке, бэкап журнала поддерживает этот порядок транзакций. Бэкапы журналов транзакций должны восстанавливаться по порядку. Если вы попытаетесь восстановить ряд бэкапов журналов в неверном порядке, SQL Server сгенерирует ошибку. Наличие бэкапов журналов позволяет выполнить так называемое восстановление к заданной точке во времени (point in time restore). Возможность восстановления к заданной точке времени может быть критичным для бизнеса. Предположим, что некто внес изменения непосредственно в одну или более таблиц. Если была допущена ошибка и произошло непредусмотренное изменение данных, восстановление к точке времени позволит администратору восстановить данные к моменту, предшествующему выполнению ошибочных изменений.

Только копирование резервных копий (Copy Only Backups)

Что такое копирование только бэкапов? Копирование бэкапов не влияет или не отслеживает информацию Log Sequence Number. Зачем это может понадобиться? Ну, предположим, что вы уже сделали полный, дифференциальные бэкапы и бэкапы журналов. Вас просят предоставить файл бэкапа баы данных в другой отдел или, возможно, отдельному разработчику, работающему на проектом. Этот отдел предположительно не имеет доступа к каталогу, где находятся резервные копии. Вместо того, чтобы попытаться скопировать множество бэкапов для предоставления общего доступа или размещения его в другом месте, к которому имеет доступ разработчик, вы могли бы просто создать полный бэкап Copy Only, который записывается в место общего доступа. Затем разработчик может использовать этот бэкап для восстановления на локальной машине, а вы получаете то преимущество, что не меняете базовую резервную копию для своей цепочки бэкапов.

Что дальше

1. Исследуйте свою среду на предмет использования «мощи дифференциала» для создания дифференциальных резервных копий с целью упростить процесс восстановления.
2. Узнайте размер ваших баз данных и определите, подходит ли ваша ситуация для получения выгоды от разбиения базы данных на множества файлов и файловых групп.

Резервные копии журналов (SQL Server)

Эта статья относится только к резервному копированию и восстановлению баз данных SQL Server, использующих полные или массовые модели восстановления.

Резервная копия tail-log записывает все записи журнала, которые еще не были сохранены ( хвост журнала), чтобы предотвратить потерю работы и сохранить цепочку журналов без изменений. Прежде чем восстановить базу данных SQL Server до последней точки во времени, необходимо создать резервную копию хвоста журнала транзакций. Резервная копия tail-log — это последняя резервная копия плана восстановления для базы данных.

Не для всех сценариев восстановления требуется резервная копия заключительного фрагмента журнала. Если точка восстановления содержится в более ранней резервной копии журналов, вам не нужна резервная копия журналов. Резервная копия tail-log не требуется при перемещении или замене (перезаписи) базы данных и не требуется восстановить ее до точки времени после последней резервной копии.

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

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

• Если база данных находится в режиме «в сети» и следующим действием над базой данных должна быть операция восстановления, то прежде необходимо выполнить резервное копирование заключительного фрагмента журнала. Чтобы избежать ошибки для веб-базы данных, необходимо использовать WITH NORECOVERY параметр инструкции BACKUP Transact-SQL.
• Если база данных, работающая в режиме «вне в сети», не запускается и необходимо восстановить базу данных, то в первую очередь необходимо выполнить резервное копирование заключительного фрагмента журнала. Так как в настоящее время транзакции не могут выполняться, используйте WITH NO_TRUNCATE этот параметр. NO_TRUNCATE фактически совпадает с резервным копированием журнала транзакций только для копирования. Использование WITH NORECOVERY является необязательным, так как в настоящее время транзакции не могут возникать.
• Если база данных повреждена, попробуйте создать резервную копию tail-log с помощью WITH CONTINUE_AFTER_ERROR параметра инструкции BACKUP . В поврежденной базе данных резервное копирование хвоста журнала может быть выполнено только в том случае, если файлы журналов не повреждены, база данных находится в состоянии, поддерживающем резервные копии tail-log, и база данных не содержит никаких массовых изменений. Если не удается создать резервную копию tail-log, все транзакции, зафиксированные после последней резервной копии журнала, будут потеряны.

В следующей таблице перечислены NORECOVERY NO_TRUNCATE параметры и CONTINUE_AFTER_ERROR параметры BACKUP .

Резервные копии tail-log с неполными метаданными резервного копирования

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

Если резервная копия tail-log содержит неполные метаданные, в таблице has_incomplete_metadata набора резервных копий задано значение 1 . Кроме того, в выходных данных RESTORE HEADERONLY HasIncompleteMetadata задано значение 1 .

Если метаданные в резервном копировании tail-log неполны, таблица backupfilegroup отсутствует большая часть сведений о файловых группах во время резервного копирования tail-log. backupfilegroup Большинство столбцов таблицы : NULL единственные значимые столбцы:

• backup_set_id
• filegroup_id
• type
• type_desc
• is_readonly

Связанные задачи

Сведения о создании резервного копирования в журнале tail-log см. в статье Резервное копирование журнала транзакций при повреждении базы данных (SQL Server).

Связанный контент

• BACKUP (Transact-SQL)
• Инструкции RESTORE (Transact-SQL)
• Резервное копирование и восстановление баз данных SQL Server
• Резервные копии только для копирования (SQL Server)
• Резервные копии журналов транзакций (SQL Server)
• Применение резервных копий журналов транзакций (SQL Server)
• Руководство по архитектуре журнала транзакций SQL Server и управлению

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

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

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

• Перед началом: Необходимые условияБезопасность
• Для восстановления резервной копии журнала транзакций используется:Среда SQL Server Management StudioTransact-SQL
• Связанные задачи

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

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

• Резервные копии должны восстанавливаться в том же порядке, в котором они были созданы. Перед тем как можно будет восстановить определенную резервную копию журнала транзакций, вначале должны быть восстановлены следующие более ранние резервные копии без отката незафиксированных транзакций, т.е.с параметром WITH NORECOVERY:
  • Полная резервная копия и последняя разностная резервная копия, если таковая имеется, созданные перед заданной резервной копией журнала транзакций. Перед созданием самой последней полной или разностной резервной копии базы данных необходимо, чтобы в базе данных использовалась модель полного восстановления (или модель восстановления с неполным протоколированием).
  • Все резервные копии журнала транзакций, созданные после полной резервной копии базы данных или разностной резервной копии (если она восстанавливается), и перед заданной резервной копией журнала транзакций. Резервные копии журналов необходимо применять в порядке их создания, без разрывов в цепочке журналов. Дополнительные сведения о резервных копиях журналов транзакций см. в статьях «Резервное копирование журналов транзакций» (SQL Server) и применение резервных копий журналов транзакций (SQL Server).

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

  Разрешения

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

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

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

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

  1. После подключения к соответствующему экземпляру ядра СУБД Microsoft SQL Server в обозревателе объектов щелкните имя сервера, чтобы развернуть дерево сервера.
  2. Раскройте узел Базы данныхи в зависимости от типа восстанавливаемой базы данных выберите пользовательскую базу данных или раскройте узел Системные базы данных и выберите системную базу данных.
  3. Щелкните правой кнопкой мыши базу данных, выберите пункт Задачи, затем пункт Восстановитьи пункт Журнал транзакций. В результате откроется диалоговое окно Восстановление журнала транзакций .

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

  • Из предыдущих резервных копий базы данных Выберите из раскрывающегося списка базу данных для восстановления. Данный список содержит только базы данных, резервное копирование которых было выполнено в соответствии с журналом резервного копирования msdb .
  • Из файла или с ленты Нажмите кнопку обзора (. ), после чего откроется диалоговое окно Выбор устройств резервного копирования . В окне Тип носителя резервной копии выберите один из перечисленных типов устройств. Чтобы выбрать одно или несколько устройств в окне Носитель резервной копии , нажмите кнопку Добавить. После добавления нужных устройств в списке Носитель резервной копии нажмите кнопку ОК для возвращения на страницу Общие .
  • На момент времени Либо сохраните значение по умолчанию (Самый последний), либо выберите конкретную дату и время, нажав кнопку обзора и открыв диалоговое окно Восстановление на момент времени .
  • До помеченной транзакции Восстановите базу данных до помеченной транзакции. Выбор данного параметра открывает диалоговое окно Выбор помеченной транзакции , в котором отображается сетка; в ней перечислены помеченные транзакции, доступные в выбранных резервных копиях журналов транзакций. По умолчанию восстановление проводится до помеченной транзакции, не включая ее. Чтобы восстановить и помеченную транзакцию, выберите пункт Включая помеченную транзакцию. В приведенной ниже таблице перечислены заголовки столбцов сетки, а также даны описания их значений.
    

    Заголовок	значение
    	Отображает флажок для выбора маркера.
    Отметка транзакции	Имя помеченной транзакции, заданное пользователем при фиксации транзакции.
    Дата	Дата и время фиксации транзакции. Дата и время транзакции отображаются, в соответствии с данными в таблице msdbgmarkhistory , а не с датой и временем на клиентском компьютере.
    Описание	Описание помеченной транзакции, заданное пользователем при ее фиксации (при его наличии).
    Номер LSN	Регистрационный номер помеченной транзакции в журнале.
    База данных	Имя базы данных, в которой была зафиксирована помеченная транзакция.
    Имя пользователя	Имя пользователя базы данных, зафиксировавшего помеченную транзакцию.

  • Сохранить параметры репликации (WITH KEEP_REPLICATION) Сохраняет настройки репликации при восстановлении опубликованной базы данных на сервере, отличном от сервера, на котором была создана база данных. Этот параметр доступен только вместе с параметром Оставить базу данных готовой к использованию, выполнив откат незафиксированных транзакций. (описанным ниже), который эквивалентен восстановлению резервной копии с параметром RECOVERY. Проверка этого параметра эквивалентна использованию параметра KEEP_REPLICATION в инструкции Transact-SQLRESTORE .
  • Выдавать приглашение перед восстановлением каждой резервной копии Перед восстановлением каждого резервного набора данных (после первого) этот параметр вызывает диалоговое окно Продолжение восстановления , в котором выводится вопрос, нужно ли продолжать последовательность восстановления. В этом окне отображается имя следующего набора носителей (если он доступен), имя резервного набора данных и его описание. Этот параметр особенно полезен, если необходимо поочередно менять ленты для различных наборов носителей. Например, можно использовать этот параметр, когда на сервере установлено только одно ленточное устройство. Перед нажатием кнопки ОКдождитесь готовности к продолжению. Чтобы оставить базу данных в состоянии восстановления, нажмите кнопку Нет . Для удобства можно продолжить последовательность восстановления после завершения последнего восстановления. Если очередная резервная копия представляет собой резервную копию данных или разностную резервную копию, следует вновь использовать задачу Восстановление базы данных . Если очередная резервная копия представляет собой резервную копию журнала, следует использовать задачу Восстановление журнала транзакций .
  • Ограничить доступ к восстановленной базе данных (WITH RESTRICTED_USER) Доступ к восстановленной базе данных будет только у пользователей db_owner, dbcreatorили sysadmin. Проверка этого параметра является синонимом использования параметра RESTRICTED_USER в инструкции Transact-SQLRESTORE .

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

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

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

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

  1. Чтобы применить резервную копию журналов транзакций, выполните инструкцию RESTORE LOG, указав при этом:
     • Имя базы данных, к которой будет применен журнал транзакций.
     • устройство резервного копирования, с которого будет восстановлена резервная копия журналов транзакций;
     • Предложение NORECOVERY.

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

  ВОССТАНОВЛЕНИЕ ЖУРНАЛА DATABASE_NAME FROM > WITH NORECOVERY.

  Где database_name имя базы данных и > — это имя устройства, содержащего восстановление резервной копии журнала.

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

  RESTORE LOG FROM WITH RECOVERY; GO 

  RESTORE LOG FROM WITH NORECOVERY; RESTORE DATABASE WITH RECOVERY; GO 

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

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

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

  ALTER DATABASE AdventureWorks2022 SET RECOVERY FULL; 

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

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

  RESTORE DATABASE AdventureWorks2022 FROM AdventureWorks2022_1 WITH NORECOVERY; GO RESTORE LOG AdventureWorks2022 FROM AdventureWorks2022_log WITH FILE = 1, NORECOVERY; GO RESTORE DATABASE AdventureWorks2022 WITH RECOVERY; GO 

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

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

  RESTORE DATABASE AdventureWorks2022 FROM AdventureWorks2022_1 WITH NORECOVERY; GO RESTORE LOG AdventureWorks2022 FROM AdventureWorks2022_log WITH FILE = 1, NORECOVERY; GO RESTORE LOG AdventureWorks2022 FROM AdventureWorks2022_log WITH FILE = 2, NORECOVERY; GO RESTORE LOG AdventureWorks2022 FROM AdventureWorks2022_log WITH FILE = 3, NORECOVERY; GO RESTORE DATABASE AdventureWorks2022 WITH RECOVERY; GO 

  Связанные задачи

  • Создание резервной копии журнала транзакций (SQL Server)
  • Restore a Database Backup Using SSMS
  • Восстановление базы данных до точки отказа в модели полного восстановления (Transact-SQL)
  • Восстановление базы данных SQL Server до определенного момента времени (модель полного восстановления)
  • Восстановление базы данных до помеченной транзакции (среда SQL Server Management Studio)

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

  

  26.02.2020

  

  insci

  

  SQL Server

  

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

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

  Предыдущая статья Следующая статья

  Читать:

  Где ее скачать