| ||||||||||||||||
 | ||||||||||||||||
| ||||||||||||||||
 | ||||||||||||||||
| ||||||||||||||||
|
|
Азбука BCP
Пошаговое руководство для "чайников" в MS SQL Server BCP
Bulk copy program (BCP), это специальная утилита командной строки для массового копирования, которая поставляется со всеми
изданиями SQL Server. Она незаслуженно редко используется, но является необходимым компонентом инструментария DBA.
Хотя эта утилита не имеет своего ярлыка в папке SQL Server, к которой можно перейти из меню Start, и только кратко упоминается
в SQL Server Books Online (BOL), программа BCP пережила многочисленные версии SQL Server и даже претерпела незначительные
улучшения в его последних реализациях.
Запуск утилит из командной строки Windows NT всё реже используется современными DBA. Для Windows 2000/NT ярлык командной строки "Command Prompt" доступен через меню Start. Если в командной строке набрать: "BCP -h" будет представлен листинг общего синтаксиса использования утилиты:
C:\>bcp -h
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
C:\>
Листинг показывает пару дюжин параметров, из которых нужно знать только несколько, чтобы усвоить советы, которые представляет автор статьи. Имейте в виду, что параметры командной строки зависят от регистра, так что, например, -n не означает то же самое, что -N. Вообще, синтаксис командной строки определяет следующая строка:
BCP {dbtable} {in | out | queryout | format} datafile options
Команда начинается с BCP, затем указывается полностью квалифицированное имя таблицы, обозначенное как dbtable. Полностью
квалифицированное имя таблицы содержит имя базы данных, владельца table-object и имя table-object. Например,
Northwind.dbo.Employees - это полностью квалифицированное имя таблицы. Вы должны указать направление: in или out. Если Вы
используете BCP, чтобы копировать данные из файла в таблицу базы данных, используйте опцию in. Если Вы используете BCP,
чтобы копировать данные из таблицы базы данных в файл, используйте опцию out. Синтаксис команды продолжает имя файла -
datafile. Имя файла может быть абсолютным, например: C:\Temp\Northwind.dat, или это может быть относительное от текущей
папки имя, например: ..\Northwind.dat. (две лидирующие точки указывают, что файл Northwind.dat расположен на одну папку
выше в иерархии папок). Если Вы используете BCP с опцией out, создаётся новый файл с указанным именем, который
автоматически перезаписывает любого файла с тем же самым именем, если он уже существует. После этого Вы можете
разместить любые другие, необходимые параметры утилиты BCP. · Исходная таблица - Employees, владелец которой dbo. · Таблица находится в базе данных Northwind. · Файл экспорта - текстовый файл Northwind.txt имеющий в качестве разделителей табуляцию. · Файл находится в текущей папке на сервере с именем HOMER, к которому Вы обращаетесь через учетную запись sa (которая без пароля). Воспользовавшись представленными выше правилами, Вы получите следующий набор параметров для утилиты BCP: BCP Northwind.dbo.Employees out Northwind.txt -c -S HOMER -U sa -P Теперь, чтобы полностью изменить направление потока данных, давайте рассмотрим пример перемещения данных средствами BCP из файла в таблицу базы, и запишем команду, которая будет использовать следующую информацию:
· Исходный файл данных - файл типа native SQL Server, с именем Invoices.dat. В результате Вы должны получить следующую команду: BCP Northwind.lisa.Invoices in Invoices.dat -n -S BART -T Эти примеры демонстрируют наиболее типичное использование BCP. Вы можете перемещать большие таблицы или базы данных целиком используя представленные в примерах параметры. С помощью BCP можно выполнять и более сложные задачи, используя текстовые файлы фиксированной длинны или файлы с различными разделителями. Для получения дополнительной информации о параметрах BCP, см. BOL.
Если база данных, в которую копируются данные, отвечает двум важным условиям, BCP может осуществлять наиболее быстрое
и эффективное массовое копирование. Во-первых, для базы данных должна быть включена опция массового копирования. Для
SQL Server 7.0 откройте Enterprise Manager, щёлкните правой кнопкой мыши по базе данных, в которую или из которой будет
осуществляться копирование, и выберите Properties в выпадающем меню. После того, как появляется диалоговое окно Properties,
откройте закладку Options, чтобы определить, активизирована ли опция Select into/bulk copy. Для SQL Server 2000 должна быть
указана Simple или Bulk-Logged Recovery model. Во вторых, используемые для вставки данных из файла таблицы не должны иметь
индексов. В этом также можно убедиться с помощью Enterprise Manager: перейдите к принимающей данные таблице, щёлкните
по ней правой кнопкой мыши, и выберете из меню пункт All Tasks, а потом Manage indexes. Вы можете временно удалить
имеющиеся индексы в окне Manage indexes. Если таблица новая, Вы можете не создавать индексы, пока не скопируете в неё с
помощью BCP данные.
Сравнение BCP и DTS Import/Export Wizard Использование BCP для передачи данных не выглядит простой задачей. Резонно задать вопрос: "Существует ли более простой метод?". В SQL Server 7.0 и 2000 альтернативным методом передачи данных является использование DTS. DTS имеет много удобных свойств, таких, как графический интерфейс. Ярлык DTS (Import and Export Data) также можно найти через меню Start, и он запускает Import/Export Wizard. Эта утилита имеет в своём арсенале несколько удобных визардов интегрированных с Enterprise Manager, и обладает более широкими возможностями, а также может осуществлять более сложные преобразования данных, которые не доступны с помощью BCP. Также Вы можете использовать DTS для передачи данных между гетерогенными СУБД. На первый взгляд может показаться не понятным почему, если DTS имеет такие большие возможности, автор статьи рекомендует использовать BCP для копирования данных? Главным преимуществом использования для этого BCP, а не DTS Import/Export Wizard, является скорость передачи данных. Import/Export Wizard больше подходит для перемещения не большого количества данных или для одновременного преобразования данных. Поскольку этот визард часто требует меньшего времени для выполнения предварительных настроек копирования, он более удобен для небольших наборов данных. Однако, этот выигрыш во времени перестаёт быть решающим, когда наборы данных станут большими, и BCP начнёт превосходить его по быстродействию. Также, визард не может копировать данные без регистрации в журнале транзакций, что сказывается на увеличении времени его работы. Однако, наиболее важной причиной того, что автор статьи старается не использовать DTS, это то, что он (с настройками по умолчанию) не всегда создает таблицы, которая точно дублирует исходные таблицы. Могут быть утеряны ограничения, индексы и identity. Эта проблема не является критичной, если Вы передаёте не много и маленькие таблицы. Однако, при перемещении или копировании базы данных, Вы должны гарантировать, что вся база данных будет передана так, как она была изначально определена. Используя BCP и SQL-скрипты, которые генерирует Enterprise Manager, Вы создаете новые таблицы точно такими же, как исходные таблицы, наряду с их ограничениями, индексами и identity. Поскольку данные, которые перемещаются, будут идентичны, то и каждые пары таблиц в старой и новой базах будут функционально эквивалентны.
Сравнение BCP и резервирования с восстановлением SQL Server имеет и другую, альтернативную BCP возможность перемещения данных путём её резервного копирования и последующего восстановления на другом сервере. Все необходимые операции можно легко сделать с помощью соответствующих визардов Enterprise Manager. В результате Вы можете очень быстро получить точную копию исходной базы данных. К сожалению, восстановление резервной копии базы данных лишает Вас возможности полностью управлять этим процессом. Поскольку новая база данных будет в точности такой же, как оригинальная, все ошибки, которые Вы сделали в исходной базе данных, перекочуют и в новую. Если у старой базы transaction log занимал сотни мегабайт, он станет такого же размера в восстанавливаемой базе данных. Все индексы, который Вы не переиндексировали, останутся в новой базе данных в таком же виде. Кроме того, таким методом Вы на сможете копировать только выборочные объекты. Восстанавливается или всё, или ничего. Использование BCP и SQL-скриптов, которые генерирует Enterprise Manager, позволяет существенно уменьшить занимаемое таблицами место за счёт отсутствия фрагментации, а создание индексов после импорта данных позволит сделать их использование наиболее оптимальным. Кроме того, применение BCP не приведёт к разрастанию журнала транзакций. Используя BCP, Вы можете передавать только необходимые объекты между базами данных.
1. При передаче всех таблиц базы данных вы можете использовать возможности системной хранимой процедуры: sp_MSforeachtable, например: USE Northwind GO sp_MSforeachtable @Command1="master..xp_cmdshell 'BCP Northwind.dbo.? out D:\?.out -S ServerName -U sa -P Можно конечно обойтись и без sp_MSforeachtable, тогда стоит использовать конструкцию на подобии представленной ниже, которая создаёт файлы в csv-формате:
DECLARE @tbl varchar( 40)
DECLARE TableCursor CURSOR FOR
select name from sysobjects where type = 'U' order by name
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @tbl
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @cmd varchar( 250)
select @cmd = 'bcp Northwind.dbo.' + @tbl + ' out ' + 'c:\export\' +
@tbl + '.csv -S ' + @@servername + ' -c -t, -U sa'
select @cmd = 'master..xp_cmdshell ''' + @cmd + ''''
exec( @cmd)
select @cmd
FETCH NEXT FROM TableCursor INTO @tbl
END
CLOSE TableCursor
DEALLOCATE TableCurso
2. При передаче больших по размеру таблиц, можно получить дополнительный выигрыш во времени за счёт использования параметра BCP: [-b batchsize]. Подробнее об этом параметре можно прочитать в SQL Server Books Online, поиск по ключевым словам: Batch Switches.
|
|
|
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|