Sql расширение файла бэкапа
Я долго созревал, чтобы написать данную статью и выложить свое приложение. Надеюсь вам будет интересно.
О чем данная статья
По максимум постараюсь описать те нюансы, с которыми мне пришлось столкнуться в ходе разработки приложения и настройки БД.
Для описанных ниже задач можно использовать мастер планов обслуживания, но мне больше понравился такой подход. Основное преимущество описанного мною метода, что данный способ можно применять ко всем версиям MS SQL (кроме Express, там немного другой подход). План обслуживания можно переносить, но у вас должна быть соответствующая в версия MS SQL и все равно будет создан Job для запуска плана обслуживания.
Сравнить редакции MS SQL и их функционал вы можете вот здесь.
Осторожно: перфекционисты могут испытать жжение и боль при просмотре кода моего приложения, т.к. оно было написано на скорую руку и заточено под решение конкретной задачи.
- Тем, у кого MS SQL Express и нет возможности запускать с помощью Job задачи
- Тем, кто в ближайшем будущем планирует перейти с MS SQL 2008 на более новую версию и не хочет настраивать зеркалирование БД, а сразу на новой версии настроить AlwaysOn
- Тем, у кого нет средств для поднятия еще резервных серверов и приходится обходиться тем, что есть.
- У кого нет сжатых сроков на время восстановления БД. Главное – это результат
- Кому лень что-то делать
- Просто любопытным людям.
Теория о резервном копирование
- СУБД перестанет автоматически очищать журнал транзакций . Журнал будет расти до тех пор, пока не будет сделана его резервная копия. Это важный момент, администратору БД необходимо продумать вопрос о плане резервного копирования и очистки журнала. UPD: спасибо за помощь Yggaz
- Создание разностной резервной копии
- Создание полной резервной копии
1. Журнал транзакций
Журнал транзакций является критическим компонентом базы данных и в случае системного сбоя может потребоваться для приведения базы данных в согласованное состояние.
- восстановление отдельных транзакций;
- восстановление всех незавершенных транзакций при запуске SQL Server;
- накат восстановленной базы данных, файла, файловой группы или страницы до момента сбоя и т.д
Эти же операции можно проделать с помощью SSMS
2.Разностная копия БД
- Используйте разностные копии БД, если создание полной копии БД занимает большой промежуток времени
- Периодически делайте полную копию БД, чтобы уменьшить объемы создаваемых разностных копий.
- После создания полной копии БД, все предыдущие разностные копии теряют свою актуальность.
Приведу небольшой пример из практики, почему мы стали использовать разностную копию. Со временем у нашего клиента разрослась база данных до таких размеров, что создание полной резервной копии занимало 8 часов, еще несколько месяцев и возможно к началу рабочего дня не успевало бы завершиться данная операция. После перевода на разностное резервное копирование, мы сократили время с 8 часов до 2-4 минут (в зависимости от дня недели). Раз в неделю мы делали полную копию БД.
Пример SQL для создания резервной разностной копии БД с проверкой копии по завершению (отличается от полного копирования флагом DIFFERENTIAL вместо него нужно использовать NOFORMAT).
3.Системные базы данных
Помимо основной базы и связанных с ней файлов, я настоятельно рекомендую делать копии и системных баз данных. Начнем с того, что рассмотрим какие базы существуют в MS SQL. Их всего 5:
Название | Описание |
База данных master | В этой базе данных хранятся все данные системного уровня для экземпляра SQL Server. |
База данных msdb | Используется агентом SQL Server для планирования предупреждений и задач. |
База данных model | Используется в качестве шаблона для всех баз данных, создаваемых в экземпляре SQL Server. Изменение размера, параметров сортировки, модели восстановления и других параметров базы данных model приводит к изменению соответствующих параметров всех баз данных, создаваемых после изменения. |
База данных resource | База данных только для чтения. Содержит системные объекты, которые входят в состав SQL Server. Системные объекты физически хранятся в базе данных Resource, но логически отображаются в схеме sys любой базы данных. |
База данных tempdb | Рабочее пространство для временных объектов или взаимодействия результирующих наборов. |
Более подробно можете прочитать о них тут и еще вот тут.
- msdb – потому что, там хранятся настроенные задачи и другие
- master – хранятся все произведенные настройки SQL Server.
4. План бекапирования
На основе выше описанного составим наш план резервного копирования данных. Он может отличаться от того, что потребуется вам, все зависит от требований к восстановлению БД. Когда я подготавливал план, мне пришлось учесть, что необходимо восстановить данные максимально и потеря данных составляла не больше одного часа.
- Полная копия основной БД, чаще чем раз в неделю нет необходимости
- Разностная копия основной БД, каждый день
- Копии журнала транзакций основной БД, каждый час
- Копия системной БД master, раз в неделю
- Копия системной БД msdb, раз в неделю
Если создание резервной копии прошло удачно, удаляется
- старая полная резервная копия
- все старые разностные копии
- все старые журналы транзакций
5. Общие рекомендации по резервному копированию
- Используйте опцию
чтобы убедиться, что все прошло хорошо. Недостатком такого решения является то, что для больших баз данных проверка контрольной суммы может серьезно загрузить систему. - Не выполняйте резервное копирование файлов на тот же физический диск, на котором хранится база данных или протокол транзакций.
- Если вы используете MS SQL 2008 или выше, рекомендую вам использовать сжатие резервных копий средствами SQL. Следующий код включит сжатие по умолчанию:
- держите резервные копии по нескольку дней на случай, если одна из них будет повреждена – старая резервная копия лучше, чем никакой.
- Используйте DBCC CHECKDB для проверки каждой базы данных перед копированием, это своевременно предупредит вас о надвигающихся проблемах. Примечание: на практики мы использовали данную проверку, только перед выполнением полной резервной копии.
- Выполняйте периодически обновление статистики и реорганизации индексов БД
Используем приложение
- Все тексты и запросы в коде вынесены в ресурсы, мне так было проще
- При вводе параметров соединения и других настроек, они сохраняются в файл. Для Express и Standart используются разные файлы (dbStandart, udExpress) в них хранится класс UserData
- Для выполнения некоторых операций могут потребоваться права администратора
- На данный момент не работает соединение с БД под доменной учетной записью
- Программа не обладает суперкрасивым интерфейсом
1. Настройка уведомления администратора
Мне было лень каждый раз заходить на сервер и проверять, сработала ли задача или произошла какая-то ошибка. Да и хотелось иметь возможность получать другие уведомления, не только о выполнения задач.
Для данной цели используется DatabaseMail MS SQL (для версии Standart и выше)
В своем приложение я сделал специальный раздел для автоматизации данной задачи
При нажатии появится форма для заполнения информации необходимой для создания профиля рассылки писем:
Приложение автоматически настроено на стандартный 25 SMTP порт для адреса с которого отправляются письма. При необходимости его можно изменить в процедуре sysmail_add_account_sp
Пользователь и пароль требуются на случай, если у почтового сервиса настроена аутентификация.
Имя оператора в системе указывается для того, чтобы у нас нормально создался профиль в DatabaseMail. Пишите любое название, которое будет для вас понятным. Ниже приведен пример заполненной формы.
- Меняются системные параметры MS SQL.
- Создается DatabaseMail Profile
- Активируется в SQL Agente профиль
- Создается DatabaseMail Account
- Добавляется DatabaseMail Account к Database Mail Profile
- Создается DatabaseMail Operator
2.Дополнительные уведомления для администратора
- проверка целостности БД. Для проверки базы данных использовалась стандартная процедура DBCC CHECKDB.
- информирование о свободном месте в файловых группах.
- Вторая задача была реализована с помощью запроса к системной таблице dbo.sysfiles
- Вот пример данного запроса, который выполнялся к базе:
Ответ с сервера приходит на почту администратора в виде html разметки. Данный синтаксис возможен благодаря следующей стандартной функции MS SQL FOR XML.
Так же пока я искал, как преобразовать в возвращаемый результат выполнения запросов в html текст, наткнулся на следующую страницу, где человек создал целую процедуру для этих целей
Настроить эти операции можно с помощью соответствующего пункта в меню программы:
Появиться окно для указания почтового ящика, на который необходимо высылать html текст отчета:
3.Решение проблем при настройке DatabaseMail
В MS SQL 2008 я столкнулся с проблемой при настройке SQL Server Agent. Симптомы следующие, после настройки невозможно запустить SQL Agent. В основном это решается с помощью установки update на SQL сервер.
Убедитесь, что установлен sp1, а потом можно уже ставить обновление.
Если данные обновления не помогают, необходимо скачать fix. Его можно найти на данном сайте конечную ссылку не могу указать сейчас, для того что бы дойти до пакета фикса, нужно будет ответить на ряд вопросов.
Если есть проблемы с модулем DatabaseMail. После настройки данного модуля с помощью приложения, необходимо зайти в SQL Agent и просмотреть журнал событий. Если там будут ошибки «невозможно подключиться к почтовому ящику». Значит есть проблема, даже если через проверку отправляется письмо.
- Management Studio — SQL Server Agent — Properties.
- Alert System
- Уберите галочку с Enable mail profile
- Нажмите OК
- Зайдите снова и поставьте галочку
- Перезагрузите SQL Server Agent.
4.Настраиваем резервное копирование с помощью приложения для SQL Standart:
Выбираем версию Standart. Настраиваем уведомления. (см. раздел, настройки уведомления):
Соединяемся с БД, заполняя данные для соединения и указываем БД, для которой будет применяться Job:
Выбираем настройку резервного копирования:
Указываем пути для сохранения копий БД. Если указанные папки не существует, то программа попытается их создать (нужны соответствующие права).
Нажимаем сохранить и базе настраиваются соответствующие задачи. Желательно настроить для каждого бэкапа разные папки, т.к. при удалении будут удаляться все файлы с расширением bak. (см. раздел удаление копий БД)
5.Настраиваем резервное копирование с помощью приложения для SQL Express:
Так как в SQL Express отсутствует SQL Agent, задачу по автоматизации резервного копирования пришлось решить другим путем. В указанной пользователем папке создается bat файле в котором описан SQL запрос, отвечающий за создание резервной копии. В случае необходимости можно редактировать его напрямую. По мимо этого должен работать стандартный планировщик Windows, в нем создается задача, которая будет запускать раз в сутки в указанное время.
Для этого запускаем приложение. Выбираем пункт MS SQL Express:
Появляется форма для заполнения параметров:
Указываем где будет сохраняться наша копия, а также где будет лежать bat файл для создания копии базы (имя файла указывать не надо, оно будет задано автоматически). Далее указываем настройки соединение и время, когда необходимо запускать задачу.
Единственный минус данного подхода в том, что приходится храниться в открытом виде пароль для соединения с БД.
6.Удаление задач из БД.
Если необходимо удалить все задачи из БД (например, захотели изменить пути сохранения БД). Для этого используем соответствующий пункт в меню программы. Из SQL Agent будут удалены все задачи с определенным начальным префиксом (в моем случае King):
7.Удаление копий БД
В некоторых задачах, настроено удаление старых копий БД. Для этого я использую процедуру master.dbo.xp_delete_file. Пример использования: Удалит все файлы с расширением bak из указанной папки, дата создания которых превышает 14 дней.
И вот еще один более подробный пример и информация о том, какие параметры принимает данная функция.
Как восстанавливать резервные копии
Из-за нехватки времени модуль восстановления еще не реализован, возможно в будущем я его добавлю, а пока просто кратко опишу как можно будет восстановить базу.
С помощью SQL скрипта. Для восстановления базы данных используется команда RESTORE.
Если необходимо восстановить просто базу из полной копии, то достаточно выполнить следующий скрипт:
В случае, если необходимо восстановить последовательно сначала полную копию, разностные копии и журналы транзакций, тогда необходимо написать следующий SQL скрипт.
В этом разделе представлены сведения о компоненте резервного копирования SQL Server . Резервное копирование базы данных в SQL Server имеет важное значение для защиты данных. Здесь представлено описание типов резервных копий и ограничений резервного копирования. В рамках данной темы также рассмотрены устройства резервного копирования и носители данных резервных копий в SQL Server .
Использование Transact-SQL
Создайте полную резервную копию базы данных, выполнив инструкцию BACKUP DATABASE для создания полной резервной копии базы данных и указав следующее:
- имя базы данных для создания резервной копии;
- устройство резервного копирования, на которое записывается полная резервная копия базы данных.
Базовый синтаксис Transact-SQL для полной резервной копии базы данных:
BACKUP DATABASE database TO backup_device [ , . n ] [ WITH with_options [ , . o ] ] ;
< DISK | TAPE >=имя_физического_устройства_резервного_копирования
При необходимости укажите один параметр WITH или несколько. Здесь описываются некоторые основные параметры WITH. Сведения обо всех параметрах WITH см. в разделе BACKUP (Transact-SQL).
Основные параметры WITH резервного набора данных:
- : Только в версии SQL Server 2008 Enterprise и выше указано, выполняется ли команда backup compression для этой резервной копии, переопределяя значение по умолчанию на уровне сервера.
- ENCRYPTION (ALGORITHM, SERVER CERTIFICATE | ASYMMETRIC KEY) : Только для SQL Server 2014 и выше укажите используемый алгоритм шифрования, а также сертификат или асимметричный ключ для шифрования.
- ОПИСАНИЕ=< 'text' | @text_variable >: задает текст свободной формы, описывающий резервный набор данных. В этой строке может содержаться до 255 символов.
- NAME = < backup_set_name | @backup_set_name_var >: указывает имя резервного набора данных. Длина имени не может превышать 128 символов. Если имя не указано, оно остается пустым.
По умолчанию команда BACKUP добавляет резервную копию в существующий набор носителей, сохраняя существующие резервные наборы данных. Чтобы явно задать значение, используйте параметр NOINIT . Сведения о добавлении в существующие резервные наборы данных см. в разделе "Наборы носителей", "Семейства носителей" и резервные наборы данных (SQL Server)".
Чтобы отформатировать носитель резервной копии, используйте параметр FORMAT:
FORMAT [ , MEDIANAME = < media_name | @media_name_variable > ] [ , MEDIADESCRIPTION = < text | @text_variable > ]
Используйте предложение FORMAT при первом обращении к носителю или при необходимости перезаписать все существующие данные. При необходимости назначьте новому носителю имя и описание.
Будьте предельно осторожны, используя предложение FORMAT инструкции BACKUP , так как оно удаляет все резервные копии, сохраненные ранее на носителе резервных копий.
Ограничения параллелизма
SQL Server использует процесс резервного копирования в сети, что позволяет создавать резервную копию базы данных во время ее использования. Во время резервного копирования можно производить большинство операций. Например, во время создания резервной копии разрешены инструкции INSERT, UPDATE и DELETE. При попытке приступить к выполнению операции резервного копирования во время создания или удаления файла базы данных выполнение операции резервного копирования будет отложено до завершения создания или удаления либо до истечения времени ожидания.
Следующие операции запрещены во время создания резервной копии базы данных или журнала транзакций.
Операции управления файлами, такие как инструкция ALTER DATABASE с параметром ADD FILE или с параметром REMOVE FILE.
Операции сжатия базы данных или файла. Сюда же включены операции автоматического сжатия.
При попытке создать или удалить файл базы данных во время выполнения операции резервного копирования, создание или удаление завершится неудачно.
Если операция резервного копирования перекрывается операцией сжатия или управления файлами, то возникает конфликт. Независимо от того, какая из конфликтующих операций начата первой, вторая операция ждет истечения времени ожидания для первой (которое зависит от параметров сеанса). Если блокировка снимается до истечения этого времени ожидания, работа второй операции продолжается. Если разблокировки за этот период не происходит, вторая операция заканчивается неудачно.
Примеры
Для следующих примеров создайте тестовую базу данных со следующим кодом Transact-SQL:
A. Резервное копирование на дисковое устройство
В следующем примере производится резервное копирование всей базы данных SQLTestDB на диск и создание нового набора носителей с помощью параметра FORMAT .
Б. Резервное копирование на ленточное устройство
В следующем примере создается полная резервная копия базы данных SQLTestDB на ленте в дополнение к предыдущим резервными копиям.
В. Резервное копирование на логическое ленточное устройство
В следующем примере создается логическое устройство резервного копирования для ленточного накопителя. Затем показано, как производится полное резервное копирование базы данных SQLTestDB на этот накопитель.
Дополнительные сведения
После создания полной резервной копии базы данных можно создавать разностные резервные копии или резервные копии журналов транзакций.
Также можно установить флажок Резервная копия только для копирования, чтобы создать резервную копию только для копирования. Резервная копия только для копирования — это резервная копия SQL Server, которая не зависит от обычной последовательности создания традиционных резервных копий SQL Server. Дополнительные сведения см. в статье о резервных копиях только для копирования (SQL Server). Резервная копия только для копирования недоступна для типа резервной копии Разностная.
При резервном копировании на URL-адрес параметр Перезаписать носитель на странице Параметры носителя недоступен.
Рекомендации
Нельзя создать резервную копию данных, находящихся в режиме "вне сети"
Любая операция резервного копирования, которая явно или неявно ссылается на данные, находящиеся в режиме «вне сети», завершается неудачей. Ниже следуют некоторые наиболее распространенные примеры этого.
Запрашивается создание полной резервной копии, но одна файловая группа в базе данных находится в режиме «вне сети». Операция завершается неудачно, так как в полное резервное копирование неявно включены все файловые группы.
Чтобы создать резервную копию этой базы данных, можно воспользоваться созданием резервных копий файлов (или файловых групп) и задать только те файловые группы, которые находятся в режиме «в сети».
Запрашивается частичное резервное копирование, но файловые группы, доступные для чтения и записи, находятся в режиме «вне сети». Операция завершается неудачей, потому что для частичного резервного копирования запрашиваются все файловые группы, доступные для чтения и записи.
Запрашивается резервное копирование заданных файлов, но один из файлов находится в режиме «в сети». Операция завершается неудачей. Чтобы создать резервную копию файлов, находящихся в режиме «в сети», устраните из списка файлы, находящиеся в режиме «вне сети», и повторите операцию.
Обычно резервное копирование журнала проходит успешно, даже если один или несколько файлов данных недоступны. Однако если какой-нибудь файл содержит массовые изменения, сделанные в модели восстановления с неполным протоколированием, то для успешного резервного копирования необходимо, чтобы все файлы находились в режиме «в сети».
ограничения
- Инструкция BACKUP не допускается в явных и неявных транзакциях.
- Резервные копии, созданные более поздними версиями SQL Server , не могут быть восстановлены в более ранних версиях SQL Server.
Общие сведения о концепциях и задачах резервного копирования см. в разделе "Обзор резервного копирования" (SQL Server) перед продолжением.
Использование 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.
Использование среды SQL Server Management Studio
При указании задачи резервного копирования с помощью SQL Server Management Studio можно создать соответствующий скрипт Transact-SQL BACKUP, нажав кнопку "Скрипт" и выбрав назначение скрипта.
После подключения к соответствующему экземпляру Microsoft Компонент SQL Server Database Engine в обозревателе объектов разверните дерево сервера.
Разверните узел Базы данныхи выберите пользовательскую базу данных или разверните узел Системные базы данных и выберите системную базу данных.
Щелкните правой кнопкой мыши базу данных, резервную копию которой вы намерены создать, наведите указатель на пункт Задачи и выберите команду Создать резервную копию. .
В диалоговом окне Резервное копирование базы данных выбранная база данных приводится в раскрывающемся списке (ее можно изменить на любую другую базу данных на сервере).
В раскрывающемся списке Тип резервной копии выберите нужный вариант (по умолчанию выбран тип Полная).
Перед тем как выполнять разностное резервное копирование или резервное копирование журналов транзакций, необходимо произвести по крайней мере одно полное резервное копирование базы данных.
В разделе Компонент резервного копирования выберите База данных.
В разделе Назначение проверьте расположение по умолчанию для файла резервной копии (в папке ../mssql/data).
Чтобы выбрать другое устройство, можно использовать раскрывающийся список Создать резервную копию на. Щелкните Добавить, чтобы добавить объекты резервного копирования и (или) целевые объекты. Резервный набор данных можно перераспределить между несколькими файлами, чтобы повысить скорость резервного копирования.
Чтобы удалить целевой объект резервного копирования, выберите его и щелкните Удалить. Чтобы просмотреть содержимое существующего целевого объекта резервного копирования, выберите его и щелкните Содержимое.
(Необязательно) Просмотрите другие доступные параметры на страницах Параметры носителя и Параметры резервного копирования.
Чтобы начать резервное копирование, нажмите кнопку OK.
После успешного завершения резервного копирования щелкните ОК, чтобы закрыть диалоговое окно SQL Server Management Studio.
Примеры
Для следующих примеров создайте тестовую базу данных со следующим кодом 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.
Носители резервных копий: термины и определения
устройство резервного копирования
Диск или ленточное устройство, на которые записываются резервные копии SQL Server для последующего восстановления. Резервные копии SQL Server можно также записать в службу хранилища BLOB-объектов Azure, а формат URL-адреса используется, чтобы указать назначение и имя файла резервной копии. Дополнительные сведения см. в разделе Резервное копирование и восстановление SQL Server с помощью службы хранилища BLOB-объектов Microsoft Azure.
носитель данных резервной копии
Один или несколько наборов дисков или ленточных устройств, на которые записывается резервная копия.
резервный набор данных
Содержимое резервной копии добавляется на набор носителей при успешной операции резервного копирования.
семейство носителей
Резервные копии, созданные на одном устройстве без зеркального отображения или на наборе устройств с зеркальным отображением в наборе носителей
набор носителей
Упорядоченный набор носителей данных резервной копии в виде определенного количества ленточных устройств или дисков, на которые может быть записана одна или несколько операций резервного копирования, с использованием фиксированного типа и номера устройств резервного копирования.
зеркальный набор носителей
Составные копии (зеркала) набора носителей данных резервных копий.
Выгрузка данных
В наборе утилит, прилагающихся к любой СУБД, обязательно есть инструменты для выгрузки и загрузки данных. Данные сохраняются либо в текстовом формате, либо в двоичном формате, специфичном для конкретной СУБД. В таблице ниже приведён список таких инструментов:
Двоичный формат | Текстовый формат | |
---|---|---|
Oracle | DataPump Export/DataPump Import Export/Import | SQL*Plus/SQL*Loader |
PostgreSQL | pg_dump, pg_dumpall/pg_restore | pg_dump, pg_dumpall/psql |
Microsoft SQL Server | bcp | bcp |
DB2 | unload/load | unload/load |
MySQL | mysqldump, mysqlpump/mysql, mysqlimport | |
MongoDB | mongodump/mongorestore | mongoexport/mongoimport |
Cassandra | nodetool snapshot/sstableloader | cqlsh |
Текстовый формат хорош тем, что его можно редактировать или даже создавать внешними программами, а двоичный в свою очередь хорош тем, что позволяет быстрее выгружать и загружать данные за счёт распараллеливания загрузки и экономии ресурсов на преобразовании форматов.
Несмотря на простоту и очевидность идеи выгрузки данных, для резервирования нагруженных промышленных баз такой метод применяют редко. Вот причины, по которым выгрузка не подходит для полноценного резервного копирования:
- процесс выгрузки создаёт значительную нагрузку на систему-источник;
- выгрузка занимает много времени – к моменту окончания выгрузки она станет уже неактуальной;
- сделать согласованную выгрузку всей базы данных при высокой нагрузке практически невозможно, поскольку СУБД вынуждена хранить снимок своего состояния на момент начала выгрузки. Чем больше транзакций совершено с момента начала выгрузки, тем больше объём снимка (неактуальных копий данных в PostgreSQL, пространства undo в Oracle, tempdb в Microsoft SQL Server и т. п.);
- выгрузка сохраняет логическую структуру данных, но не сохраняет их физическую структуру – параметры физического хранения таблиц, индексы и др.; восстановление индексов при загрузке может занимать значительное время.
- высокая избирательность: можно выгрузить отдельные таблицы, отдельные поля и даже отдельные строки;
- выгруженные данные можно загрузить в базу данных другой версии, а если выгрузка сделана в текстовом формате, то и в другую базу данных.
Самым же распространённым методом резервного копирования баз данных является копирование файлов базы.
«Горячее» сохранение файлов
Большинство резервных копий современных баз данных выполняется путём копирования файлов базы данных без остановки базы. Здесь видны несколько проблем:
- В момент начала копирования содержимое базы данных может не совпадать с содержимым файлов, т. к. часть информации находится в кеше и ещё не записана на диск.
- Во время копирования содержимое базы может меняться. Если используются изменяемые структуры данных, то меняется содержимое файлов, а при использовании неизменяемых структур меняется набор файлов: новые файлы появляются, а старые удаляются.
- Поскольку запись данных в базу и чтение файлов БД никак не синхронизированы, программа резервного копирования может прочитать некорректную страницу, в которой половина будет от старой версии страницы, а другая половина – от новой.
- в Oracle это отдельная команда ALTER DATABASE/TABLESPACE BEGIN BACKUP;
- в PostgreSQL – функция pg_start_backup();
- в Microsoft SQL Server и DB2 подготовка к резервному копированию выполняется неявно в процессе выполнения команды BACKUP DATABASE;
- в MySQL Enterprise, Percoba Server, Cassandra и MongoDB подготовка неявно выполняется внешней утилитой – mysqlbackup, Percona XtraBackup, OpsCenter и Ops Manager соответственно.
Вот как выглядит подготовка к резервному копированию в СУБД с изменяемыми дисковыми структурами, т. е. во всех традиционных дисковых реляционных системах:
- Запоминается момент начала резервного копирования; резервная копия должна будет содержать журналы базы данных начиная с этого момента.
- Выполняется контрольная точка, то есть все изменения, которые произошли в страницах данных до запомненного момента, сбрасываются на диск. Это гарантирует, что журналы до момента начала резервного копирования при восстановлении не потребуются.
- Включается особый режим журналирования: если страница данных изменилась в первый раз после загрузки с диска, то вместо того, чтобы записывать в журнал изменения страницы, база запишет туда страницу целиком. При выполнении подготовительной процедуры все страницы вытесняются на диск, и поэтому при первом изменении блок всегда будет записан в журнал целиком. Но если в процессе резервного копирования страница снова будет вытеснена на диск, то следующее её изменение также приведёт к появлению в журнале полной копии страницы. Это гарантирует, что если вдруг при копировании файла с данными страница получится некорректной, применение журнала сделает его корректной вновь.
- Блокируется изменение заголовков файлов данных, то есть той его части, изменения которой не отражаются в журналах. Это гарантирует, что заголовок будет скопирован корректно, а потом к файлу данных корректно будут применены журналы.
По окончании резервного копирования нужно перевести базу данных обратно в обычное состояние. В Oracle это делается командой ALTER DATABASE/TABLESPACE END BACKUP, в PostgreSQL – вызовом функции pg_stop_backup(), а в других базах – внутренними подпрограммами соответствующих команд или внешних сервисов.
Вот как выглядит временнáя диаграмма процесса резервного копирования:
- Подготовка к резервному копированию (begin backup) занимает время, иногда значительное. Даже если используются зеркальные тома или файловые системы с возможностью изготовления снимков, процесс резервного копирования не будет мгновенным.
- Вместе с файлами данных необходимо сохранить журналы начиная с момента начала подготовки к резервному копированию и заканчивая моментом возврата базы в нормальное состояние.
- Восстановиться из этой резервной копии можно на момент возврата базы в нормальное состояние. Восстановление на более ранний момент невозможно.
- Данные из памяти сбрасываются на диск.
- Фиксируется список файлов, попадающих в резервную копию. До тех пор, пока процесс резервного копирования не закончится, базе запрещено удалять эти файлы, даже если они становятся не нужны.
Ограничения на операции резервного копирования
Резервное копирование может выполняться, если база данных находится в режиме «в сети» и используется. Однако действуют следующие ограничения:
Термины
создание резервных копий
Копирование данных или записей журнала из базы данных SQL Server или журнала ее транзакций на устройство для резервного копирования, например на диск, на котором создается резервная копия данных или журнала.
резервная копия
Копия данных SQL Server , используемая для восстановления данных после возникновения ошибки. Резервная копия данных SQL Server создается на уровне базы данных для одного или нескольких файлов или групп файлов. Нельзя создать резервные копии на уровне таблиц. Кроме резервной копии данных модель полного восстановления требует создания резервной копии журнала транзакций.
модель восстановления
Свойство базы данных, с помощью которого выполняется управление обслуживанием журналов транзакций в базе данных. Существует три модели восстановления: простая модель восстановления, модель полного восстановления и модель восстановления с неполным протоколированием. Модель восстановления базы данных определяет требования к резервному копированию и восстановлению.
Примеры
A. Полная резервная копия (локальная)
В следующем примере создается полная резервная копия базы данных в заданном по умолчанию расположении резервного копирования на экземпляре сервера Computer\Instance . Дополнительно в этом примере указывается параметр -BackupAction Database.
– О, никакое убежище не выдержит попадания метеорита. Но ведь у вас, как и у каждого, есть резерв, так что можете не беспокоиться.
Станислав Лем, «Звёздные дневники Ийона Тихого»
Резервным копированием называется сохранение копии данных где-то вне основного места их хранения.
Главное назначение резервного копирования – восстановление данных после их потери. В связи с этим нередко приходится слышать, что при наличии реплики базы данных с неё всегда можно восстановить данные, и резервное копирование не нужно. На самом деле резервное копирование позволяет решить как минимум три задачи, которые не могут быть решены при помощи реплики, да и реплику без резервной копии не инициализировать.
Во-первых, резервная копия позволяет восстановить данные после логической ошибки. Например, бухгалтер удалил группу проводок или администратор БД уничтожил табличное пространство. Обе операции абсолютно легитимны с точки зрения базы данных, и процесс репликации воспроизведёт их в базе-реплике.
Во-вторых, современные СУБД – весьма надёжные программные комплексы, однако изредка всё же происходит повреждение внутренних структур базы данных, после которого доступ к данным пропадает. Что особенно обидно, такое нарушение происходит обычно при высокой нагрузке или при установке какого-нибудь обновления. Но как высокая нагрузка, так и регулярные обновления говорят о том, что база данных – отнюдь не тестовая, и данные, хранящиеся в ней, ценны.
Наконец, третья задача, решение которой требует наличия резервной копии, – это клонирование базы, например, для целей тестирования.
Резервное копирование баз данных так или иначе базируется на одном из двух принципов:
- Выборка данных с последующим сохранением в произвольном формате;
- Снимок состояния файлов БД и сохранение журналов.
Сжатие резервных копий
SQL Server 2008 Enterprise и более поздние версии поддерживают сжатие резервных копий, а SQL Server 2008 и более поздние версии позволяют восстановить сжатые резервные копии. Дополнительные сведения см. в разделе Сжатие резервных копий (SQL Server).
Related tasks
Устройства резервного копирования и носители резервных копий
В этом разделе описывается создание полной резервной копии базы данных в SQL Server с помощью SQL Server Management Studio, Transact-SQL или PowerShell.
«Холодное» сохранение файлов БД
Очевидная идея – остановить базу данных и скопировать все её файлы. Такая резервная копия называется «холодной». Способ крайне надёжный и простой, но у него есть два очевидных недостатка:
- из «холодной» резервной копии можно восстановить только то состояние базы данных, которое было в момент останова; транзакции, сделанные после рестарта базы, в «холодную» резервную копию не попадут;
- далеко не у каждой базы данных есть технологическое окно, когда базу можно остановить.
- «холодная» копия иногда должна включать в себя и журналы. Методы определения журналов, которые должны попасть в «холодную» копию, индивидуальны для каждой СУБД. Например, в Oracle необходимо скопировать так называемые online redo, то есть фиксированное количество журнальных файлов в специальном каталоге, причём даже тогда, когда база остановлена корректно. В PostgreSQL нужно сохранить все журналы начиная с журнала, содержащего последнюю контрольную точку, информация о которой содержится в управляющем файле.
- каталог базы данных может содержать достаточно большие файлы временных табличных пространств, которые не обязательно включать в резервную копию. Кстати, это замечание верно и для «горячего» резервного копирования.
Инкрементальное резервное копирование
Чтобы ускорить восстановление на точку, хотелось бы иметь возможность выполнять резервное копирование как можно чаще, но при этом не занимать лишнего места на дисках и не нагружать базу задачами резервного копирования.
Решение задачи – инкрементальное резервное копирование, то есть копирование только тех страниц данных, которые изменились с момента предыдущего резервного копирования.
Инкрементальное резервное копирование имеет смысл только для СУБД, использующих изменяемые структуры данных.
Инкремент может отсчитываться как от полной резервной копии (кумулятивная копия), так и от любой предыдущей копии (дифференциальная копия).
К сожалению, единой терминологии не существует, и разные производители используют разные термины:
Дифференциальная | Кумулятивная | |
---|---|---|
Oracle | Differential | Cumulative |
PostgresPro | Incremental | — |
Microsoft SQL Server | — | Differential |
IBM DB2 | Delta | Incremental |
MySQL Enterprise | Incremental | Differential |
Percona Server | Incremental |
При наличии инкрементальных копий процесс восстановления на точку выглядит следующим образом:
- восстанавливается последняя полная резервная копия, сделанная до момента восстановления;
- поверх полной копии восстанавливаются инкрементальные копии;
- накатываются журналы с точки начала резервного копирования до точки восстановления.
Есть три способа создания инкрементальной копии:
- создание полной копии и вычисление разницы с предыдущей полной копией;
- разбор журналов, создание списка изменённых страниц и резервирование страниц, включённых в список;
- запрос изменённых страниц в базе данных.
Второй и третий способ отличаются механизмом определения списка изменённых страниц. Разбор журналов более ресурсоёмкий, плюс для его реализации необходимо знать структуру журнальных файлов. Спросить у самой базы, какие именно страницы изменились, проще всего, но для этого ядро СУБД должно иметь функциональность отслеживания изменённых блоков (block change tracking).
Впервые функциональность инкрементального резервного копирования была создана в ПО Oracle Recovery Manager (RMAN), появившемся в релизе Oracle 8i. Oracle сразу реализовал отслеживание изменённых блоков, поэтому необходимости в разборе журналов нет.
PostgreSQL не отслеживает изменённые блоки, поэтому утилита pg_probackup, разработанная российской компанией Postgres Professional, определяет изменённые страница путём анализа журнала. Однако компания поставляет и СУБД PostgresPro, которая включает расширение ptrack, отслеживающее изменение страниц. При использовании pg_probackup с СУБД PostgresPro утилита запрашивает изменённые страницы у самой базы – точно так же, как и RMAN.
Microsoft SQL Server так же, как и Oracle, отслеживает изменённые страницы, но команда BACKUP позволяет делать только полные и кумулятивные резервные копии.
В DB2 есть возможность отслеживания измененных страниц, но по умолчанию она выключена. После включения DB2 позволит делать полные, дифференциальные и кумулятивные резервные копии.
Важное отличие описанных в этом разделе средств (кроме pg_probackup) от файловых средств резервного копирования в том, что они запрашивают образы страниц у базы данных, а не читают данные с диска самостоятельно. Недостаток такого подхода – небольшая дополнительная нагрузка на базу. Однако этот недостаток с лихвой компенсируется тем, что прочитанная страница всегда корректна, поэтому нет необходимости во включении на время резервного копирования особого режима журналирования.
Ещё раз обратите внимание, что наличие инкрементальных копий не отменяет требований к наличию журналов для восстановления на произвольную точку во времени. Поэтому в промышленных базах данных журналы постоянно переписываются на внешний носитель, а резервные копии, полные и/или инкрементальные, создаются по расписанию.
Наилучшей на сегодня реализацией идеи инкрементального резервного копирования является программно-аппаратный комплекс (в терминологии Oracle – engineered system) Zero Data Loss Recovery Appliance – специализированное решение Oracle для резервного копирования собственной БД. Комплекс представляет собой кластер серверов с большим объёмом дисков, на которые установлена модифицированная версия ПО Recovery Manager и может работать как с другими программно-аппаратными комплексами Oracle (Database Appliance, Exadata, SPARC Supercluster), так и с базами Oracle на традиционной инфраструктуре. В отличие от «обычного» RMAN, в ZDLRA реализована концепция «вечного инкремента» (incremental forever). Система единственный раз создаёт полную копию базы данных, а потом делает только инкрементальные копии. Дополнительные модули RMAN позволяют объединять копии, создавая новые полные копии из инкрементальных.
К чести российских разработчиков нужно заметить, что и pg_probackup умеет объединять инкрементальные копии.
В отличие от многих похожих вопросов, вопрос «какой метод резервного копирования лучше» имеет однозначный ответ – лучше всего родная для используемой СУБД утилита, обеспечивающая возможность инкрементального копирования.
Для администратора БД гораздо более важными являются вопросы выбора стратегии резервного копирования и интеграция средств резервирования баз данных в корпоративную инфраструктуру. Но эти вопросы выходят за рамки данной статьи.
Типы резервного копирования
резервная копия, предназначенная только для копирования
Специальная резервная копия, независимая от обычной последовательности резервных копий SQL Server .
резервное копирование данных
Резервная копия данных всей базы данных (резервная копия базы данных), части базы данных (частичная резервная копия) или набора файлов данных или файловых групп (резервная копия файлов).
резервное копирование базы данных
Резервная копия базы данных. Полные резервные копии базы данных отображают состояние всей базы данных на момент завершения резервного копирования. Разностные резервные копии базы данных содержат только изменения базы данных с момента последнего полного резервного копирования.
разностная резервная копия
Резервная копия, основанная на последнем полном резервировании частичной базы данных или набора файлов данных или групп файлов ( базовая копия для разностного копирования), которая содержит только добавочные данные, измененные по сравнению с базовой копией для разностного копирования.
Частичная разностная резервная копия, включающая только те экстенты данных, которые изменились в файловых группах с момента создания предыдущей частичной резервной копии, называется основой для разностной резервной копии.
полная резервная копия
Резервная копия, которая содержит все данные заданной базы данных или наборов файлов или файловых групп, а также журналов для обеспечения возможности последующего восстановления этих данных.
резервная копия журналов
Резервная копия журналов транзакций, включающая все записи журнала, не входившие в предыдущую резервную копию журналов. (модель полного восстановления)
резервная копия файлов
Резервная копия одного или нескольких файлов или файловых групп базы данных.
частичная резервная копия
Содержит данные только из некоторых файловых групп базы данных, включая данные в первичной файловой группе, все файловые группы, доступные для чтения-записи, а также любые дополнительно указанные файлы, доступные только для чтения.
Безопасность
Для резервной копии базы данных свойству TRUSTWORTHY присваивается значение OFF. Сведения о том, как задать значение TRUSTWORTHY on, см. в разделе ALTER DATABASE SET Options (Transact-SQL).
Начиная с версии SQL Server 2012 (11.x), параметры PASSWORD и MEDIAPASSWORD при создании резервных копий не поддерживаются. Все еще вы можете восстанавливать резервные копии, созданные с паролями.
Разрешения
Разрешения BACKUP DATABASE и BACKUP LOG по умолчанию назначаются участникам предопределенной роли сервера sysadmin и предопределенным ролям базы данных db_owner и db_backupoperator.
Проблемы, связанные с владельцем и разрешениями у физических файлов на устройстве резервного копирования, могут помешать операции резервного копирования. Служба SQL Server выполняет операции чтения и записи на устройстве. Учетная запись, под которой работает служба SQL Server, должна иметь разрешения на запись на устройстве резервного копирования. Однако процедура sp_addumpdevice, добавляющая запись для устройства резервного копирования в системные таблицы, не проверяет разрешения на доступ к файлу. Проблемы с физическим файлом устройства резервного копирования могут не проявиться до тех пор, пока эта резервная копия не будет применена или не будет выполнена попытка восстановления.
Восстановление на точку
Резервная копия позволяет восстановить состояние базы данных на момент, когда завершилась команда возврата из режима резервного копирования. Однако авария, после которой потребуется восстановление, может произойти в любой момент. Задача восстановления состояния БД на произвольный момент называется «восстановлением на точку» (point-in-time recovery).
Чтобы обеспечить такую возможность, следует сохранять журналы БД начиная с момента окончания резервного копирования, а в процессе восстановления продолжить применять журналы к восстановленной копии. После того, как БД восстановлена из резервной копии на момент окончания копирования, состояние базы (файлов и кэшированных страниц) гарантированно корректно, поэтому особый режим журналирования не нужен. Применяя журналы до нужного момента, можно получить состояние базы данных на любую точку во времени.
Если скорость восстановления резервной копии ограничена лишь пропускной способностью диска, то скорость применения журналов обычно ограничена производительностью процессора. Если в основной базе данных изменения происходят параллельно, то при восстановлении все изменения выполняются последовательно – в порядке чтения из журнала. Таким образом время восстановления линейно зависит от того, насколько далеко точка восстановления отстоит от точки окончания резервного копирования. Из-за этого приходится довольно часто делать полные резервные копии – минимум раз в неделю для баз с небольшой транзакционной нагрузкой и до ежедневного копирования высоконагруженных баз.
Читайте также: