Способ копирование базы данных

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

Применимо к: SQL Server (все поддерживаемые версии)

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

SQL Server 2016 использует путь по умолчанию, отличный от пути, использованного в предыдущих версиях. Поэтому для восстановления резервной копии базы данных, созданной в месте расположения по умолчанию для ранних версий, необходимо использовать параметр MOVE. Сведения о новом пути по умолчанию см. в разделе Расположение файлов для экземпляра по умолчанию и именованных экземпляров SQL Server. Дополнительные сведения о перемещении файлов баз данных см. в статье «Перемещение файлов баз данных» далее в этом подразделе.

Основные этапы копирования базы данных, используя функции резервного копирования и восстановления

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

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

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

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

Рассматриваются дополнительные вопросы, которые могут повлиять на процесс.

Перед восстановлением файлов базы данных

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

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

Это может быть необходимо в следующих ситуациях.

Нужная структура каталогов или сопоставление дисков, используемые на исходном компьютере, могут отсутствовать на другом компьютере. Например, возможно, резервная копия содержит файл, который нужно восстановить на диск E, но на целевом компьютере диска E — нет.

на целевом диске может быть недостаточно свободного места;

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

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

Если существующий файл не может быть перезаписан, возникнет ошибка восстановления.

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

Перемещение файлов баз данных

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

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

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

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

Дополнительные сведения см. в подразделе «Восстановление файлов и файловых групп в новое место назначения» далее в этом разделе.

Изменение имени базы данных

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

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

Обновление базы данных с помощью восстановления

При восстановлении резервных копий из предыдущей версии желательно заранее знать, существует ли на целевом компьютере путь (диск и каталог) для каждого полнотекстового каталога резервной копии. Чтобы получить список всех логических и физических имен — пути и имени файла — всех файлов резервной копии, включая файлы каталогов, выполните инструкцию RESTORE FILELISTONLY FROM Дополнительные сведения см. в разделе Инструкция RESTORE FILELISTONLY (Transact-SQL).

Если нужный путь на целевом компьютере не существует, есть два варианта.

Создать нужное сопоставление дисков или структуру каталогов на целевом компьютере.

Переместить файлы каталогов на новое место назначения во время операции восстановления с помощью предложения WITH MOVE инструкции RESTORE DATABASE. Дополнительные сведения см. в разделе RESTORE (Transact-SQL)невозможно.

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

Владелец базы данных

При восстановлении базы данных на другом компьютере имя входа пользователя SQL Server или Microsoft Windows, начавшего процесс восстановления, автоматически становится именем владельца базы данных. При восстановлении базы данных системный администратор или владелец новой базы данных могут сменить ее владельца. Для предотвращения несанкционированного восстановления базы данных устанавливайте пароли на носители или сами резервные копии.

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

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

Просмотр файлов данных и журналов в резервном наборе данных

Восстановление файлов и файловых групп в новом расположении

Восстановление файлов и файловых групп поверх существующих файлов

Восстановление базы данных с новым именем

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

Изменение владельца базы данных

Копирование базы данных с помощью управляющих объектов SQL Server (SMO)

Источник

Резервное копирование данных в MySQL

1. Копирование файлов базы

Базу данных MySQL можно скопировать, если временно выключить MySQL-сервер и просто скопировать файлы из папки /var/lib/mysql/db/. Если сервер не выключить, по очевидным причинам вероятна потеря и порча данных. Для больших нагруженных баз эта вероятность близка к 100%. Кроме того, при первом запуске с «грязной» копией базы данных MySQL-сервер начнет процесс проверки всей базы, который может затянуться на часы.

В большинстве «живых» проектов регулярное выключение сервера БД на длительное время неприемлемо. Для решения этой проблемы применяется трюк, основанный на снэпшотах файловой системы. Снэпшот — это что-то вроде «фотографии» файловой системы на определенный момент времени, сделанный без реального копирования данных (и потому быстро). Аналогичным образом работает «ленивое копирование» объектов во многих современных языках программирования.
Общая схема действий такова: блокируются все таблицы, сбрасывается файловый кэш БД, делается снэпшот файловой системы, разблокируются таблицы. После этого файлы спокойно копируются из снэпшота, после чего он уничтожается. «Блокирующая» часть такого процесса занимает время порядка секунд, что уже терпимо. В качестве расплаты на какое-то время, пока «жив» снэпшот, снижается производительность файловых операций, что в первую очередь бьет по скорости операций записи в базу.

Некоторые файловые системы, например, ZFS, поддерживают снятие снэпшотов нативно. Если вы не пользуетесь ZFS, но на вашем сервере стоит менеджер томов LVM, вы также сможете скопировать базу MySQL через снэпшот. Наконец, под *nix можно воспользоваться драйвером снэпшотов R1Soft Hot Copy, но этот способ не заработает в контейнере openvz (процесс бэкапа MySQL описан здесь).

Для баз MyISAM существует официальная бесплатная утилита mysqlhotcopy, которая «правильно» копирует файлы баз MyISAM без остановки сервера. Существует аналогичная утилита для InnoDB, но она платная, хотя и возможностей в ней больше.

Копирование файлов — самый быстрый способ перебросить базу данных целиком с одного сервера на другой.

2. Копирование через текстовые файлы

Для того, чтобы считать в бэкап данные из production-базы, необязательно дергать файлы. Можно выбрать данные запросом и сохранить их в текстовый файл. Для этого используется SQL-команда SELECT INTO OUTFILE и парная ей LOAD DATA INFILE. Выгрузка производится построчно (можно отобрать для сохранения только нужные строки, как в обычном SELECT). Структура таблиц нигде не указывается — об этом должен заботиться программист. Он также должен позаботиться о включении команд SELECT INTO OUTFILE в транзакцию, если это необходимо для обеспечения целостности данных. На практике SELECT INTO OUTFILE используется для частичного бэкапа очень больших таблиц, которые нельзя скопировать никаким другим образом.

В большинстве случаев намного более удобна созданная Игорем Романенко утилита mysqldump. Утилита mysqldump формирует файл, содержащий все SQL-команды, необходимые для полного восстановления БД на другом сервере. Отдельными опциями можно добиться совместимости этого файла с практически любой СУБД (не только MySQL), кроме того, существует возможность выгрузки данных в форматах CSV и XML. Для восстановления данных из таких форматов существует утилита mysqlimport.

Утилита mysqldump консольная. Существуют её надстройки и аналоги, позволяющие управлять бэкапом через веб-интерфейс, например, украинская тулза Sypex Dumper (их представитель zapimir есть на хабре).

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

3. Инкрементные бэкапы

Традиционно рекомендуют держать 10 бэкапов: по одному на каждый день недели, а также бэкапы двухнедельной, месячной и квартальной давности — это позволит достаточно глубоко откатиться в случае порчи каких-либо данных.
Храниться бэкапы должны точно не на том же диске, что и живая база, и не на том же сервере. На случай пожаров и прочих катаклизмов лучше всего арендовать пару юнитов в соседнем дата-центре.

Эти требования могут стать проблемой для больших баз. Прокачка бэкапа 100-гигабайтной базы по 100-мбитной сети займет часа три, на которые полностью забьет канал.
Частично решить эту проблему позволяют инкрементные бэкапы, когда полный бэкап делается, скажем, только по воскресеньям, а в остальные дни пишутся только данные, добавленные или измененные за прошедшие сутки. Сложность в том, как выявить эти самые «данные, изменившиеся за сутки».

Здесь практически вне конкуренции система Percona XtraBackup, которая содержит модифицированный движок InnoDB, анализирует двоичные логи MySQL и вытаскивает из них необходимую информацию. Почти такими же возможностями обладает платная InnoDB Hot Backup, упомянутая выше.

Общая проблема с любыми бэкапами в том, что они всегда отстают. В случае фатального сбоя основного сервера восстановить систему можно будет только с некоторым «откатом» по времени, что очень и очень разочарует её пользователей. Если в системе так или иначе были затронуты финансовые потоки, подобный «откат» может в прямом смысле влететь в копеечку.

4. Репликация

Избежать откатов призвана система репликации MySQL. Идея репликации основана на том, что кроме «главного» сервера («Мастера») постоянно работают ведомые сервера MySQL («слейвы»), которые получают инкрементные бэкапы с мастера в режиме реального времени. Таким образом, время отката уменьшается почти до сетевого лага. В случае краха Мастера можно оперативно назначить «новым Мастером» один из слейвов и перенаправить клиентов на него. Кроме того, слейвы могут обрабатывать запросы на чтение данных (SELECT-ы); это можно использовать для выполнения каких-то расчетов или снижения нагрузки на мастера. MySQL поддерживает репликацию «из коробки», процесс настройки репликации в MySQL хорошо описан юзером whisk. Существует возможность запуска конфигураций Master-Master, а с помощью внешних аппаратно-программных систем — и балансировки нагрузки между мастерами. Только не нужно забывать про ограничения, накладываемые CAP-теоремой.

Репликация — это очень здорово, только использовать её нужно по назначению. Реплика — это полная копия базы, но это не резервная копия! Очевидно же, что если на мастере выполнить DROP TABLE или UPDATE users SET password=«Haha!», изменения будут тут же скопированы на слейв, и откатить их назад станет невозможно.

Репликацию можно совместить с бэкапом на уровне файлов базы, останавливая слейв, а не мастера.

Источник

Копирование и восстановление баз данных в Microsoft SQL Server 2012 / 2008

В данной статье я постарался привести сжатые теоретические выкладки, необходимые для понимания процесса резервного копирования и создания плана резервного копирования в Microsoft SQL Server (справедливо для Microsoft SQL Server 2012 и Microsoft SQL Server 2008 R2).

0. Оглавление

1. Причины потери данных

Можно выделить 5 основных причин потери данных:

1. Программные ошибки — Возникновение условий, приводящих к аварийному завершению системы. Поскольку такие ошибки основываются на дефектах программной логики, система баз данных не может выполнить восстановление в подобных ситуациях. Поэтому восстановление должен проводить сам программист, выполнив обработку таких исключений.
2. Ошибки администратора (человеческий фактор) — Случаи, в которых пользователь с большими полномочиями может неумышленно (или умышленно) разрушить данные. Необходимо постараться создать такой режим работы, который сделает подобную ситуацию маловероятной, однако совсем исключать такую возможность нельзя.
3. Выход из строя компьютера (сбой системы) — Возникает в результате ошибок в оборудовании и программном обеспечении. В этом случае содержимое оперативной памяти компьютера может быть потерянно. В качестве защиты, можно рекомендовать использование резервного сервера, зеркальное отображение баз данных и пр.
4. Отказ дискового накопителя — Физическое разрушение жесткого диска. Рекомендуется использование технологий RAID для хранения файлов баз данных, кроме того необходимо, чтобы файлы резервных копий хранились на дисковом носителе, отличным от устройства, на котором располагаются файлы баз данных.
5. Катастрофы (пожар, наводнение, землетрясение) или кража — Обойти эту ситуацию станет возможным, если устройство, содержащее необходимую для восстановления данных информацию, будет храниться отдельно от основного оборудования и не будет потеряно в результате катастроф или краж.

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

2. Типы резервного копирования

Существует 2 режима создания резервных копий:

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

MS SQL Server поддерживает оба режима создания резервных копий.

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

Выбор модели восстановления базы данных определяет объем данных, который может быть потерян во время разрушения базы данных, а также скорость использования, размер резервной копии протокола транзакций и период времени, необходимый для резервного копирования протокола. MS SQL Server поддерживает три модели восстановления:

1. Полная модель восстановления — модель, при которой все операции записываются в протокол транзакций. Поэтому эта модель предоставляет полную защиту против сбоев внешних устройств.
   • Преимущества:
     1. Есть возможность восстановить базу данных с последней подтвержденной транзакции, которая была сохранена в файле протокола.
     2. Возможно восстановить данные на любой момент времени.
     3. Возможно восстановить данные на отметку в протоколе. Отметки в протоколе соответствуют заданной транзакции и добавляются только если эта транзакция подтверждается.
     4. Протоколируются все операции, связанные с изменением индекса. В этом случае пересоздание индекса выполняется быстрее, потому что не надо создавать их заново.
   • Недостатки:
     1. Соответствующий протокол транзакций может быть очень большим по объему, и файлы на диске, содержащие этот протокол, могут увеличиваться в размере очень быстро. В связи с этим возможно заполнение протокола транзакций.
     2. Протокол транзакций должен быть защищен от сбоев внешних устройств. По этой причине использование технологии RAID для защиты протокола транзакций строго рекомендуется.
     3. Требуется значительно больше времени на резервное копирование.
   • Заключение:
     Данная модель восстановления рекомендована для производственных баз данных, если позволяет аппаратная часть сервера баз данных.
2. Модель восстановления с неполным протоколированием — То же, что и полная модель восстановления, за тем исключением, что не ведется протоколирование массовых или bulk-операций. А резервные копии протокола транзакций содержат в этом случае результат такой операции.
   • Преимущества:
     1. Как и с полной моделью восстановления, есть возможность восстановить базу данных с последней подтвержденной транзакции, которая была сохранена в файле протокола.
     2. Возможно восстановить данные на любой момент времени, если не выполнялось объемных операций.
     3. Возможно восстановить данные на отметку в протоколе, если не было объемных операций.
     4. Объемные операции выполняются намного быстрее, чем под полной моделью восстановления, так как они не протоколируются.
     5. Для резервной копии протокола требуется гораздо меньше памяти, чем в случае полного восстановления.
   • Недостатки:
     1. Те же, что и при полной модели восстановления.
     2. Не протоколируется операции с изменением индекса. При восстановлении, индексы потребуется создать заново.
     3. Восстановление с резервной копии протокола дольше, нежели при полной модели восстановления.
     4. Нет возможности восстановить данные на момент времени или на отметку в протоколе в случае объемных операций.
   • Заключение:
     Модель восстановления с неполным протоколированием используется для производственных баз данных в тех случаях, когда периодически происходят крупномасштабные или объемные bulk-операции.
3. Простая модель восстановления — В простой модели восстановления протокол транзакций усекается, если появляется точка восстановления. Но это не означает, что вообще не существует протоколирования, содержимое протокола используется во время создания контрольной точки, где все транзакции протокола подтверждены или для них выполнен откат.
   • Преимущества:
     1. Производительность всех объемных операций очень высокая.
     2. Низкие требования к объему памяти протокола.
   • Недостатки:
     1. Данные возможно восстановить только на момент последнего резервного копирования, а значит недопустимы восстановления на конкретный момент времени или на отметку в протоколе. Все изменения с последнего резервного копирования должны быть восстановлены вручную.
   • Заключение:
     Рекомендуется использовать простую модель восстановления для производственных баз, только в тех случаях, когда сервер баз данных не обладает достаточным объемом памяти или когда аппаратная часть сервера баз данных ниже рекомендуемой.

4. Методы резервного копирования

MS SQL Server предоставляет 4 различных метода резервного копирования:

1. Полное копирование базы данных (Full) — При полном резервном копировании создается резервная копия всей базы данных целиком, она включает в себя схему всех таблиц, соответствующие файловые структуры, а также содержит все данные из этой базы на момент завершения резервного копирования. Это осуществляется благодаря тому, что полное копирование захватывает состояние базы данных на момент начала копирования. А затем, если копирование выполняется динамически, система записывает любые действия, которые имеют место в процессе создания копии.
   • Преимущества:
     Быстрая скорость восстановление базы данных.
   • Недостатки:
     Полное резервное копирование требует больше времени и требует больше пространства для хранения, чем другие методы копирования.
   • Заключение:
     Для небольших баз данных, которые можно скопировать быстро, лучше всего применять полное резервное копирование. Но для больших баз требуется кроме полных резервных копий, создавать также и дифференцированные (разностные) копии баз данных.
2. Дифференцированное (разностное) резервное копирование (Differential) — В этом случае создается копия только частей баз данных, которые менялись с момента последнего полного копирования баз данных. Эта полная резервная копия называется основой для разностной копии. Как и в случае полного резервного копирования, любые действия, имеющие место во время создания копии, также копируются.
   • Преимущества:
     Этот тип резервного копирования минимизирует время, требуемое для копирования, т. к. количество копируемых данных значительно меньше, чем при полном копировании.
   • Недостатки:
     Для восстановления необходимо загрузить сначала полную копию баз данных, а затем разностную, что занимает больше времени. И, соответственно, нет смысла применять разностное копирование без полного.
   • Заключение:
     Используется на больших базах данных в связке с полным резервным копированием.
3. Резервное копирование протокола транзакций (Transaction log) — Данный вид копирования применяется при полной модели восстановления (или при неполном протоколировании) баз данных, и учитывает только изменения, записанные в протокол транзакций. Поэтому такая форма резервного копирования не основывается на физических страницах баз данных, а только на логических операциях.
   • Преимущества:
     1. Самая быстрая скорость создания копии.
     2. В отличии от дифференцированных резервных копий, где возможно восстановить базу данных только на момент последнего копирования, позволяет восстановить базу данных на конкретный момент времени.
     3. Правильное «закрытие» протокола транзакций перед началом новой порции действий с этим протоколом. Одна из наиболее общих ошибок системы возникает, когда переполняется протокол транзакций. Если память, используемая для протокола транзакций, заполняется на 100%, то система должна остановить все выполняющие транзакции, пока память не будет освобождена. Эта проблема может быть устранена только при частом выполнении резервного копирования протокола транзакций: каждый раз происходит «закрытие» порции существующего протокола и сохранение на другом внешнем устройстве. Эта порция протокола становится повторно используемой, следовательно, система восстанавливает дисковое пространство.
   • Недостатки:
     Более долгий процесс восстановления, чем при дифференцированном резервном копировании, где требуется полная копия базы данных и последняя разностная копия, в данном случае потребуется полная копия и все существующие копии протоколов транзакций.
   • Заключение:
     Рекомендуется применять на производственных базах данных в связке с полным резервным копированием.
4. Резервное копирование файлов или файловых групп — Данный метод позволяет копировать указанные файлы баз данных вместо копирования всей базы данных. Отдельные файлы могут быть восстановлены из копии, позволяя выполнить восстановление после сбоя, который повлиял лишь на небольшое подмножество файлов баз данных.
   • Заключение:
     Копирование файлов базы данных рекомендуется выполнять только когда база данных является очень большой, и нет достаточно времени для полного копирования базы данных.

5. Какие базы данных и как часто копировать?

1. База данных masterявляется наиболее важной базой данных системы, потому что она содержит информацию обо всех базах данных в этой системе. Поэтому резервное копирование базы данных master должно происходить на регулярной основе. Кроме того, рекомендуется создавать копию каждый раз, когда выполняются действия, приводящие к модификации базы данных master. Вот некоторые из них:
   • Выполнение операторов и хранимых процедур;
   • Создание, изменение и удаление базы данных;
   • Изменения протокола транзакций.
2. Следует выполнять резервное копирование всех производственныхбаз данных на регулярной основе. Дополнительно, необходимо делать резервную копию после того как с базами данных были выполнены следующие изменения:
   • После создания базы данных;
   • После создания индексов;
   • После создания протокола транзакций;
   • После выполнения непротоколируемых операций (операции, которые не записываются в протокол транзакций).

6. Пример стратегии резервного копирования

Ниже приводится некоторый план резервного копирования, в реальной организации:

И некоторые характеристики связанные с выполнением резервного копирования базы данных DB1:

• Процессор Opteron 8220 8×1.0 Ghz
• ОЗУ 24 Гб
• HDD 250 Гб RAID 1+0

Источник