Как создать лог бэкап в ms sql

Руководство по тех. обслуживанию

Резервное копирование — процесс создания копии данных на носителе (жестком диске и т. д.), предназначенном для восстановления данных в оригинальном или новом месте их расположения в случае их повреждения или разрушения. Создание резервных копий баз данных SQL Server, выполнение проверочных процедур восстановления резервных копий и хранение резервных копий в безопасном месте вне рабочей площадки помогают предотвратить возможную необратимую потерю данных. Резервное копирование — единственный способ защитить данные!

Резервное копирование «Первой Формы» состоит из двух частей:

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

• C:\inetpub\wwwroot (всей папки wwwroot)

old_icon

• C:\Program Files (x86)\TCJobService (всей папки TCJobService) не используется, начиная с версии 2.215

2. Резервное копирование баз данных осуществляется встроенными инструментами и процедурами сервера MS SQL. «Первой Формой» используются два типа баз данных, подлежащих регулярному резервному копированию:

• D10Task — база данных, содержащая все сущности приложения: задачи, пользователи, прочие данные (кроме файлов).

• TaskFilesDB — файловая база данных, содержащая хранящиеся в приложении файлы. Файловых баз может быть несколько, и по мере появления их необходимо добавлять в процедуру резервного копирования.

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

Чтобы определить название базы данных посмотрите на строку DB в верхней части панели администрирования «Первой Формы» :

backup1_n

Имя БД приложения.

Чтобы определить название файловой базы данных, в интерфейсе администратора «Первой Формы» перейдите в раздел Системные настройки — Провайдеры загружаемых файлов . В списке могут содержаться несколько строк, каждая из которых описывает подключение к отдельной базе данных. Щелкните по строке, чтобы открыть окно с настройками провайдера, и в параметре Строка подключения проверьте название базы данных в выражении вида:

» initial catalog=TaskFilesDB » ( где TaskFilesDB — название файловой базы данных).

backup2

Название файловой БД.

warning_icon

База данных должна быть в режиме FULL. Нахождение БД в режиме SIMPLE не дает возможности восстановить БД на заданный момент времени и не рекомендуется к использованию на рабочей («боевой») БД.

Рекомендации по резервному копированию БД SQL

• Full backup — Полное копирование БД. Не реже чем 1 раз в неделю (лучше каждую ночь)

BACKUP DATABASE [D10Task] TO DISK = N’\\backup-01\D10Task\D10Task_backup_2019_03_24.bak’ WITH RETAINDAYS = 14, NOFORMAT, NOINIT, NAME = N’D10Task_backup_2019_03_24′, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10

• Diff backup — Инкрементальное копирование БД. Не реже чем 1 раз в день, кроме дня когда делается Full backup (лучше чаще)

BACKUP DATABASE [D10Task] TO DISK = N’\\backup-01\D10Task\D10Task_backup_2019_03_24_10.bakdiff’ WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N’D10Task_backup_2019_03_24_10′, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10

• Log backup — Копирование журналов. Не реже чем 1 раз в час, кроме часа когда делаются Full и Diff backup

BACKUP LOG [D10Task] TO DISK = N’\\backup-01\D10Task\D10Task_backup_2019_03_24_11.trn’ WITH NOFORMAT, NOINIT, NAME = N’D10Task_backup_2019_03_24_11′, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10

Log backup «отрезает» журнал (лог) БД, что обеспечивает защиту от «распухания» Transaction Log.

warning_icon

Резервное копирование баз данных рекомендуется выполнять средствами, предоставленными официальными поставщиками ваших back-up устройств, либо при помощи встроенного планировщика заданий SQL Server Agent (в версии Express этот функционал недоступен).

warning_icon

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

warning_icon

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

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

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

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

ограничения

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

Разрешения

Нужные разрешения BACKUP DATABASE и BACKUP LOG назначаются по умолчанию членам предопределенной роли сервера sysadmin и предопределенным ролям базы данных db_owner и db_backupoperator. Перед началом убедитесь в наличии необходимых разрешений.

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

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

После подключения к соответствующему экземпляру ядра СУБД SQL Server в обозревателе объектов щелкните имя сервера, чтобы развернуть дерево сервера.
Раскройте узел Базы данныхи в зависимости от типа восстанавливаемой базы данных выберите пользовательскую базу данных или раскройте узел Системные базы данных и выберите системную базу данных.
Щелкните правой кнопкой мыши базу данных, выберите пункт Задачи, а затем команду Создать резервную копию. Откроется диалоговое окно Резервное копирование базы данных .
В списке База данных проверьте имя базы данных. При необходимости можно выбрать другую базу данных из списка.
Убедитесь в том, что используется либо модель восстановления FULL , либо BULK_LOGGED.
Выберите Журнал транзакций в списке Тип резервного копирования.
(Необязательно) Выберите вариант Копировать только резервные копии, чтобы создать резервную копию только для копирования. Резервная копия только для копирования — это резервная копия SQL Server, которая не зависит от последовательности обычных резервных копий SQL Server, см. статью «Резервные копии только для копирования» (SQL Server).

Заметка Если выбран параметр Разностная , то резервную копию только для копирования создать не удастся.

Чтобы задать срок действия резервного набора данных, выберите пункт После (параметр по умолчанию) и введите срок действия набора в днях с момента его создания. Это значение может быть задано в диапазоне от 0 до 99 999 дней. Значение 0 означает, что срок действия резервного набора данных не ограничен. Значение по умолчанию задается в параметре Срок хранения носителей резервных копий по умолчанию (дней) диалогового окна Свойства сервера (страницаПараметры базы данных ). Для этого щелкните правой кнопкой мыши имя сервера в обозревателе объектов и выберите его свойства, затем страницу Параметры базы данных .
Чтобы указать дату истечения срока действия резервного набора данных, выберите пункт Наи введите дату истечения срока действия резервного набора данных.

Создать резервную копию в существующем наборе носителей Для этого параметра нажмите кнопку «Добавить к существующему резервному набору» или перезаписать все существующие резервные наборы данных, см. раздел «Наборы носителей», «Семейства носителей» и «Резервные наборы данных» (SQL Server).
- (Необязательно) Выберите Проверить имя набора носителей и срок действия резервного набора данных, чтобы при выполнении операции резервного копирования проверялся срок действия набора носителей и резервного набора данных.
- (Необязательно) Введите имя в текстовом поле Имя набора носителей. Если имя не указано, создается набор носителей с пустым именем. Если имя набора носителей указано, то для носителя (ленточного или дискового) проверяется совпадение введенного и существующего имени.
Если оставить имя носителя пустым и установить рядом с ним флажок для проверки, имя носителя при успешном завершении также станет пустым.
- Проверить резервную копию после завершения.
- Рассчитать контрольную сумму перед записью на носитель и (необязательно) Продолжить при ошибке контрольной суммы. Сведения о контрольных суммах см. в разделе «Возможные ошибки мультимедиа во время резервного копирования и восстановления» (SQL Server).
- Для повседневного резервного копирования журналов оставьте вариант по умолчанию Обрезать журнал транзакций путем удаления неактивных записей.
- Для создания резервной копии заключительного фрагмента журнала (активного журнала) включите параметр Выполнять резервное копирование заключительного фрагмента журнала, оставляя базу данных в состоянии восстановления. Резервное копирование заключительного фрагмента журнала выполняется после сбоя, чтобы предотвратить потерю сделанной работы. Резервное копирование активного журнала (резервное копирование заключительного фрагмента журнала) следует выполнять как после сбоя, так и перед началом восстановления базы данных, а также при сбое базы данных-получателя. Выбор этого параметра равносилен применению параметра NORECOVERY в инструкции BACKUP LOG языка Transact-SQL. Дополнительные сведения о резервных копиях заключительного фрагмента журнала см. в разделе Резервные копии заключительного фрагмента журнала (SQL Server).
- AES 128
- AES 192
- AES 256
- Triple DES
Использование Transact-SQL

Выполните инструкцию BACKUP LOG для создания резервной копии журнала транзакций, указав следующее:
- имя базы данных, которой принадлежит журнал транзакций, резервную копию которого необходимо создать;
- Устройство резервного копирования, на которое записывается резервная копия журнала транзакций.
В этом примере используется база данных AdventureWorks2022 , которая опирается на простую модель восстановления. Чтобы разрешить создание резервных копий журналов, перед созданием полной резервной копии база данных должна быть настроена на использование модели полного восстановления.

В этом примере создается резервная копия журнала транзакций для базы данных AdventureWorks2022 на созданном ранее устройстве резервного копирования, имеющая имя MyAdvWorks_FullRM_log1 .
```
BACKUP LOG AdventureWorks2022 TO MyAdvWorks_FullRM_log1; GO 
```
Использование PowerShell

Настройка и использование поставщика SQL Server PowerShell. Используйте командлет Backup-SqlDatabase и укажите Log в качестве значения параметра -BackupAction .

В следующем примере создается полная резервная копия журналов базы данных в заданном по умолчанию расположении резервного копирования на экземпляре сервера Computer\Instance .
```
Backup-SqlDatabase -ServerInstance Computer\Instance -Database -BackupAction Log 
```
Связанные задачи
- Восстановление резервной копии журнала транзакций (SQL Server)
- Восстановление базы данных SQL Server до определенного момента времени (модель полного восстановления)
- Устранение неполадок при переполнении журнала транзакций (ошибка SQL Server 9002)
Резервные копии журналов (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 и управлению
Километры логов и восстановление баз данных на MS SQL

Если вы используете SQL Server, то, вероятно, слышали про Полную и Простую модель восстановления баз данных. Вы, возможно, знаете, что Простая модель позволяет восстановить данные только на момент создания резервной копии, в то время как Полная — на любой момент времени, надо лишь регулярно делать резервные копии журнала транзакций. Однако, для восстановления данных при Полной модели потребуется «накатить» резервные копии журналов транзакций в определенной последовательности. Это можно без проблем сделать с помощью SSMS, но только на том SQL Server, где создавались резервные копии. Для восстановления на другом сервере потребуется вручную написать T-SQL скрипт. И чем длиннее будет цепочка резервных копий, тем больше будет сам скрипт и тем больше времени уйдет на его создание. По этой же причине администраторы редко используют уже созданные резервные копии, когда требуется развернуть копию базы на другом SQL Server, и предпочитают создавать свежий полный бэкап. Но такая процедура может быть настоящей проблемой для больших баз данных из-за высокой нагрузки на сервер. Кроме этого, если сервер «упал», то, как правило, нет времени писать длинный T-SQL скрипт для восстановления. В такие моменты нужно делать все максимально быстро и без лишней нервотрепки.

В интернете, в том числе и на Хабре (например, тут), можно найти различные способы, решающие задачу автоматизированного построения T-SQL скрипта восстановления. В основном это различные скрипты, базирующиеся на названиях файлов резервных копий или запросы на сервер-источник к истории резервных копий (к базе msdb). В этой статье я хотел бы сделать обзор возможностей XML-планов восстановления, которые появились в Quick Maintenance & Backup for MS SQL начиная с версии 1.6.

Обзор самой утилиты можно почитать в статье по этой ссылке или на официальном сайте. Наличие XML-плана восстановления в сетевой папке вместе с резервными копиями позволит не тратить время на подготовку T-SQL скрипта. Какая бы длинная ни была цепочка резервных копий, вы в несколько кликов восстановите базу данных на другом SQL Server. Также это можно делать по расписанию, на тестовом или рабочем сервере, например, для проверки всей цепочки резервных копий или актуализации копий баз данных.

Что такое XML-план восстановления

XML-план восстановления — это XML-файл, в котором перечислены имена файлов резервных копий в последовательности, необходимой для восстановления одной или нескольких баз данных. Пример содержимого XML-файла:

Пример
```
  1 London 10 Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 2016-02-16T17:00:04.65625+03:00  Northwind 2016-02-16T17:00:02  London\Northwind\Full\20160216_155457_London_Northwind_Full.bak Full 1 2016-02-16T15:54:57 58000000021900037 58000000023500001  London\Northwind\Diff\20160216_162546_London_Northwind_Diff.bak Differential 1 2016-02-16T16:25:47 58000000024300034 58000000025800001  London\Northwind\Log\20160216_163000_London_Northwind_Log.trn Log 1 2016-02-16T16:30:01 58000000024300001 58000000025800001  London\Northwind\Log\20160216_170001_London_Northwind_Log.trn Log 1 2016-02-16T17:00:02 58000000025800001 58000000025800001     
```
XML-файл всегда размещается в корневой папке с резервными копиями и содержит относительные пути до файлов резервных копий. Такая организация позволяет не терять актуальность после копирования файлов в другое место, например, в сетевую папку.

Создание XML-плана

Программа позволяет создавать XML-план двумя способами:
Перед созданием XML-файла программа определит последовательность резервных копий по информации, хранимой в системной базе mdsb, аналогично тому, как это делает SQL Server Management Studio. Для первого и второго способов XML-план будет содержать последовательность резервных копий, необходимых для восстановления базы данных на последнее возможное состояние.

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

Восстановление по XML-плану

Восстановление баз данных по XML-плану может выполняться двумя способами:

1. Вручную. Для этого в программе имеется специальное окно, которое вызывается командой «Восстановить по XML-плану» из контекстного меню в древовидном списке серверов.

На форме нужно выбрать XML-файл и базы данных, резервные копии которых будут восстановлены. Обратите внимание, восстанавливать можно в одноименные базы данных, во временную, либо в указанную базу данных. Режим восстановления во временную базу данных удобен для проверки цепочки резервных копий. Режим в Указанную базу данных может быть полезен, если требуется восстановить в определенную базу данных, например, с нестандартным размещением её файлов на дисках. По кнопке «Показать Т-SQL» можно просмотреть сформированный T-SQL скрипт, который будет запущен для восстановления.

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

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

Заключение

Механизм XML-планов восстановления в QMB — это отличная возможность, позволяющая значительно облегчить жизнь администраторам при восстановлении данных с километровыми логами, переносе баз на другой SQL Server и проверке бэкапов. Механизм можно задействовать даже в тех случаях, когда для резервного копирования используется стандартный План обслуживания. В дальнейшем мы планируем подготовить статью, о том как это сделать с помощью программы.

Если вы уже используете QMB и не задействовали эту возможность, то скорее включайте XML-план восстановления! С удовольствием ответим на ваши вопросы в комментариях или по электронной почте support@qmbsql.ru
- Блог компании СофтЛаб
- Microsoft SQL Server
Читать:
Как обойти бан в чате телеграм