Sql server копирование файлов

Обновлено: 16.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 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 .

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

Имеет маленький размер
Сильно нагружен ОС и системными запросами
Довольно медленный
Помирает

Все эти факторы влияют как на отказоустойчивость, так и на скорость обработки запросов SQl-сервером, а следовательно и на работоспособность комплекса в целом!
Теперь, когда вы прониклись важностью момента, можно приступить к практическим действиям. Итак:

Перенос пользовательской базы данных¶

1. Договариваемся с творческой частью коллектива, что в определенное время все перестают работать с базой. А именно, прекращают что-то туда добавлять и/или изменять.

2. Останавливаем сервисы, которые работают с МБД в автоматическом режиме, например:

DB Import - импорт новостных лент
DDB - распределенная база данных
Sch_to_DB - репликация расписаний
иначе, есть вероятность потерять часть информации.

3. Запускаем Microsoft SQL Server Management Studio.

4. Самым первым делом всегда делаем бэкап базы!

5. Далее, смотрим, где лежат файлы нужной нам базы данных (в нашем примере это будет МБД под названием "RADIO-DB"). Для этого, нажимаем на ней ПКМ и открываем Properties (Свойства). Заходим в раздел Files (Файлы) и смотрим раздел Path (Путь):

6. Далее, нажимаем ПКМ на целевой базе и выбираем пункт Tasks\Detach (Задачи\Отсоединить):

7. В открывшемся окне ставим обе галочки и нажимаем ОК. После чего, МБД пропадет из списка:

8. Через обычный проводник заходим в каталог, где лежат нужные нам файлы. В нашем примере, это C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012\MSSQL\DATA.

9. Копируем эти файлы в новый каталог на новый диск и снова открываем Microsoft SQL Server Management Studio.

10. Нажимаем ПКМ на разделе Databases (Базы данных), выбираем пункт Attach (Присоединить) и в открывшемся окне нажимаем кнопку Add (Добавить) и выбираем нужный нам файл RADIO-DB.mdf уже из нового каталога:

Убеждаемся, что пути у нас теперь новые и нажимаем ОК.

Всё, пользовательская база данных переехала на новый диск. Не нужно ничего перезапускать и т.д. Убеждаемся, что рабочие места переподключились к МБД и разрешаем им снова работать в штатном режиме.

Перенос системных баз данных¶

Но, остались еще системные базы данных (спрятаны в разделе System Databases). Это msdb, model и tempdb, которые в общем-то тоже будет неплохо перенести на быстрый и отказоустойчивый диск. Тем более, что среди них есть одна, очень для нас важная база - tempdb. Именно через нее проходят все запросы, прежде чем попасть в пользовательскую МБД. Перенести системные базы ничуть не сложнее, чем пользовательские. И для этого надо:

1. Используя Microsoft SQL Server Management Studio, выполнить следующий скрипт:

Его также можно скачать из этого описания и запустить непосредственно на SQl-сервере.

2. Останавливаем службу SQL.

3. Копируем из старого каталога (помним наш пример: C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012\MSSQL\DATA) все файлы, указанные в скрипте выше, в новый каталог, который мы прописали в том же скрипте.

4. Обязательно добавляем учетную запись группы безопасности. Подробно о том, как это сделать, читайте в конце данной статьи, в разделе "Предоставление разрешения на доступ к файловой системе идентификатору безопасности службы".

5. Запускаем службу SQL.

6. Убедиться, что мы все сделали правильно, можно, посмотрев в свойствах каждой системной БД раздел Files (Файлы). Там должны быть новые пути к обоим файлам (самой БД и логу).

Перенос самой системной базы данных master¶

Да, еще у нас осталась самая системная из всех системных баз - master
- путь, прописанный для этой базы, будет путем по умолчанию для всех вновь создающихся баз на данном сервере. Впрочем, для пользователей Digispot это не очень актуально. Тем более, что мы уже умеем менять пути любым базам.

1. Для изменения пути к БД master, нам понадобится оснастка SQL Server Configuration Manager (Диспетчер конфигурации SQL Server). Запускаем ее и открываем свойства SQL Server:

2. В свойствах SQL Server`а открываем вкладку Startup Parameters (Параметры запуска):

и по очереди меняем все указанные пути на новые.
- каждая строка начинается со своего символа -d, -e или -l. Ни в коем случае не меняйте их и не удаляйте!

3. Каждое изменение пути подтверждаем нажатием кнопки Update.

4. Теперь останавливаем сервис, копируем файлы master.mdf и mastlog.ldf из старого каталога в новый. После чего запускам сервис. ERRORLOG можно не копировать. Он создастся заново.

Предоставление разрешения на доступ к файловой системе идентификатору безопасности службы¶

С помощью проводника Windows перейдите в папку файловой системы, в которой находятся файлы базы данных. Правой кнопкой мыши щелкните эту папку и выберите пункт Свойства.

На вкладке Безопасность щелкните Изменитьи затем ― Добавить.

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

В поле Введите имена объектов для выбора введите имя идентификатора безопасности службы. В качестве идентификатора безопасности службы компонента Компонент Database Engine используйте NT SERVICE\MSSQLSERVER для экземпляра по умолчанию или NT SERVICE\MSSQL$InstanceName — для именованного экземпляра.

В поле имен Группа или пользователь выберите имя идентификатора безопасности службы, а затем в поле Разрешения для <имя> установите флажок Разрешить для параметра Полный доступ.

Вот теперь, точно всё. Спасибо за внимание!

P.S. В зависимости от конкретной ОС, конкретной версии SQL сервера, вашей кармы и наличия солнечных вспышек, что-то может пойти не так. Прежде чем приступать к вышеописанным действиям, убедитесь, что:
а) оно вам действительно надо
б) вы морально готовы
ц) вы понимаете, что вы делаете
д) у вас вся ночь впереди, чтобы переустановить SQL заново и развернуть бэкап.

detach_db2.PNG Просмотреть (31,7 КБ) Станислав Середницкий, 22/03/2018 17:27

detach_db.PNG Просмотреть (62,9 КБ) Станислав Середницкий, 22/03/2018 17:28

detach_db3.PNG Просмотреть (87,3 КБ) Станислав Середницкий, 22/03/2018 17:56

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

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