Sql server копирование файлов
У меня есть система MS SQL Server 2008 Express, которая содержит базу данных, которую я хотел бы "скопировать и переименовать" (для целей тестирования), но я не знаю простого способа достичь этого.
Я замечаю, что в версии R2 SQL Server есть мастер копирования базы данных, но, к сожалению, я не могу обновить.
база данных, о которой идет речь, находится вокруг концерта. Я попытался восстановить резервную копию базы данных, которую хочу скопировать в новую базу данных, но безуспешно.
установите Microsoft SQL Management Studio, вы можете скачать его бесплатно с веб-сайта Microsoft:
версия 2008
Microsoft SQL Management Studio 2008 является частью SQL Server 2008 Express с расширенными службами
версия 2012
клик скачать и проверьте ENU\x64\SQLManagementStudio_x64_ENU.exe
версия
клик скачать и проверьте MgmtStudio 64BIT\SQLManagementStudio_x64_ENU.exe
откройте Microsoft SQL Management Studio
щелкните правой кнопкой мыши базу данных для клонирования, нажмите кнопку Tasks , нажмите кнопку Copy Database. . Следуйте за мастером, и вы закончите.
вы можете попытаться отсоединить базу данных, скопировать файлы в новые имена в командной строке, а затем присоединить оба DBs.
в командной строке (я упростил пути к файлам для этого примера):
оказывается, я пытался восстановить из резервной копии неправильно.
Первоначально я создал новую базу данных, а затем попытался восстановить резервную копию здесь. То, что я должен был сделать, и что сработало в конце, - это открыть диалоговое окно восстановления и ввести имя новой базы данных в поле назначения.
короче говоря, восстановление из резервной копии сделало трюк.
Спасибо за все отзывы и предложения ребята
это скрипт, который я использую. Немного сложно, но это работает. Протестировано на SQL Server 2012.
используя MS SQL Server 2012, необходимо выполнить 3 основных шага:
во-первых, создать .sql файл, содержащий только структуру исходного DB
- щелкните правой кнопкой мыши на БД, а потом задачи затем Сформировать Сценарии
- следуйте за мастером и сохраните локально
во-вторых, замените исходную БД на целевую в
- щелкните правой кнопкой мыши на файл назначения, выберите Новый Запрос и Ctrl-H или (редактировать - найти и заменить - быстрая замена)
наконец, заполнить данными
- Правой Кнопкой Мыши на целевой БД, затем выберите задачи и Импорт Данных
- источник данных выпадающее значение "поставщик данных .net framework для SQL server " + установите текстовое поле строки соединения под данными ex: Data Source=Mehdi\SQLEXPRESS;Initial Catalog=db_test;User >
- сделать то же самое с пунктом
- проверьте таблицу, которую вы хотите перенести, или установите флажок " источник: . - проверить их всех!--16-->
в SQL Server 2008 R2 резервное копирование базы данных в виде файла в папку. Затем выберите опцию восстановления, которая появляется в папке "база данных". В Мастере введите новое имя, которое требуется в целевой базе данных. И выберите восстановить файл frrom и использовать файл, который вы только что создали. Я jsut сделал это, и это было очень быстро (мой DB был маленьким, но все же) Пабло.
ни одно из решений, упомянутых здесь, не работало для меня - я использую SQL Server Management Studio 2014.
вместо этого мне пришлось снять флажок "take tail-log backup before restore" на экране "параметры": в моей версии он установлен по умолчанию и предотвращает завершение операции восстановления. После снятия флажка операция восстановления прошла без проблем.
если база данных не очень велика, вы можете посмотреть команды "база данных сценариев" в SQL Server Management Studio Express, которые находятся в контекстном меню самого элемента базы данных в проводнике.
вы можете выбрать, что все для сценария; вы хотите объекты и данные, конечно. Затем вы сохраните весь сценарий в один файл. Затем вы можете использовать этот файл для повторного создания базы данных; просто убедитесь, что вверху "правильные" база данных.
другой способ, который делает трюк с помощью мастер импорта/экспорта, выберите источник-это ваш сервер с исходной базой данных, а затем в пункте назначения выберите тот же сервер с целевой базой данных (сначала вам нужно создать пустую базу данных), затем нажмите finish
он создаст все таблицы и перенесет все данные в новую базу данных,
решение, основанное на этом комментарии:https://stackoverflow.com/a/22409447/2399045 . Просто установите настройки: имя БД, папка temp, папка db files. А после запуска у вас будет копия БД с именем в формате "sourcedbname_yyyyy-mm-dd".
скрипт, основанный на ответе Джо (отсоединить, скопировать файлы, прикрепить оба).
- запустите Managment Studio как учетную запись администратора.
это не обязательно, но, возможно, ошибка отказа в доступе при выполнении.
- настройка sql server для выполнения xp_cmdshel
- запустите скрипт, но введите свои имена БД в @dbName и @copyDBName переменные перед.
вы можете просто создать новую базу данных, а затем перейти к задачам, импортировать данные и импортировать все данные из базы данных, которую вы хотите дублировать в базу данных, которую вы только что создали.
В этом разделе описывается создание резервной копии файлов и файловых групп в SQL Server с помощью среды SQL Server Management Studio, Transact-SQLили PowerShell. Если размер базы данных и требования по производительности делают полное резервное копирование базы данных нецелесообразным, можно создать резервную копию файлов. Резервная копия файлов содержит данные одного или нескольких файлов или файловых групп.
Использование Transact-SQL
Чтобы создать резервную копию файла или файловой группы, используйте инструкцию BACKUP DATABASE . В этой инструкции должны быть указаны по меньшей мере следующие данные:
Имя базы данных.
предложение FILE или FILEGROUP для каждого резервируемого файла или группы файлов;
устройство резервного копирования, на которое будет записываться полная резервная копия.
Базовая структура синтаксиса Transact-SQL для резервного копирования файлов:
BACKUP DATABASE database
< FILE = логическое_имя_файла | FILEGROUP = логическое_имя_файловой_группы > [ , . f ]
TO backup_device [ , . n ]
[ WITH with_options [ , . o ] ] ;
В рамках модели полного восстановления следует создать также резервную копию журнала. Чтобы использовать полный набор полных резервных копий файлов для восстановления базы данных, необходимо иметь достаточное количество резервных копий журнала, чтобы охватить все резервные копии файлов от начала резервной копии первого файла.
«Горячее» сохранение файлов
Большинство резервных копий современных баз данных выполняется путём копирования файлов базы данных без остановки базы. Здесь видны несколько проблем:
- В момент начала копирования содержимое базы данных может не совпадать с содержимым файлов, т. к. часть информации находится в кеше и ещё не записана на диск.
- Во время копирования содержимое базы может меняться. Если используются изменяемые структуры данных, то меняется содержимое файлов, а при использовании неизменяемых структур меняется набор файлов: новые файлы появляются, а старые удаляются.
- Поскольку запись данных в базу и чтение файлов БД никак не синхронизированы, программа резервного копирования может прочитать некорректную страницу, в которой половина будет от старой версии страницы, а другая половина – от новой.
- в 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) занимает время, иногда значительное. Даже если используются зеркальные тома или файловые системы с возможностью изготовления снимков, процесс резервного копирования не будет мгновенным.
- Вместе с файлами данных необходимо сохранить журналы начиная с момента начала подготовки к резервному копированию и заканчивая моментом возврата базы в нормальное состояние.
- Восстановиться из этой резервной копии можно на момент возврата базы в нормальное состояние. Восстановление на более ранний момент невозможно.
- Данные из памяти сбрасываются на диск.
- Фиксируется список файлов, попадающих в резервную копию. До тех пор, пока процесс резервного копирования не закончится, базе запрещено удалять эти файлы, даже если они становятся не нужны.
Рекомендации
Метод 3: Использование Powershell для создания резервных копий
Если вы не используете Powershell с SQL Server, то это того стоит. Если вы не используете модуль DBATools с SQL Server, получите его сейчас. PowerShell может делать фантастические, чудесные вещи, а DBATools может сделать для вас мощные, удивительные вещи во всем, что связано с SQL Server. Ниже простой пример использования команды DBATools Backup-DbaDatabase для создания полного бэкапа. Эта команда имеет полный набор опций, включая резервирование всех баз данных на SQL Server, если не передавать параметр -Database. Проверьте это прямо сейчас.
Ограничения
Инструкция BACKUP не разрешена в явных и неявных транзакциях.
В простой модели восстановления резервные копии файлов для чтения и записи должны создаваться вместе. Это помогает обеспечить восстановление базы данных до согласованного момента времени. Вместо того чтобы указывать каждый файл или файловую группу для чтения и записи, воспользуйтесь параметром READ_WRITE_FILEGROUPS. Этот параметр создает резервные копии всех файловых групп, доступных для чтения и записи, в базе данных. С помощью параметра READ_WRITE_FILEGROUPS создаются так называемые частичные резервные копии. См. раздел Частичное резервное копирование (SQL Server).
Дополнительные сведения об ограничениях см. в разделе Общие сведения о резервном копировании (SQL Server).
5 способов сделать резервные копии в SQL Server
В прошлый раз мы обсуждали 5 типов резервных копий. Сейчас я хочу представить вам пять способов сделать бэкап в SQL Server. Я не смогу продемонстрировать все доступные опции каждого из этих шести методов. Здесь много чего есть даже для такой простой темы как бэкапы.
Как выполнить массовую загрузку файлов в таблицу FileTable
Для массовой загрузки файлов в таблицу FileTable можно использовать различные способы.
bcp
Вызвать с предложением CHECK_CONSTRAINTS .
Отключить пространство имен FileTable и вызвать без предложения CHECK_CONSTRAINTS . Затем снова включить пространство имен FileTable.
BULK INSERT
Вызвать с предложением CHECK_CONSTRAINTS .
Отключить пространство имен FileTable и вызвать без предложения CHECK_CONSTRAINTS . Затем снова включить пространство имен FileTable.
INSERT INTO . SELECT * FROM OPENROWSET(BULK . )
Вызвать с предложением IGNORE_CONSTRAINTS .
Отключить пространство имен FileTable и выполнить вызов без предложения IGNORE_CONSTRAINTS . Затем снова включить пространство имен FileTable.
Сведения об отключении ограничений FileTable см. в разделе Управление таблицами FileTable.
Использование PowerShell
Используйте командлет Backup-SqlDatabase и укажите Files в качестве значения параметра -BackupAction . Также укажите один из следующих параметров:
Чтобы создать резервную копию определенного файла, укажите параметр -DatabaseFileString , где String ― это один или несколько файлов базы данных для резервного копирования.
Чтобы создать резервную копию всех файлов из заданной файловой группы, укажите параметр -DatabaseFileGroupString , где String ― это одна или несколько файловых групп базы данных для резервного копирования.
В следующем примере создается полная резервная копия каждого файла из вторичных файловых групп «FileGroup1» и «FileGroup2» базы данных . Резервные копии создаются в расположении резервных копий по умолчанию экземпляра сервера Computer\Instance .
Перед началом
«Холодное» сохранение файлов БД
Очевидная идея – остановить базу данных и скопировать все её файлы. Такая резервная копия называется «холодной». Способ крайне надёжный и простой, но у него есть два очевидных недостатка:
- из «холодной» резервной копии можно восстановить только то состояние базы данных, которое было в момент останова; транзакции, сделанные после рестарта базы, в «холодную» резервную копию не попадут;
- далеко не у каждой базы данных есть технологическое окно, когда базу можно остановить.
- «холодная» копия иногда должна включать в себя и журналы. Методы определения журналов, которые должны попасть в «холодную» копию, индивидуальны для каждой СУБД. Например, в Oracle необходимо скопировать так называемые online redo, то есть фиксированное количество журнальных файлов в специальном каталоге, причём даже тогда, когда база остановлена корректно. В PostgreSQL нужно сохранить все журналы начиная с журнала, содержащего последнюю контрольную точку, информация о которой содержится в управляющем файле.
- каталог базы данных может содержать достаточно большие файлы временных табличных пространств, которые не обязательно включать в резервную копию. Кстати, это замечание верно и для «горячего» резервного копирования.
Permissions
Разрешения BACKUP DATABASE и BACKUP LOG по умолчанию назначаются участникам предопределенной роли сервера sysadmin и предопределенным ролям базы данных db_owner и db_backupoperator.
Проблемы, связанные с владельцем и разрешениями у физических файлов на устройстве резервного копирования, могут помешать операции резервного копирования. SQL Server должен иметь возможность считывать и записывать данные на устройстве; учетная запись, от имени которой выполняется служба SQL Server , должна иметь разрешения на запись. Однако процедура sp_addumpdevice, добавляющая запись для устройства резервного копирования в системные таблицы, не проверяет разрешения на доступ к файлу. Проблемы физического файла устройства резервного копирования могут не проявляться до момента доступа к физическому ресурсу во время операции резервного копирования или восстановления.
Инкрементальное резервное копирование
Чтобы ускорить восстановление на точку, хотелось бы иметь возможность выполнять резервное копирование как можно чаще, но при этом не занимать лишнего места на дисках и не нагружать базу задачами резервного копирования.
Решение задачи – инкрементальное резервное копирование, то есть копирование только тех страниц данных, которые изменились с момента предыдущего резервного копирования.
Инкрементальное резервное копирование имеет смысл только для СУБД, использующих изменяемые структуры данных.
Инкремент может отсчитываться как от полной резервной копии (кумулятивная копия), так и от любой предыдущей копии (дифференциальная копия).
К сожалению, единой терминологии не существует, и разные производители используют разные термины:
Дифференциальная | Кумулятивная | |
---|---|---|
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 умеет объединять инкрементальные копии.
В отличие от многих похожих вопросов, вопрос «какой метод резервного копирования лучше» имеет однозначный ответ – лучше всего родная для используемой СУБД утилита, обеспечивающая возможность инкрементального копирования.
Для администратора БД гораздо более важными являются вопросы выбора стратегии резервного копирования и интеграция средств резервирования баз данных в корпоративную инфраструктуру. Но эти вопросы выходят за рамки данной статьи.
Пример. Перенос файлов из файловой системы в таблицу FileTable
В этом сценарии файлы хранятся в файловой системе, а в SQL Server имеется таблица метаданных, содержащая указатели на эти файлы. Необходимо переместить файлы в таблицу FileTable, затем заменить исходный путь UNC для каждого файла в метаданных на путь UNC таблицы FileTable. Функция GetPathLocator (Transact-SQL) помогает добиться этой цели.
Например, предположим, что в базе данных имеется таблица PhotoMetadata, содержащая данные о фотографиях. В этой таблице также имеется столбец UNCPath типа varchar(512), содержащий фактический UNC-путь к JPG-файлу.
Чтобы перенести файлы изображений из файловой системы в таблицу FileTable, нужно выполнить указанные ниже действия.
Создайте новую таблицу FileTable для хранения файлов. В этом примере используется имя таблицы dbo.PhotoTable, но не показан код для создания самой таблицы.
Для копирования JPG-файлов с их структурой каталогов в корневой каталог таблицы FileTable можно использовать программу xcopy или аналогичное средство.
Исправьте метаданные в таблице PhotoMetadata с помощью кода, похожего на следующий:
массовая загрузка файлов в таблицу FileTable
FileTable ведет себя как обычная таблица для массовых операций с указанными ниже квалификациями.
Таблица FileTable имеет системные ограничения, гарантирующие целостность пространства имен файлов и каталогов. Эти ограничения должны быть проверены на массовых данных, загружаемых в FileTable. Так как часть операций массовой вставки разрешает игнорировать табличные ограничения, следующие меры применяются принудительно.
В настоящее время операции массовой загрузки в таблицу FileTable, принудительно применяющие ограничения, можно выполнять, как с любой другой таблицей. В эту категорию входят следующие операции:
bcp с предложением CHECK_CONSTRAINTS;
BULK INSERT с предложением CHECK_CONSTRAINTS;
INSERT INTO . SELECT * FROM OPENROWSET(BULK . ) без предложения IGNORE_CONSTRAINTS.
Операции массовой загрузки, не применяющие принудительно ограничения, завершаются неуспешно, если системные ограничения для таблицы FileTable не были отключены. В эту категорию входят следующие операции:
bcp без предложения CHECK_CONSTRAINTS;
BULK INSERT без предложения CHECK_CONSTRAINTS;
INSERT INTO . SELECT * FROM OPENROWSET(BULK . ) с предложением IGNORE_CONSTRAINTS.
Как отключить ограничения FileTable для массовой загрузки
Для массовой загрузки файлов в таблицу FileTable без издержек по применению определенных в системе ограничений, можно временно отключить ограничения. Дополнительные сведения см. в статье Управление таблицами FileTable.
– О, никакое убежище не выдержит попадания метеорита. Но ведь у вас, как и у каждого, есть резерв, так что можете не беспокоиться.
Станислав Лем, «Звёздные дневники Ийона Тихого»
Резервным копированием называется сохранение копии данных где-то вне основного места их хранения.
Главное назначение резервного копирования – восстановление данных после их потери. В связи с этим нередко приходится слышать, что при наличии реплики базы данных с неё всегда можно восстановить данные, и резервное копирование не нужно. На самом деле резервное копирование позволяет решить как минимум три задачи, которые не могут быть решены при помощи реплики, да и реплику без резервной копии не инициализировать.
Во-первых, резервная копия позволяет восстановить данные после логической ошибки. Например, бухгалтер удалил группу проводок или администратор БД уничтожил табличное пространство. Обе операции абсолютно легитимны с точки зрения базы данных, и процесс репликации воспроизведёт их в базе-реплике.
Во-вторых, современные СУБД – весьма надёжные программные комплексы, однако изредка всё же происходит повреждение внутренних структур базы данных, после которого доступ к данным пропадает. Что особенно обидно, такое нарушение происходит обычно при высокой нагрузке или при установке какого-нибудь обновления. Но как высокая нагрузка, так и регулярные обновления говорят о том, что база данных – отнюдь не тестовая, и данные, хранящиеся в ней, ценны.
Наконец, третья задача, решение которой требует наличия резервной копии, – это клонирование базы, например, для целей тестирования.
Резервное копирование баз данных так или иначе базируется на одном из двух принципов:
- Выборка данных с последующим сохранением в произвольном формате;
- Снимок состояния файлов БД и сохранение журналов.
Метод 1: Использование графического интерфейса в SSMS для создания бэкапа
Вы попадете на страницу General Backup Menu page в SSMS. Здесь вы можете получить доступ к множеству настроек, относящихся к создаваемому бэкапу.
В выпадающем списке “Backup type” вы можете выбрать тип создаваемого бэкапа - полный, дифференциальный или журнала.
В разделе “Backup component” можно уточнить, какой бэкап будет делаться - файлов и файловых групп или базы данных (по умолчанию).
В разделе Destination (назначение) вы выбираете, где будет создан бэкап - диск (по умолчанию) или, если выбрать из списка “URL”, то на Azure. При выборе Disk вам предлагается место и имя для бэкапа. Этим местом будет каталог по умолчанию для бэкапов, указанный при установке SQL Server. Если вас не устраивает это место, просто нажмите “Remove” (удалить), а потом “Add” (добавить) для выбора места, которое вы хотите использовать. В меню “Add” можно использовать общие пути.
Раздел Media Options на “Select a Page” позволяет выбрать такие варианты, как хотите ли вы добавить этот бэкап к имеющемуся набору или начать заново.
Мне кажется, что эти опции среды перешли из прошлого, когда бэкапы записывались на физические ленты. Эти ленты тогда должны были перематываться время от времени. Не лучший способ добавлять бэкапы в набор, поскольку все файлы бэкапов добавляются в единый набор. Если что-то случится с этим набором, и он станет непригодным для использования, то и все бэкапы станут недоступными.
В разделе Reliability (надежность) вы можете установить опции “Verify backup when finished” (проверить бэкап по завершению) и “Perform checksum before writing to media.” (посчитать контрольную сумму перед записью на носитель). Эти опции увеличат время создания бэкапа, но помогут с проверкой его целостности по время записи.
В Backup Options меню “Select a page” есть одна очень важная особенность, которую следует отметить.
Здесь имеется опция, связанная со сжатием бэкапа. В более старых версиях SQL Server, например, 2005 и 2008 эта опция была доступна только для Enterprise Edition. Начиная с SQL Server 2008R2, она доступна в Standard Edition. Чтобы сделать использование сжатия по умолчанию для всех ваших бэкапов, просто выполните нижеприведенный код на вашем SQL Server. Затем, когда вы перейдете к этой опции в графическом интерфейсе SSMS, просто оставьте её установленной в “Use the default server setting.” Вам захочется сэкономить пространство, которое предлагает сжатие. Зачем использовать больше пространства на вашем отдельном хранилище бэкапов, чем это необходимо? Я имею в виду, что вы храните свои резервные копии где-то еще, а не на SQL Server, верно?!
Установив необходимые опции, просто щелкните "ОК", и SQL Server сделает вам бэкап. Вы можете также щелкнуть опцию “Script” наверху окна мастера, чтобы SQL Server показал код T-SQL, который будет исполнен. Вы сможете сохранить его в качестве примера для дальнейшего использования.
загрузить или перенести файлы в таблицу FileTable
Выбор метода загрузки или переноса файлов в таблицу FileTable зависит от того, где хранятся файлы в настоящее время.
Затем нужно обновить существующую таблицу метаданных, чтобы они указывали на новое расположение файлов.
Использование среды SQL Server Management Studio
После подключения к соответствующему экземпляру компонента Компонент SQL Server Database Engineв обозревателе объектов разверните дерево сервера, щелкнув его имя.
Раскройте узел Базы данных и в зависимости от типа восстанавливаемой базы данных выберите пользовательскую базу данных или раскройте узел Системные базы данных и выберите системную базу данных.
Щелкните правой кнопкой мыши базу данных, выберите пункт Задачи, а затем команду Создать резервную копию. Откроется диалоговое окно Резервное копирование базы данных .
В списке База данных проверьте имя базы данных. При необходимости можно выбрать другую базу данных из списка.
В списке Тип резервной копии выберите Полная или Разностная.
Для параметра Компонент резервного копирования выберите Файл и файловые группы.
В диалоговом окне Выбор файлов и файловых групп выберите файлы и файловые группы, резервные копии которых необходимо создать. Можно выбрать один или несколько отдельных файлов или установить флажок для файловой группы, чтобы автоматически выбрать все файлы в этой группе.
Оставьте имя резервного набора данных, предложенное по умолчанию в текстовом поле Имя , или введите другое имя резервного набора данных.
(Необязательно) В текстовом поле Описание введите описание резервного набора данных.
Укажите, когда истекает срок действия резервного набора данных.
Чтобы задать срок действия резервного набора данных, выберите пункт После (параметр по умолчанию) и введите срок действия набора в днях с момента его создания. Это значение может быть задано в диапазоне от 0 до 99 999 дней. Значение 0 означает, что срок действия резервного набора данных не ограничен.
Значение по умолчанию задается в параметре Срок хранения носителей резервных копий по умолчанию (дней) диалогового окна Свойства сервера (страница Параметры базы данных ). Чтобы получить доступ к этому параметру, щелкните правой кнопкой мыши на имени сервера в обозревателе объектов и выберите его свойства, затем выберите страницу Параметры базы данных .
Чтобы указать дату истечения срока действия резервного набора данных, выберите пункт На и введите дату истечения срока действия резервного набора данных.
Чтобы выбрать тип назначения резервной копии, выберите пункт Диск или Лента. Чтобы выбрать пути к 64 (или менее) дискам или ленточным накопителям, содержащим один набор носителей, нажмите кнопку Добавить. Выбранные пути отображаются в списке Сохранить на .
Чтобы удалить носитель резервной копии, выберите его и нажмите кнопку Удалить. Чтобы просмотреть содержимое носителя резервной копии, выберите его и щелкните Содержимое.
Чтобы просмотреть или выбрать дополнительные параметры, нажмите кнопку Параметры на панели Выбор страницы .
Выберите параметр Переписать носитель , указав один из следующих вариантов:
Создать резервную копию в существующем наборе носителей
Для этого параметра выберите вариант Добавить в существующий резервный набор данных или Перезаписать все существующие резервные наборы данных.
Сведения о создании резервной копии на существующем наборе носителей см. в разделе Наборы носителей, семейства носителей и резервные наборы данных (SQL Server).
(Необязательно) Выберите Проверить имя набора носителей и срок действия резервного набора данных, чтобы при выполнении операции резервного копирования проверялся срок действия набора носителей и резервного набора данных.
(Необязательно) Введите имя в текстовом поле Имя набора носителей. Если имя не указано, создается набор носителей с пустым именем. Если имя набора носителей указано, то для носителя (ленточного или дискового) проверяется совпадение введенного и существующего имени.
Если оставить имя носителя пустым и установить рядом с ним флажок для проверки, имя носителя при успешном завершении также станет пустым.
Создать резервную копию в новом наборе носителей и удалить все существующие резервные наборы данных
Для этого параметра введите имя в текстовом поле Имя нового набора носителей и при необходимости введите описание набора носителей в текстовое поле Описание нового набора носителей .
(Необязательно) В разделе Надежность можно также установить флажки:
Проверить резервную копию после завершения.
Рассчитать контрольную сумму перед записью на носитель и (необязательно) Продолжить при ошибке контрольной суммы.
При резервном копировании на накопитель на магнитной ленте (как указано в разделе Назначение страницы Общие ) активен параметр Выгрузить ленту после резервного копирования . Выбор этого параметра активирует параметр Перемотать ленту перед выгрузкой .
Параметры в разделе Журнал транзакций доступны, только если создается резервная копия журнала транзакций (это можно указать в разделе Тип резервной копии вкладки Общие ).
SQL Server 2008 Enterprise и более поздние версии поддерживают сжатие резервных копий. По умолчанию сжатие резервных копий зависит от значения параметра конфигурации сервера backup-compression default . Однако независимо от текущего значения по умолчанию на уровне сервера можно сжать резервные копии, установив параметр Сжимать резервные копии, или отказаться от сжатия резервных копий, установив параметр Не сжимать резервные копии.
Сведения о том, как просмотреть текущую настройку сжатия резервных копий по умолчанию, см. в разделе Параметр конфигурации сервера "Просмотр или настройка параметра сжатия резервных копий по умолчанию".
Примеры
В следующих примерах описано резервное копирование одного или нескольких файлов из вторичных файловых групп в базе данных Sales . База данных использует модель полного восстановления и содержит следующие вторичные файловые группы.
Файловая группа с именем SalesGroup1 , содержащая файлы SGrp1Fi1 и SGrp1Fi2 .
Файловая группа с именем SalesGroup2 , содержащая файлы SGrp2Fi1 и SGrp2Fi2 .
A. Создание резервной копии двух файлов
В следующем примере создается разностная резервная копия только файла SGrp1Fi2 из группы SalesGroup1 и файла SGrp2Fi2 из группы SalesGroup2 .
Б. Создание полной резервной копии вторичных файловых групп
В следующем примере создается полная резервная копия каждого файла в обеих вторичных файловых группах.
В. Создание разностной резервной копии файлов вторичных файловых групп
В следующем примере создается разностная резервная копия каждого файла в обеих вторичных файловых группах.
Метод 2: Использование T-SQL для создания резервной копии на SQL Server
T-SQL - проверенный и надежный метод резервного копирования баз данных. При использовании T-SQL доступно больше опций для создания бэкапов, чем при использовании графического интерфейса. Большинство этих опций являются более продвинутыми. Очень базовый пример команды backup, которая создает полную резервную копию, представлен ниже. Затем следуют примеры дифференциального бэкапа и бэкапа журнала.
Стоит отметить два параметра Buffer Count и maxtransfersize. Вы можете поэкспериментировать с этими параметрами T-SQL, чтобы ускорить создание бэкапов. Значение Buffer Count управляет числом буферов ввода/вывода, которые используются для обработки бэкапа, а maxtransfersize отвечает за то, сколько данных перемещается за один раз.
Ниже я предоставил 3 примера моих тестов, выполненных на домашнем ПК. Исходные данные buffercount и maxtransfersize были получены с помощью установки флагов 3605 и 3213 с последующим обращением к журналу ошибок после выполнения первого бэкапа. После чего я просто экспериментировал со значениями. Имейте в виду, что слишком сильное увеличение числа буферов может вызвать ошибку нехватку памяти.
Как вы можете видеть начальная пропускная способность составляла 219,412Мб/с, а прошедшее время для этой части было 39 секунд. Это были настройки по умолчанию SQL Server.
Увеличение числа буферов до 8 увеличило пропускную способность до 258,653Мб/с, и время выполнения упало примерно на 6 секунд. Сочетание второго изменения с размером maxtransfersize 4Мб увеличило пропускную способность до 270,095 и еще сократило время на 1,4 секунды. Я скинул 8 секунд времени бэкапа. Это была небольшая база данных размером около 14Гб. Для бОльших баз данных увеличение пропускной способности может дать значительную экономию времени.
Как выполнить загрузку файлов в таблицу FileTable
Ниже перечислены методы, которые можно использовать для загрузки файлов в таблицу FileTable.
Перетаскивание файлов из исходной папки в новую папку FileTable в проводнике Windows.
Применение параметров командной строки, таких как MOVE, COPY, XCOPY или ROBOCOPY, из командной строки или пакетного файла или скрипта.
Выгрузка данных
В наборе утилит, прилагающихся к любой СУБД, обязательно есть инструменты для выгрузки и загрузки данных. Данные сохраняются либо в текстовом формате, либо в двоичном формате, специфичном для конкретной СУБД. В таблице ниже приведён список таких инструментов:
Двоичный формат | Текстовый формат | |
---|---|---|
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 и т. п.);
- выгрузка сохраняет логическую структуру данных, но не сохраняет их физическую структуру – параметры физического хранения таблиц, индексы и др.; восстановление индексов при загрузке может занимать значительное время.
- высокая избирательность: можно выгрузить отдельные таблицы, отдельные поля и даже отдельные строки;
- выгруженные данные можно загрузить в базу данных другой версии, а если выгрузка сделана в текстовом формате, то и в другую базу данных.
Самым же распространённым методом резервного копирования баз данных является копирование файлов базы.
Метод 4: Использование планов обслуживания для создания резервных копий
Тут я лишь поделюсь с вами несколькими мыслями относительно использования планов обслуживания. Во-первых, планы обслуживания (Maintenance Plans) представляют собой еще один метод с графическим интерфейсом для настройки резервных копий. В этом отношении они простой способ «указать и щелкнуть» для обработки хранения резервных копий, о чем мы еще не говорили. Во-вторых, в силу природы этого метода, который позволяет вам выбрать Backups как вариант плана, а затем пройти по шагам каждую часть мастера процесса, Maintenance Plans может стать общим подходом для ИТ-профессионалов, вышедших из системных администраторов. Например, нет необходимости знать или понимать опции, представленные в мастере SSMS Backup.
Описывает процедуру загрузки или переноса файлов в таблицы FileTable.
Восстановление на точку
Резервная копия позволяет восстановить состояние базы данных на момент, когда завершилась команда возврата из режима резервного копирования. Однако авария, после которой потребуется восстановление, может произойти в любой момент. Задача восстановления состояния БД на произвольный момент называется «восстановлением на точку» (point-in-time recovery).
Чтобы обеспечить такую возможность, следует сохранять журналы БД начиная с момента окончания резервного копирования, а в процессе восстановления продолжить применять журналы к восстановленной копии. После того, как БД восстановлена из резервной копии на момент окончания копирования, состояние базы (файлов и кэшированных страниц) гарантированно корректно, поэтому особый режим журналирования не нужен. Применяя журналы до нужного момента, можно получить состояние базы данных на любую точку во времени.
Если скорость восстановления резервной копии ограничена лишь пропускной способностью диска, то скорость применения журналов обычно ограничена производительностью процессора. Если в основной базе данных изменения происходят параллельно, то при восстановлении все изменения выполняются последовательно – в порядке чтения из журнала. Таким образом время восстановления линейно зависит от того, насколько далеко точка восстановления отстоит от точки окончания резервного копирования. Из-за этого приходится довольно часто делать полные резервные копии – минимум раз в неделю для баз с небольшой транзакционной нагрузкой и до ежедневного копирования высоконагруженных баз.
Читайте также: