Как сделать bak файл ms sql server
Обновлено: 06.08.2024
Резервное копирование и восстановление 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):
В этом разделе описывается создание полной резервной копии базы данных в SQL Server с помощью SQL Server Management Studio, Transact-SQLили PowerShell.
Ограничения
- Инструкция BACKUP не допускается в явных и неявных транзакциях.
- Резервные копии, созданные более поздними версиями SQL Server , не могут быть восстановлены в более ранних версиях SQL Server.
Основные и дополнительные сведения о понятиях и задачах, связанных с резервным копированием см. в этой статье.
Рекомендации
безопасность
Для резервной копии базы данных свойству TRUSTWORTHY присваивается значение OFF. Дополнительные сведения о том, как задать для параметра TRUSTWORTHY значение ON, см. в разделе Параметры ALTER DATABASE SET (Transact-SQL).
Начиная с версии SQL Server 2012 (11.x), параметры PASSWORD и MEDIAPASSWORD при создании резервных копий не поддерживаются. Все еще вы можете восстанавливать резервные копии, созданные с паролями.
Permissions
Разрешения BACKUP DATABASE и BACKUP LOG по умолчанию назначаются участникам предопределенной роли сервера sysadmin и предопределенным ролям базы данных db_owner и db_backupoperator.
Проблемы, связанные с владельцем и разрешениями у физических файлов на устройстве резервного копирования, могут помешать операции резервного копирования. Служба SQL Server выполняет операции чтения и записи на устройстве. Учетная запись, под которой работает служба SQL Server, должна иметь разрешения на запись на устройстве резервного копирования. Однако процедура sp_addumpdevice, добавляющая запись для устройства резервного копирования в системные таблицы, не проверяет разрешения на доступ к файлу. Проблемы с физическим файлом устройства резервного копирования могут не проявиться до тех пор, пока эта резервная копия не будет применена или не будет выполнена попытка восстановления.
Использование среды SQL Server Management Studio
При создании задания резервного копирования с помощью среды SQL Server Management Studio вы можете сформировать соответствующий скрипт Transact-SQL BACKUP, нажав кнопку Скрипт и выбрав назначение скрипта.
После подключения к соответствующему экземпляру Microsoft Компонент SQL Server Database Engine в обозревателе объектов разверните дерево сервера.
Разверните узел Базы данных и выберите пользовательскую базу данных или разверните узел Системные базы данных и выберите системную базу данных.
Щелкните правой кнопкой мыши базу данных, резервную копию которой вы намерены создать, наведите указатель на пункт Задачи и выберите команду Создать резервную копию. .
В диалоговом окне Резервное копирование базы данных выбранная база данных приводится в раскрывающемся списке (ее можно изменить на любую другую базу данных на сервере).
В раскрывающемся списке Тип резервной копии выберите нужный вариант (по умолчанию выбран тип Полная).
Перед тем как выполнять разностное резервное копирование или резервное копирование журналов транзакций, необходимо произвести по крайней мере одно полное резервное копирование базы данных.
В разделе Компонент резервного копирования выберите База данных.
В разделе Назначение проверьте расположение по умолчанию для файла резервной копии (в папке ../mssql/data).
Чтобы выбрать другое устройство, можно использовать раскрывающийся список Создать резервную копию на. Щелкните Добавить, чтобы добавить объекты резервного копирования и (или) целевые объекты. Резервный набор данных можно перераспределить между несколькими файлами, чтобы повысить скорость резервного копирования.
Чтобы удалить целевой объект резервного копирования, выберите его и щелкните Удалить. Чтобы просмотреть содержимое существующего целевого объекта резервного копирования, выберите его и щелкните Содержимое.
(Необязательно) Просмотрите другие доступные параметры на страницах Параметры носителя и Параметры резервного копирования.
Чтобы начать резервное копирование, нажмите кнопку OK.
После успешного завершения резервного копирования щелкните ОК, чтобы закрыть диалоговое окно SQL Server Management Studio.
Дополнительные сведения
После создания полной резервной копии базы данных можно создавать разностные резервные копии или резервные копии журналов транзакций.
Также можно установить флажок Резервная копия только для копирования, чтобы создать резервную копию только для копирования. Резервная копия только для копирования — это резервная копия SQL Server, которая не зависит от обычной последовательности создания традиционных резервных копий SQL Server. Дополнительные сведения см. в разделе Резервные копии только для копирования (SQL Server). Резервная копия только для копирования недоступна для типа резервной копии Разностная.
При резервном копировании на URL-адрес параметр Перезаписать носитель на странице Параметры носителя недоступен.
Примеры
Для следующих примеров создайте тестовую базу данных со следующим кодом Transact-SQL:
A. Полное резервное копирование на диск в расположение по умолчанию
В этом примере база данных SQLTestDB будет заархивирована на диск в папку резервных копий по умолчанию.
После подключения к соответствующему экземпляру Microsoft Компонент SQL Server Database Engine в обозревателе объектов разверните дерево сервера.
Разверните элемент Базы данных, щелкните SQLTestDB правой кнопкой мыши, наведите указатель на пункт Задачи и выберите действие Создать резервную копию. .
Щелкните ОК.
После успешного завершения резервного копирования щелкните ОК, чтобы закрыть диалоговое окно SQL Server Management Studio.
Б. Полное резервное копирование на диск в нестандартное расположение
В этом примере база данных SQLTestDB будет заархивирована на диск в выбранную вами папку.
После подключения к соответствующему экземпляру Microsoft Компонент SQL Server Database Engine в обозревателе объектов разверните дерево сервера.
Разверните элемент Базы данных, щелкните SQLTestDB правой кнопкой мыши, наведите указатель на пункт Задачи и выберите действие Создать резервную копию. .
На странице Общие в разделе Назначение выберите Диск в раскрывающемся списке Создать резервную копию на: .
Щелкайте элемент Удалить, пока не будут удалены все существующие файлы резервных копий.
Введите допустимый путь и имя файла в текстовом поле Имя файла и используйте расширение .bak, чтобы упростить классификацию файла.
Щелкните ОК, а затем еще раз щелкните ОК, чтобы начать резервное копирование.
После успешного завершения резервного копирования щелкните ОК, чтобы закрыть диалоговое окно SQL Server Management Studio.
В. Создание зашифрованной резервной копии
В этом примере база данных SQLTestDB будет заархивирована с шифрованием в папку резервных копий по умолчанию.
После подключения к соответствующему экземпляру Microsoft Компонент SQL Server Database Engine в обозревателе объектов разверните дерево сервера.
Разверните узел Базы данных и узел Системные базы данных, щелкните правой кнопкой мыши базу данных master и выберите действие Создать запрос, чтобы открыть окно запроса с подключением к базе данных SQLTestDB .
Выполните приведенные ниже команды, чтобы создать главный ключ базы данных и сертификат в базе данных master .
В обозревателе объектов в узле Базы данных щелкните правой кнопкой мыши базу данных SQLTestDB , наведите указатель на пункт Задачи и выберите действие Создать резервную копию. .
На странице Параметры носителя в разделе Перезапись носителя выберите Создать резервную копию в новом наборе носителей и удалить все существующие резервные наборы данных.
На странице Параметры резервного копирования в разделе Шифрование установите флажок Зашифровать резервную копию .
В раскрывающемся списке "Алгоритм" выберите AES 256.
В раскрывающемся списке Сертификат или асимметричный ключ выберите MyCertificate .
Щелкните ОК.
Г. Резервное копирование в службу хранилища BLOB-объектов Azure
В приведенном ниже примере создается полная резервная копия базы данных SQLTestDB в службе "Хранилище BLOB-объектов Azure". В этом примере предполагается, что у вас уже есть учетная запись хранения с контейнером BLOB-объектов. В примере создается подписанный URL-адрес, и если у контейнера уже есть подписанный URL-адрес, операция завершится сбоем.
Если у вас нет контейнера BLOB-объектов Azure в учетной записи хранения, создайте его перед продолжением работы. Дополнительные сведения см. в статье Создание учетной записи хранения и разделе Создание контейнера.
После подключения к соответствующему экземпляру Microsoft Компонент SQL Server Database Engine в обозревателе объектов разверните дерево сервера.
Разверните элемент Базы данных, щелкните SQLTestDB правой кнопкой мыши, наведите указатель на пункт Задачи и выберите действие Создать резервную копию. .
На странице Общие в разделе Назначение выберите URL-адрес в раскрывающемся списке Создать резервную копию на: .
Если ранее вы зарегистрировали контейнер службы хранилища Azure, который хотите использовать с SQL Server Management Studio, то выберите его. В противном случае щелкните Создать контейнер, чтобы зарегистрировать новый контейнер.
В диалоговом окне Соединение с подпиской Майкрософт войдите в свою учетную запись.
В текстовом поле с раскрывающимся списком Выберите учетную запись хранения выберите свою учетную запись хранения.
В текстовом поле с раскрывающимся списком Выбрать контейнер BLOB-объектов выберите контейнер больших двоичных объектов.
В поле календаря с раскрывающимся списком Политика срока действия подписанных URL-адресов выберите дату окончания срока действия для политики общего доступа, создаваемой в этом примере.
Щелкните Создать учетные данные, чтобы создать подписанный URL-адрес и учетные данные в SQL Server Management Studio.
Щелкните ОК, чтобы закрыть диалоговое окно Соединение с подпиской Майкрософт.
В текстовом поле Файл резервной копии при необходимости измените имя файла резервной копии.
Щелкните ОК, чтобы закрыть диалоговое окно Выбор места назначения резервной копии.
Чтобы начать резервное копирование, нажмите кнопку OK.
После успешного завершения резервного копирования щелкните ОК, чтобы закрыть диалоговое окно SQL Server Management Studio.
Использование Transact-SQL
Создайте полную резервную копию базы данных, выполнив инструкцию BACKUP DATABASE для создания полной резервной копии базы данных и указав следующее:
- имя базы данных для создания резервной копии;
- устройство резервного копирования, на которое записывается полная резервная копия базы данных.
Базовая структура синтаксиса Transact-SQL для полного резервного копирования базы данных:
BACKUP DATABASE database TO backup_device [ , . n ] [ WITH with_options [ , . o ] ] ;
< DISK | TAPE >= physical_backup_device_name
При необходимости укажите один параметр WITH или несколько. Здесь описываются некоторые основные параметры WITH. Сведения о всех параметрах WITH см. в разделе BACKUP (Transact-SQL).
Основные параметры WITH резервного набора данных:
- : Только в версии SQL Server 2008 Enterprise и выше указано, выполняется ли команда backup compression для этой резервной копии, переопределяя значение по умолчанию на уровне сервера.
- ENCRYPTION (ALGORITHM, SERVER CERTIFICATE | ASYMMETRIC KEY) : Только для SQL Server 2014 и выше укажите используемый алгоритм шифрования, а также сертификат или асимметричный ключ для шифрования.
- DESCRIPTION=< 'text' | @text_variable >: Задает произвольное текстовое описание резервного набора данных. В этой строке может содержаться до 255 символов.
- NAME = < имя_резервного_набора_данных | @переменная_резервного_набора_данных > : Указывает имя резервного набора данных. Длина имени не может превышать 128 символов. Если имя не указано, оно остается пустым.
По умолчанию команда BACKUP добавляет резервную копию в существующий набор носителей, сохраняя существующие резервные наборы данных. Чтобы явно задать значение, используйте параметр NOINIT . Сведения о присоединении к существующим резервным наборам данных см. в разделе Наборы носителей, семейства носителей и резервные наборы данных (SQL Server).
Чтобы отформатировать носитель резервной копии, используйте параметр FORMAT:
FORMAT [ , MEDIANAME = < media_name | @ media_name_variable > ] [ , MEDIADESCRIPTION = < text | @ text_variable > ]
Используйте предложение FORMAT при первом обращении к носителю или при необходимости перезаписать все существующие данные. При необходимости назначьте новому носителю имя и описание.
Будьте предельно осторожны, используя предложение FORMAT инструкции BACKUP , так как оно удаляет все резервные копии, сохраненные ранее на носителе резервных копий.
Примеры
Для следующих примеров создайте тестовую базу данных со следующим кодом Transact-SQL:
A. Резервное копирование на дисковое устройство
В следующем примере производится резервное копирование всей базы данных SQLTestDB на диск и создание нового набора носителей с помощью параметра FORMAT .
Б. Резервное копирование на ленточное устройство
В следующем примере создается полная резервная копия базы данных SQLTestDB на ленте в дополнение к предыдущим резервными копиям.
В. Резервное копирование на логическое ленточное устройство
В следующем примере создается логическое устройство резервного копирования для ленточного накопителя. Затем показано, как производится полное резервное копирование базы данных SQLTestDB на этот накопитель.
Использование PowerShell
Используйте командлет Backup-SqlDatabase . Чтобы явно указать, что это полная резервная копия базы данных, задайте параметр -BackupAction со значением Database, которое используется по умолчанию. Данный параметр является необязательным для полных резервных копий баз данных.
Для этих примеров требуется модуль SqlServer. Чтобы определить, установлен ли он, выполните команду Get-Module -Name SqlServer . Чтобы установить его, выполните команду Install-Module -Name SqlServer в сеансе PowerShell с правами администратора.
Дополнительные сведения см. в статье SQL Server PowerShell Provider.
При открытии окна PowerShell из SQL Server Management Studio для подключения к установке SQL Server учетные данные можно опустить, так как для установки подключения между PowerShell и экземпляром SQL Server автоматически используются ваши учетные данные в SSMS.
Примеры
A. Полная резервная копия (локальная)
В следующем примере создается полная резервная копия базы данных <myDatabase> в заданном по умолчанию расположении резервного копирования на экземпляре сервера Computer\Instance . Дополнительно в этом примере указывается параметр -BackupAction Database.
В этой статье мы рассмотрим, как настроить резервное копирование баз данных в 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. Для разовых операций прекрасно подойдет графический инструмент SQL Management Studio. Для автоматизации — Powershell или cmd. Данные операции применяются к любым базам, как для 1С, так и любых других приложений.
С помощью графического интерфейса
Открываем MS SQL Management Studio. Кликаем правой кнопкой мыши по базе, для которой хотим сделать резервную копию - Задачи - Создать резервную копию:
В открывшемся окне оставляем полный тип копий и путь к резервному файлу (при необходимости, можно его поменять, удалив и создав снова. Можно указать как локальный диск, так и сетевой):
С помощью командной строки (cmd)
Данный способ удобно использовать для автоматизации резервного копирования. Более того, команды подходят как для Windows, так и Linux. Выполняется при помощи утилиты sqlcmd.
Пример готового скрипта
@echo off
set dd=%DATE:
3,2%
set yyyy=%DATE:
6,4%
set curdate=%dd%-%mm%-%yyyy%
set username=sa
set password=my_pass
* в данном примере мы подключаемся к локальному SQL серверу под учетной записью sa с паролем my_pass и делаем резервную копию баз work1 и work2. Резервные копии размещаем по пути D:\Backup\MSSQL. Имя файлов резервных копий work1_<текущая дата>.bak и work2_<текущая дата>.bak
* некоторые опции могут не работать, в зависимости от используемой редакции MS SQL.
Для автоматизации скрипта, создайте задание в планировщике, чтобы скрипт запускался по расписанию.
Типы резервных копий
Хорошей практикой является создание разных типов копий:
1) Полное копирование — резервирование всей базы. Выполняется командой, рассмотренной выше, например:
* в данном примере мы подключаемся к локальному серверу под пользователем sa с паролем my_pass и делаем полную копию базы work1; саму копию сохраняем в виде файла D:\Backup\MSSQL\bak_full.bak.
2) Разностное (дифференциальное) — резервирование базы данных с момента создания последней полной копии. Выполняется командой для резервного копирования с добавлением опции DIFFERENTIAL:
3) Инкрементальное или копирование логов. Выполняется Transact-SQL:
* обратите внимение, команда похожа на команду для полного резервного копирования — вместо DATABASE пишем LOG.
С помощью Powershell
Данный способ может быть не доступен на старых системах. В остальном, стоит придерживаться именно такого способа резервного копирования.
Для выполнения команды, сначала импортируем модуль:
import-module sqlps -DisableNameChecking
Backup-SqlDatabase -ServerInstance <имя SQL сервера> -Database <имя базы> -BackupFile <путь к файлу с резервной копией>
Пример скрипта на powershell
$server = "SQL01"
$curdate = Get-Date -Format yyyyMMdd
import-module sqlps -DisableNameChecking
$db = work1
Backup-SqlDatabase -ServerInstance $server -Database $db -BackupFile $db_$curdate.bak
* где выполняется резервное копирования базы work1 на сервере SQL01
Также как и для cmd, данный скрипт можно поместить в планировщик для запуска по расписанию.
Срок действия резервного набора данных
Данная настройка позволяет указать, через какой промежуток времени резервную копию можно удалить (перезаписать). Важно понимать, что настройка не влияет на сам период восстановления — если срок истек, восстановиться из набора можно.
Задать параметр можно в основном окне при создании резервной копии:
Путь расположения резервных копий
Все резервные копии по умолчанию будут попадать в каталог резервных копий. Чтобы его посмотреть и поменять, при необходимости, выполняем следующее.
Кликаем правой кнопкой мыши по корневому разделу SQL Server и выбираем свойства:
Переходим в раздел Параметры баз данных (1) - в подразделе «Места хранения, используемые базой данных по умолчанию» мы увидим путь до места размещения резервных копий (2), который можно поменять кнопкой справа (3):
Читайте также: