T sql копирование файлов

Обновлено: 07.07.2024

В практике аудита использование СУБД SQL Server для хранения и анализа данных – одно из непременных условий, существенно повышающих качество проводимой работы.

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

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

Но для начала немного простых примеров использования sqlcmd (все данные тестовые).

Посмотрим, как это работает в интерактивном режиме.

Запускаем командную строку Windows – cmd.

Чтобы подключиться к именованному экземпляру SQL Server, укажем имя сервера и имя экземпляра SQL Server (в примере соответственно DESKTOP\SQLEXPRESS), с которым необходимо соединиться.

–S указывает на server\instance_name;

–E –trusted connection (доверительное соединение).

Отображаемая в командной строке цифра 1> означает, что подключение состоялось и есть готовность принимать запросы для исполнения.

Если ранее при создании пользователя SQL Server для пользователя была включена аутентификацию SQL Server,

то при подключении требуется указать имя пользователя и ввести его пароль.

Чтобы это выполнить, предварительно необходимо выйти из sqlcmd и заново войти с указанием учетных данных:

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

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

Давайте теперь проверим текущую базу данных.

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

Следующий пример использования sqlcmd – вывод списка баз данных в экземпляре SQL:

Теперь давайте посмотрим на запуск sqlcmd в командном режиме.

Получим список таблиц в базе данных db1, выведя его в файл tables.txt, используя в sqlcmd подготовленный заранее и сохраненный на жестком диске ПК, сценарий tablesList.sql со следующим содержанием:

Затем вызовем sqlcmd для выполнения созданного сценария tablesList.sql:

В tables.txt отображается следующий результат (для примера выбрана тестовая база данных, состоящая из одной таблицы):

Теперь вернемся к нашей задаче автоматизации резервного копирования с использованием sqlcmd и планировщика задач Windows.

Во-первых, создадим файл сценария резервного копирования базы данных db1 и сохраним его в файле backup.sql:

Файл резервной копии в результате выполнения этого сценария сохраняется в каталог C:\Users\User\SQL\Backup\.

Далее создаем командный файл run-sqlcmd-backup.bat следующего содержания:

И, наконец, открываем Планировщик заданий Windows (подробнее о планировщике заданий Windows можно прочесть в официальных документах Microsoft по этой тематике), для того, чтобы создать задачу резервного копирования базы данных db1, запускающую командный файл run-sqlcmd-backup.bat по необходимому расписанию.

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

Как перенести файлы базы данных 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

В этом разделе описывается создание резервной копии файлов и файловых групп в SQL Server с помощью среды SQL Server Management Studio, Transact-SQLили PowerShell. Если размер базы данных и требования по производительности делают полное резервное копирование базы данных нецелесообразным, можно создать резервную копию файлов. Резервная копия файлов содержит данные одного или нескольких файлов или файловых групп.

Перед началом

Ограничения

Инструкция BACKUP не разрешена в явных и неявных транзакциях.

В простой модели восстановления резервные копии файлов для чтения и записи должны создаваться вместе. Это помогает обеспечить восстановление базы данных до согласованного момента времени. Вместо того чтобы указывать каждый файл или файловую группу для чтения и записи, воспользуйтесь параметром READ_WRITE_FILEGROUPS. Этот параметр создает резервные копии всех файловых групп, доступных для чтения и записи, в базе данных. С помощью параметра READ_WRITE_FILEGROUPS создаются так называемые частичные резервные копии. См. раздел Частичное резервное копирование (SQL Server).

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

Permissions

Разрешения BACKUP DATABASE и BACKUP LOG по умолчанию назначаются участникам предопределенной роли сервера sysadmin и предопределенным ролям базы данных db_owner и db_backupoperator.

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

Использование среды SQL Server Management Studio

После подключения к соответствующему экземпляру компонента Компонент SQL Server Database Engineв обозревателе объектов разверните дерево сервера, щелкнув его имя.

Раскройте узел Базы данных и в зависимости от типа восстанавливаемой базы данных выберите пользовательскую базу данных или раскройте узел Системные базы данных и выберите системную базу данных.

Щелкните правой кнопкой мыши базу данных, выберите пункт Задачи, а затем команду Создать резервную копию. Откроется диалоговое окно Резервное копирование базы данных .

В списке База данных проверьте имя базы данных. При необходимости можно выбрать другую базу данных из списка.

В списке Тип резервной копии выберите Полная или Разностная.

Для параметра Компонент резервного копирования выберите Файл и файловые группы.

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

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

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

Укажите, когда истекает срок действия резервного набора данных.

Чтобы задать срок действия резервного набора данных, выберите пункт После (параметр по умолчанию) и введите срок действия набора в днях с момента его создания. Это значение может быть задано в диапазоне от 0 до 99 999 дней. Значение 0 означает, что срок действия резервного набора данных не ограничен.

Значение по умолчанию задается в параметре Срок хранения носителей резервных копий по умолчанию (дней) диалогового окна Свойства сервера (страница Параметры базы данных ). Чтобы получить доступ к этому параметру, щелкните правой кнопкой мыши на имени сервера в обозревателе объектов и выберите его свойства, затем выберите страницу Параметры базы данных .

Чтобы указать дату истечения срока действия резервного набора данных, выберите пункт На и введите дату истечения срока действия резервного набора данных.

Чтобы выбрать тип назначения резервной копии, выберите пункт Диск или Лента. Чтобы выбрать пути к 64 (или менее) дискам или ленточным накопителям, содержащим один набор носителей, нажмите кнопку Добавить. Выбранные пути отображаются в списке Сохранить на .

Чтобы удалить носитель резервной копии, выберите его и нажмите кнопку Удалить. Чтобы просмотреть содержимое носителя резервной копии, выберите его и щелкните Содержимое.

Чтобы просмотреть или выбрать дополнительные параметры, нажмите кнопку Параметры на панели Выбор страницы .

Выберите параметр Переписать носитель , указав один из следующих вариантов:

Создать резервную копию в существующем наборе носителей

Для этого параметра выберите вариант Добавить в существующий резервный набор данных или Перезаписать все существующие резервные наборы данных.

Сведения о создании резервной копии на существующем наборе носителей см. в разделе Наборы носителей, семейства носителей и резервные наборы данных (SQL Server).

(Необязательно) Выберите Проверить имя набора носителей и срок действия резервного набора данных, чтобы при выполнении операции резервного копирования проверялся срок действия набора носителей и резервного набора данных.

(Необязательно) Введите имя в текстовом поле Имя набора носителей. Если имя не указано, создается набор носителей с пустым именем. Если имя набора носителей указано, то для носителя (ленточного или дискового) проверяется совпадение введенного и существующего имени.

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

Создать резервную копию в новом наборе носителей и удалить все существующие резервные наборы данных

Для этого параметра введите имя в текстовом поле Имя нового набора носителей и при необходимости введите описание набора носителей в текстовое поле Описание нового набора носителей .

(Необязательно) В разделе Надежность можно также установить флажки:

Проверить резервную копию после завершения.

Рассчитать контрольную сумму перед записью на носитель и (необязательно) Продолжить при ошибке контрольной суммы.

При резервном копировании на накопитель на магнитной ленте (как указано в разделе Назначение страницы Общие ) активен параметр Выгрузить ленту после резервного копирования . Выбор этого параметра активирует параметр Перемотать ленту перед выгрузкой .

Параметры в разделе Журнал транзакций доступны, только если создается резервная копия журнала транзакций (это можно указать в разделе Тип резервной копии вкладки Общие ).

SQL Server 2008 Enterprise и более поздние версии поддерживают сжатие резервных копий. По умолчанию сжатие резервных копий зависит от значения параметра конфигурации сервера backup-compression default . Однако независимо от текущего значения по умолчанию на уровне сервера можно сжать резервные копии, установив параметр Сжимать резервные копии, или отказаться от сжатия резервных копий, установив параметр Не сжимать резервные копии.

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

Использование Transact-SQL

Чтобы создать резервную копию файла или файловой группы, используйте инструкцию BACKUP DATABASE <файл_или_файловая_группа>. В этой инструкции должны быть указаны по меньшей мере следующие данные:

Имя базы данных.

предложение FILE или FILEGROUP для каждого резервируемого файла или группы файлов;

устройство резервного копирования, на которое будет записываться полная резервная копия.

Базовая структура синтаксиса Transact-SQL для резервного копирования файлов:

BACKUP DATABASE database

< FILE = логическое_имя_файла | FILEGROUP = логическое_имя_файловой_группы > [ , . f ]

TO backup_device [ , . n ]

[ WITH with_options [ , . o ] ] ;

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

Примеры

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

Файловая группа с именем SalesGroup1 , содержащая файлы SGrp1Fi1 и SGrp1Fi2 .

Файловая группа с именем SalesGroup2 , содержащая файлы SGrp2Fi1 и SGrp2Fi2 .

A. Создание резервной копии двух файлов

В следующем примере создается разностная резервная копия только файла SGrp1Fi2 из группы SalesGroup1 и файла SGrp2Fi2 из группы SalesGroup2 .

Б. Создание полной резервной копии вторичных файловых групп

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

В. Создание разностной резервной копии файлов вторичных файловых групп

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

Использование PowerShell

Используйте командлет Backup-SqlDatabase и укажите Files в качестве значения параметра -BackupAction . Также укажите один из следующих параметров:

Чтобы создать резервную копию определенного файла, укажите параметр -DatabaseFileString , где String ― это один или несколько файлов базы данных для резервного копирования.

Чтобы создать резервную копию всех файлов из заданной файловой группы, укажите параметр -DatabaseFileGroupString , где String ― это одна или несколько файловых групп базы данных для резервного копирования.

В следующем примере создается полная резервная копия каждого файла из вторичных файловых групп «FileGroup1» и «FileGroup2» базы данных <myDatabase> . Резервные копии создаются в расположении резервных копий по умолчанию экземпляра сервера Computer\Instance .

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

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

Создание резервной копии таблицы SQL Server возможно в SQL Server. Существуют различные альтернативные способы резервного копирования таблицы в SQL Server SQL

BCP (ПРОГРАММА БОЛЬШОГО КОПИРОВАНИЯ)
Генерация табличного скрипта с данными
Сделайте копию таблицы, используя SELECT INTO
СОХРАНИТЬ данные таблицы непосредственно в плоский файл
Экспорт данных с использованием SSIS в любой пункт назначения

Здесь я объясняю только первый отдых, который вы, возможно, знаете

Способ 1 - Резервное копирование таблицы SQL с использованием BCP (ПРОГРАММА БОЛЬШОГО КОПИРОВАНИЯ)

Для резервного копирования таблицы SQL с именем Person.Contact, которая находится в SQL Server AdventureWorks, нам нужно выполнить следующий скрипт, который

ВЫХОД

У вас должны быть права на массовый импорт / экспорт
Выше скрипт -n обозначает родные типы данных SQL, которые являются ключевыми при восстановлении.
-T обозначает, что вы подключаетесь к SQL Server с использованием аутентификации Windows, если вы хотите подключиться с использованием аутентификации SQL Server, используйте -U -P
Это также скажет вам скорость передачи данных, в моем случае это было 212468,08 строк в секунду.
После выполнения этой команды будет создан файл с именем «AdventureWorks.Person.Contact_20120222» в указанной папке назначения.

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

Способ 1:

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

и так далее . для n таблицы

Этот оператор создаст таблицы с именами newtable1, newtable1, .. поэтому вам не нужно предварительно создавать его.

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

Способ 2:

Если вы хотите сделать резервную копию таблицы на другом сервере для какого-либо решения аварийного восстановления или предотвращения потери данных, то вы можете создать сценарий для таблицы, используя опцию «Создать сценарии» в «Задачах».

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

Выберите свою базу данных из списка, таблицы которого необходимо сохранить

Следующий экран, который появляется, - Опции скрипта.

Прокрутите вниз параметры скрипта, пока не увидите Параметры таблицы / просмотра. Мы хотим, чтобы выполнялись следующие условия: проверка ограничений, данных сценария, внешних ключей, первичных ключей, триггеров и уникальных ключей. (Или вы можете выбрать все, что вам нужно, конечно). Выберите Next, и мы представим экран выбора типов объектов

Выберите Таблицы и нажмите «Далее». Наконец, выберите таблицу или таблицы, которые вы хотите сделать резервную копию, и нажмите Далее

Способ 3:

Вы также можете использовать утилиту bcp для резервного копирования таблиц.

5 способов сделать резервные копии в SQL Server

В прошлый раз мы обсуждали 5 типов резервных копий. Сейчас я хочу представить вам пять способов сделать бэкап в SQL Server. Я не смогу продемонстрировать все доступные опции каждого из этих шести методов. Здесь много чего есть даже для такой простой темы как бэкапы.

Метод 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, который будет исполнен. Вы сможете сохранить его в качестве примера для дальнейшего использования.

Метод 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Гб. Для бОльших баз данных увеличение пропускной способности может дать значительную экономию времени.

Метод 3: Использование Powershell для создания резервных копий

Если вы не используете Powershell с SQL Server, то это того стоит. Если вы не используете модуль DBATools с SQL Server, получите его сейчас. PowerShell может делать фантастические, чудесные вещи, а DBATools может сделать для вас мощные, удивительные вещи во всем, что связано с SQL Server. Ниже простой пример использования команды DBATools Backup-DbaDatabase для создания полного бэкапа. Эта команда имеет полный набор опций, включая резервирование всех баз данных на SQL Server, если не передавать параметр -Database. Проверьте это прямо сейчас.

Метод 4: Использование планов обслуживания для создания резервных копий

Тут я лишь поделюсь с вами несколькими мыслями относительно использования планов обслуживания. Во-первых, планы обслуживания (Maintenance Plans) представляют собой еще один метод с графическим интерфейсом для настройки резервных копий. В этом отношении они простой способ «указать и щелкнуть» для обработки хранения резервных копий, о чем мы еще не говорили. Во-вторых, в силу природы этого метода, который позволяет вам выбрать Backups как вариант плана, а затем пройти по шагам каждую часть мастера процесса, Maintenance Plans может стать общим подходом для ИТ-профессионалов, вышедших из системных администраторов. Например, нет необходимости знать или понимать опции, представленные в мастере SSMS Backup.

Читайте также: