T 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 скрипт.
Описывает процедуру загрузки или переноса файлов в таблицы 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 зависит от того, где хранятся файлы в настоящее время.
Затем нужно обновить существующую таблицу метаданных, чтобы они указывали на новое расположение файлов.
Метод 3: Использование Powershell для создания резервных копий
Если вы не используете Powershell с SQL Server, то это того стоит. Если вы не используете модуль DBATools с SQL Server, получите его сейчас. PowerShell может делать фантастические, чудесные вещи, а DBATools может сделать для вас мощные, удивительные вещи во всем, что связано с SQL Server. Ниже простой пример использования команды DBATools Backup-DbaDatabase для создания полного бэкапа. Эта команда имеет полный набор опций, включая резервирование всех баз данных на SQL Server, если не передавать параметр -Database. Проверьте это прямо сейчас.
Как отключить ограничения FileTable для массовой загрузки
Для массовой загрузки файлов в таблицу FileTable без издержек по применению определенных в системе ограничений, можно временно отключить ограничения. Дополнительные сведения см. в статье Управление таблицами FileTable.
Пример. Перенос файлов из файловой системы в таблицу 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 можно использовать различные способы.
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.
Как выполнить загрузку файлов в таблицу FileTable
Ниже перечислены методы, которые можно использовать для загрузки файлов в таблицу FileTable.
Перетаскивание файлов из исходной папки в новую папку FileTable в проводнике Windows.
Применение параметров командной строки, таких как MOVE, COPY, XCOPY или ROBOCOPY, из командной строки или пакетного файла или скрипта.
Метод 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Гб. Для бОльших баз данных увеличение пропускной способности может дать значительную экономию времени.
5 способов сделать резервные копии в SQL Server
В прошлый раз мы обсуждали 5 типов резервных копий. Сейчас я хочу представить вам пять способов сделать бэкап в SQL Server. Я не смогу продемонстрировать все доступные опции каждого из этих шести методов. Здесь много чего есть даже для такой простой темы как бэкапы.
Столбец is_directory в схеме FileTable The is_directory column in the FileTable schema
В приведенной ниже таблице описывается взаимодействие между столбцом is_directory и столбцом file_stream , в котором находятся данные FILESTREAM в таблице FileTable. The following table describes the interaction between the is_directory column and the file_stream column that contains the FILESTREAM data in a FileTable.
is_directory значение is_directory value | file_stream значение file_stream value | Поведение Behavior |
FALSE FALSE | NULL NULL | Это недопустимое сочетание, которое будет перехвачено системным ограничением. This is an invalid combination that will be caught by a system-defined constraint. |
FALSE FALSE | Этот элемент представляет файл. The item represents a file. | |
TRUE TRUE | NULL NULL | Этот элемент представляет каталог. The item represents a directory. |
TRUE TRUE | Это недопустимое сочетание, которое будет перехвачено системным ограничением. This is an invalid combination that will be caught by a system-defined constraint. |
Полный путь к элементу, хранящемуся в таблице FileTable The full path to an item stored in a FileTable
Полный путь к файлу или каталогу, сохраненный в таблице FileTable, начинается со следующих элементов. The full path to a file or directory stored in a FileTable begins with the following elements:
Общий ресурс с поддержкой доступа файлового ввода-вывода к данным FILESTREAM на уровне экземпляра SQL Server SQL Server . The share enabled for FILESTREAM file I/O access at the SQL Server SQL Server instance level.
Имя DIRECTORY_NAME на уровне базы данных. The DIRECTORY_NAME specified at the database level.
FILETABLE_DIRECTORY на уровне таблицы FileTable. The FILETABLE_DIRECTORY specified at the FileTable level.
В итоге иерархия выглядит следующим образом. The resulting hierarchy looks like this:
Данная иерархия каталогов образует корень пространства имен файлов FileTable. This directory hierarchy forms the root of the FileTable’s file namespace. В этой иерархии каталогов данные FILESTREAM для FileTable хранятся в виде файлов и в виде вложенных каталогов, которые также могут содержать файлы и вложенные каталоги. Under this directory hierarchy, the FILESTREAM data for the FileTable is stored as files, and as subdirectories which can also contain files and subdirectories.
Важно иметь в виду, что иерархия каталогов, созданная в общем ресурсе FILESTREAM на уровне экземпляра, является виртуальной иерархией каталогов. It is important to keep in mind that the directory hierarchy created under the instance-level FILESTREAM share is a virtual directory hierarchy. Иерархия хранится в базе данных SQL Server SQL Server и не представлена физически в файловой системе NTFS. This hierarchy is stored in the SQL Server SQL Server database and is not represented physically in the NTFS file system. Все операции, осуществляющие доступ к файлам и каталогам в общем ресурсе FILESTREAM в таблицах FileTable, перехватываются и обрабатываются компонентом SQL Server SQL Server , внедренным в файловую систему. All operations that access files and directories under the FILESTREAM share and in the FileTables that it contains are intercepted and handled by a SQL Server SQL Server component embedded in the file system.
Как перенести файлы базы данных SQL Server в другой каталог или на другой диск
Рассмотрим пример перемещения файлов пользовательской базы данных SQL Server в новое месторасположение. В рассматриваемом примере все файлы одной отдельно взятой БД с именем EffectOffice будут перенесены с одного логического диска на другой (с диска T:\ на диск U:\ ).
Перед началом процедуры переноса файлов базы данных остановим cервисы и приложения, работающие с этой базой данных.
Подключимся к экземпляру SQL Server, на котором расположена интересующая нас база данных и выясним текущее размещение файлов БД с помощью запроса:
Выполним запрос на закрытие всех соединений к БД и перевод БД в одно-пользовательский режим:
Переведём базу данных в Offline-режим:
Выполним копирование файлов БД в новое место-расположение с помощью утилиты командной строки robocopy, которая позволит нам сохранить все разрешения на каталоги и файлы на уровне NTFS.
В нашем примере файлы БД копируются из каталога T:\DBCL02-EffectOffice в каталог U:\DBCL02-EffectOffice . Каталог назначения при этом будет создан в процессе копирования и на него будут скопированы все атрибуты исходного каталога.
Выполним замену путей к файлам на уровне SQL Server запросом вида (отдельный запрос по каждому файлу):
Переведём базу данных в Online-режим и обратно включим многопользовательский режим работы с БД
Запустим сторонние службы и приложения, использующие базу данных и убедимся в штатной работе с данными.
После успешного запуска БД и проверок, можем удалить файлы с их исходного местоположения ( T:\DBCL02-EffectOffice )
Дополнительные источники информации:
Проверено на следующих конфигурациях:
Версия SQL Server |
---|
Microsoft SQL Server 2016 Standard Edition SP2 CU14 (13.0.5830.85) |
Автор первичной редакции:
Алексей Максимов
Время публикации: 24.09.2020 09:15
Уровень вложенности Nesting level
ВАЖНО! IMPORTANT!! Нельзя хранить более 15 уровней вложенных каталогов в каталоге FileTable. You cannot store more than 15 levels of subdirectories in the FileTable directory. Если сохранено 15 уровней вложенных каталогов, каталог самого нижнего уровня не сможет содержать файлы, так как эти файлы представляют собой дополнительный уровень. When you store 15 levels of subdirectories, then the lowest level cannot contain files, since these files would represent an additional level.
Семантика корневых каталогов на уровне экземпляра, базы данных и таблицы FileTable The semantics of the root directories at the instance, database, and FileTable levels
Эта иерархия каталогов имеет следующую семантику. This directory hierarchy observes the following semantics:
Общий ресурс FILESTREAM на уровне экземпляра настраивается администратором и хранится в виде свойства сервера. The instance-level FILESTREAM share is configured by an administrator and stored as a property of the server. Этот общий ресурс можно переименовать с помощью диспетчера конфигурации SQL Server SQL Server . You can rename this share by using SQL Server SQL Server Configuration Manager. Операция переименования вступает в силу только после перезапуска сервера. A renaming operation does not take effect until the server is restarted.
Параметр DIRECTORY_NAME уровня базы данных при создании базы данных по умолчанию имеет значение null. The database-level DIRECTORY_NAME is null by default when you create a new database. Администратор может задать или изменить это имя с помощью инструкции ALTER DATABASE . An administrator can set or change this name by using the ALTER DATABASE statement. Это имя должно быть уникальным (при сравнении без учета регистра) в этом экземпляре. The name must be unique (in a case-insensitive comparison) in that instance.
Обычно имя FILETABLE_DIRECTORY указывается в составе инструкции CREATE TABLE при создании таблицы FileTable. You typically provide the FILETABLE_DIRECTORY name as part of the CREATE TABLE statement when you create a FileTable. Это имя можно изменить с помощью команды ALTER TABLE . You can change this name by using the ALTER TABLE command.
Эти корневые каталоги нельзя переименовать с помощью операций файлового ввода-вывода. You cannot rename these root directories through file I/O operations.
Эти корневые каталоги нельзя открыть с использованием дескрипторов файлов для монопольного доступа. You cannot open these root directories with exclusive file handles.
Извлечение файлов из базы данных
В прошлой теме мы рассмотрели, как добавить файл в базу данных. Теперь произведем обратную операцию – получим файл из БД. Вначале определим класс файла, который упростит работу с данными:
Затем в коде программы определим следующий метод:
В этом методе с помощью SqlDataReader мы получаем значения из БД и по ним создаем объект Image, который потом добавляется в список. И в конце смотрим, если в списке есть элементы, то берем первый элемент и сохраняем его на локальный компьютер. И после сохранения в папке нашей программы появится загруженный из базы данных файл.
ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse
Описывает структуру каталогов, в которой файлы хранятся в таблицах FileTable. Describes the directory structure in which the files are stored in FileTables.
Метод 4: Использование планов обслуживания для создания резервных копий
Тут я лишь поделюсь с вами несколькими мыслями относительно использования планов обслуживания. Во-первых, планы обслуживания (Maintenance Plans) представляют собой еще один метод с графическим интерфейсом для настройки резервных копий. В этом отношении они простой способ «указать и щелкнуть» для обработки хранения резервных копий, о чем мы еще не говорили. Во-вторых, в силу природы этого метода, который позволяет вам выбрать Backups как вариант плана, а затем пройти по шагам каждую часть мастера процесса, Maintenance Plans может стать общим подходом для ИТ-профессионалов, вышедших из системных администраторов. Например, нет необходимости знать или понимать опции, представленные в мастере SSMS Backup.
Рассмотрим, как мы можем сохранять файлы, в частности, файлы изображений в базу данных. Для этого добавим в базу данных новую таблицу Images с четырьмя столбцами: Id (первичный ключ и идентификатор, имеет тип int), FileName (будет хранить имя файла и имеет тип nvarchar), Title (будет хранить заголовок файла и также имеет тип nvarchar) и ImageData (будет содержать бинарные данные файла и имеет тип varbimary(MAX)).
Определим код, в котором будут загружаться данные в таблицу:
После выполнения этой программы в базе данных появится соответствующая запись:
Руководство. Как использовать относительные пути для переносимого кода How to: Use Relative Paths for Portable Code
Чтобы код и приложения были независимы от текущего компьютера и базы данных, следует избегать создания кода с использованием абсолютных путей. To keep code and applications independent of the current computer and database, avoid writing code that relies on absolute file paths. Вместо этого рекомендуется получать полный путь к файлу во время выполнения с помощью функций FileTableRootPath (Transact-SQL) и GetFileNamespacePath (Transact-SQL), как показано в приведенном ниже примере. Instead, get the complete path for a file at run time by using the FileTableRootPath (Transact-SQL) and GetFileNamespacePath (Transact-SQL)) functions together, as shown in the following example. По умолчанию функция GetFileNamespacePath возвращает относительный путь к файлу, находящемуся внутри корневого пути к базе данных. By default, the GetFileNamespacePath function returns the relative path of the file under the root path for the database.
Важные ограничения Important restrictions
Длина полного имени Length of full path name
ВАЖНО! IMPORTANT!! Файловая система NTFS поддерживает пути, намного превышающие ограничение в 260 символов, установленное в оболочке Windows и большинстве других функций Windows API. The NTFS file system supports path names that are much longer than the 260-character limit of the Windows shell and most Windows APIs. Поэтому можно создавать файлы в файловой иерархии FileTable с помощью Transact-SQL, которые нельзя будет просмотреть или открыть в Проводнике Windows и многих других приложениях Windows, поскольку полный путь превышает 260 символов. Therefore it is possible to create files in the file hierarchy of a FileTable by using Transact-SQL that you cannot view or open with Windows Explorer or many other Windows applications, because the full path name exceeds 260 characters. Однако с этими файлами вы можете продолжать работать с помощью инструкций Transact-SQL. However you can continue to access these files by using Transact-SQL.
Самородов Федор Анатольевич: Как работать с файлами из Transact-SQL
Иногда хочется поработать с файлами прямо из SQL-кода. Например, вывести в файл какую-нибудь отладочную информацию, выгрузить XML-данные, использовать текстовый файл для ведения журнала или сохранить отчёт в HTML-файле. А может, наоборот, прочитать из файла какие-то данные, конфигурационную информацию, импортировать содержимое CSV-таблицы или XML-источника.
Есть несколько способов получить доступ к файловой системе и сдержимому файлов из базы данных SQL Server. Один из них — задействовать штатные процедуры для работы с COM-объектами. Вот простой пример:
Этот способ работы с файлами не самый оптимальный. Его имеет смысл использовать, если нужно быстро решить простую задачу. Для промышленного использования это не лучший вариант. Как можно полноценно работать с файлами из SQL Server’а, вы узнаете на наших курсах.
Кстати, если вы администратор, то я уверен, что знаю о чём вы подумали, глядя на пример кода, получающего доступ к файлу Hosts из SQL-сценария. Не пугайтесь, безопасность не пострадает, если SQL Server настроен правильно. Что должен сделать администратор SQL Server’а, чтобы спокойно спать по ночам мы обсудим на наших занятиях.
Использование имен виртуальной сети для групп доступности AlwaysOn Using Virtual Network Names (VNNs) with AlwaysOn Availability Groups
Если база данных, содержащая данные FILESTREAM или FileTable, принадлежит группе доступности: When the database that contains FILESTREAM or FileTable data belongs to an AlwaysOn availability group:
Функции FILESTREAM и FileTable принимают или возвращают имена виртуальной сети, а не имена компьютеров. The FILESTREAM and FileTable functions accept or return virtual network names (VNNs) instead of computer names. Дополнительные сведения об этих функциях см. в разделе Функции Filestream и FileTable (Transact-SQL). For more information about these functions, see Filestream and FileTable Functions (Transact-SQL).
При осуществлении любого доступа к данным FILESTREAM или FileTable посредством API-интерфейса файловой системы будут использоваться имена виртуальной сети, а не имена компьютеров. All access to FILESTREAM or FileTable data through the file system APIs should use VNNs instead of computer names. Дополнительные сведения см. в разделе FILESTREAM и FileTable с группами доступности AlwaysOn (SQL Server). For more information, see FILESTREAM and FileTable with Always On Availability Groups (SQL Server).
Руководство. Работа с каталогами и путями в таблицах FileTable How To: Work with Directories and Paths in FileTables
Следующие 3 функции можно использовать для работы с каталогами FileTable в Transact-SQL Transact-SQL : You can use the following 3 functions to work with FileTable directories in Transact-SQL Transact-SQL :
Чтобы получить этот результат, выполните следующее. To get this result | Воспользуйтесь этой функцией Use this function |
---|---|
Получите корневой путь UNC для конкретной таблицы FileTable или для текущей базы данных. Get the root-level UNC path for a specific FileTable or for the current database. | FileTableRootPath (Transact-SQL) FileTableRootPath (Transact-SQL) |
Получите абсолютный или относительный путь UNC к файлу или каталогу в таблице FileTable. Get an absolute or relative UNC path for a file or directory in a FileTable. | GetFileNamespacePath (Transact-SQL) GetFileNamespacePath (Transact-SQL) |
Получите значение идентификатора path_locator для заданного файла или каталога в таблице FileTable, указав путь к нему. Get the path locator ID value for the specified file or directory in a FileTable, by providing the path. | GetPathLocator (Transact-SQL) GetPathLocator (Transact-SQL) |
Читайте также: