sqlcmd и bcp: SQL Server из командной строки

sqlcmd выполняет T-SQL из терминала, а bcp быстро перемещает данные таблиц в файлы и обратно. Вместе они покрывают всё, что иначе пришлось бы прокликивать в SSMS без возможности автоматизировать: запуск скриптов миграций из CI, backup из cron, выгрузку запроса в CSV, загрузку миллиона строк из файла за секунды. Чаще всего вы будете пользоваться двумя командами: sqlcmd -S host,port -U user -d db -C -b -i script.sql и bcp dbo.Table out table.dat -n -S host,port -U user -d db. Остальная часть руководства - это флаги вокруг них и значения по умолчанию, которые изменились в версии 18 и сломали огромное количество скриптов.

Какой у вас sqlcmd#

Программ под названием sqlcmd две, и в основном они принимают одни и те же аргументы:

• ODBC sqlcmd - оригинальный, поставляется с SQL Server и в пакете Microsoft mssql-tools18 для Linux и macOS (устанавливается в /opt/mssql-tools18/bin/). Рядом с ним должен быть установлен ODBC Driver for SQL Server.
• go-sqlcmd - более новая переделка на Go в виде одного исполняемого файла, устанавливается через winget install sqlcmd на Windows или brew install sqlcmd на macOS. ODBC-драйвер ему не нужен, он добавляет несколько удобств, например создание локального контейнера SQL Server, и задуман как прямая замена для скриптов.

Для скриптов подходит любой. Там, где их поведение различается, руководство об этом говорит. bcp существует только в варианте ODBC, из того же пакета mssql-tools18 или из установки SQL Server на Windows. Проверить, что у вас есть, можно так:

$ sqlcmd -?$ bcp -v

Инструменты версии 18 внесли одно изменение, которое важнее всех остальных: соединения по умолчанию шифруются, а сертификат сервера проверяется. Сервер с самоподписанным сертификатом - а это большинство экземпляров для разработки и многие хостинговые - тогда падает с ошибкой цепочки сертификатов. Передайте -C в sqlcmd (доверять сертификату сервера) и -u в bcp 18 либо установите сертификат, которому доверяет клиент. Скрипты, написанные для старых инструментов без этих флагов, - обычная причина, по которой задание CI сломалось после обновления образа раннера.

Подключение#

$ export SQLCMDPASSWORD='the-generated-password'$ sqlcmd -S db.example.net,14330 -U sa -d appdb -C1> SELECT @@VERSION;2> GO

Флаги:

Пароль, переданный через -P, попадает в историю оболочки и в список процессов, где его может прочитать любой другой пользователь машины. SQLCMDPASSWORD в окружении, заданный в CI из хранилища секретов, избегает и того, и другого. Существуют также SQLCMDSERVER, SQLCMDUSER и SQLCMDDBNAME, так что скрипт может вообще не содержать параметров подключения.

Без -d вы попадаете в базу по умолчанию для логина. В RE:NODE созданная для вас база уже назначена базой по умолчанию для sa, так что sqlcmd -S host,port -U sa -C сразу приводит вас в неё.

Выполнение запросов и скриптов#

В интерактивном режиме ничего не выполняется, пока вы не наберёте GO на отдельной строке. GO - это не T-SQL: это разделитель пакетов, который понимают sqlcmd и SSMS; они разбивают скрипт на каждом GO и отправляют части по одной. Поэтому CREATE PROCEDURE должно быть первым выражением в своём пакете, и поэтому переменная, объявленная до GO, после него пропадает. GO 100 выполняет предыдущий пакет сто раз, что удобно для генерации тестовых данных.

Для разовых команд -Q выполняет запрос и завершается:

$ sqlcmd -S db.example.net,14330 -U sa -C -Q "SELECT name, state_desc FROM sys.databases"

-q (строчная) выполняет его и остаётся в интерактивном режиме. Для файлов - -i:

$ sqlcmd -S db.example.net,14330 -U sa -d appdb -C -b -i 001_schema.sql -i 002_seed.sql

Несколько файлов -i выполняются по порядку в одном сеансе. Внутри скрипта команды, начинающиеся с двоеточия, - это директивы sqlcmd, а не T-SQL:

SSMS понимает те же директивы, если включить SQLCMD Mode в меню Query, так что один скрипт работает и интерактивно, и в конвейере.

Если в скрипте есть не-ASCII текст в литералах N'...', сообщите ODBC sqlcmd кодировку файла через -f 65001 для UTF-8 или сохраните скрипт в UTF-8 с BOM. Без этого символы с диакритикой могут прийти искажёнными. Почему префикс N вообще важен, объясняет статья collation и Unicode в SQL Server.

Переменные и коды выхода для автоматизации#

Скриптовые переменные позволяют одному скрипту работать с несколькими окружениями. Ссылайтесь на них как $(Name) и передавайте их через -v:

:on error exitUSE [$(DbName)];IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'$(ReaderUser)')    CREATE USER [$(ReaderUser)] FOR LOGIN [$(ReaderUser)];ALTER ROLE db_datareader ADD MEMBER [$(ReaderUser)];GO

$ sqlcmd -S db.example.net,14330 -U sa -C -b \    -v DbName="appdb" ReaderUser="reporting" \    -i create-reader.sql

Переменные подставляются как текст до отправки пакета, поэтому работают где угодно - в именах объектов, внутри строковых литералов, в USE. В этом же их опасность: никогда не передавайте через скриптовую переменную недоверенный ввод, потому что это конкатенация строк без всякого экранирования. Переменные окружения с тем же именем тоже подхватываются как скриптовые переменные, и это аккуратный способ передавать значения из CI.

Безопасным в конвейере sqlcmd делают коды выхода. По умолчанию sqlcmd сообщает об ошибке, продолжает работу и завершается с кодом 0. Добавьте -b, и при ошибке с уровнем серьёзности 11 и выше он завершится с ненулевым кодом, так что шаг CI упадёт, а не отчитается об успехе после сломанной миграции. Сочетайте это с :on error exit в самом скрипте и с SET XACT_ABORT ON внутри любой транзакции, чтобы сбой на полпути не продолжал работу и не оставлял транзакцию открытой.

$ sqlcmd -S "$DB_HOST,$DB_PORT" -U deployer -d appdb -C -b -i migrate.sql \    || { echo "migration failed"; exit 1; }

Тот же приём делает sqlcmd рукой планировщика на SQL Server Express, где нет SQL Server Agent. Ночное выражение BACKUP DATABASE в файле, которое cron запускает на другой машине с -b, чтобы сбои были видны, - это полноценное задание backup; скрипт и места, требующие внимания, например куда попадает .bak, есть в статье backup и восстановление SQL Server. То же касается обновления статистики и обслуживания индексов: всё, что вы положили бы в шаг задания Agent типа T-SQL, может стать вызовом sqlcmd по таймеру.

Если миграции запускаются из фреймворка приложения, статья миграции Entity Framework Core в рабочей среде показывает, как получить идемпотентный скрипт, который может применить эта же команда.

Выгрузка результатов запроса в файл#

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

$ sqlcmd -S db.example.net,14330 -U sa -d appdb -C -W -h -1 -s "," \    -Q "SET NOCOUNT ON; SELECT OrderId, CustomerId, Total FROM dbo.Orders" \    -o orders.csv

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

• -W обрезает пробелы в конце столбцов, без него каждое значение дополняется до ширины столбца.
• -h -1 убирает строку заголовков и строку из дефисов под ней. Не указывайте его, если заголовки нужны, - тогда строка из дефисов будет второй строкой файла.
• -s "," задаёт разделитель столбцов.
• SET NOCOUNT ON подавляет строку (1234 rows affected) в конце.

Чего sqlcmd не делает, так это не заключает значения в кавычки. Запятая внутри имени ломает выравнивание столбцов в этой строке. Для данных со свободным текстом либо выберите разделитель, который не может встретиться (табуляцию или вертикальную черту), либо получите из запроса JSON через FOR JSON PATH, либо используйте bcp, который к тому же намного быстрее на больших объёмах.

bcp: массовый экспорт и импорт#

bcp читает и пишет данные таблиц массово, тем же быстрым путём, который SQL Server использует для массовой загрузки. У него четыре режима, которые задаются вторым аргументом: out (вся таблица), queryout (результат запроса), in (загрузить файл в таблицу) и format (записать файл формата).

# Export a table in native format: fastest, exact, SQL Server only$ bcp dbo.Orders out orders.dat -S db.example.net,14330 -U sa -d appdb -n -u# Export a query as comma-separated text$ bcp "SELECT OrderId, CustomerId, Total FROM appdb.dbo.Orders WHERE Status = 1" \    queryout paid.csv -S db.example.net,14330 -U sa -c -t, -u# Load the native file into another server's empty table$ bcp dbo.Orders in orders.dat -S other.example.net,14330 -U sa -d appdb \    -n -E -b 10000 -h "TABLOCK" -u

Если не указать -P, bcp запросит пароль, и в интерактивной работе это более безопасный вариант. Что будет в файле, решают флаги формата:

И флаги, которые определяют поведение загрузки:

Работа с кодовыми страницами для текстовых файлов различается в сборках bcp для Windows и для Linux и macOS, так что если в ваших данных есть не-ASCII текст в режиме -c, проверьте параметр -C в документации для своей платформы или используйте -w и избавьте себя от этого вопроса.

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

$ bcp dbo.Orders format nul -c -t, -f orders.fmt -S db.example.net,14330 -U sa -d appdb -u

Добавление -x записывает XML-вариант, который проще читать. Передайте файл через -f orders.fmt в команде in или out.

Быстрая загрузка и альтернативы#

Массовая загрузка быстрее всего, когда она минимально журналируется: таблица заблокирована (-h "TABLOCK"), база в модели восстановления simple или bulk-logged, а цель - куча или пустая таблица. В этих условиях SQL Server журналирует выделение страниц, а не каждую строку, что в несколько раз быстрее и не раздувает журнал транзакций. Почему крупная загрузка в модели full может увеличить журнал на размер данных, объясняет статья модели восстановления SQL Server и рост журнала.

При крупной разовой загрузке в таблицу с несколькими некластерными индексами часто быстрее удалить или отключить их, загрузить данные и перестроить индексы, чем поддерживать их построчно. Ограничения CHECK и внешние ключи при загрузке через bcp по умолчанию не проверяются и затем помечаются как недоверенные; добавьте -h "CHECK_CONSTRAINTS", если их нужно соблюдать, или перепроверьте после загрузки через ALTER TABLE ... WITH CHECK CHECK CONSTRAINT ALL.

Серверные альтернативы - BULK INSERT и OPENROWSET(BULK ...): они читают файл с собственного диска сервера базы данных и начиная с SQL Server 2017 правильно понимают CSV с FORMAT = 'CSV' и FIELDQUOTE. Им нужен файл на сервере, а на Linux - sysadmin, потому что роль bulkadmin там не поддерживается. Для кода приложения у каждого драйвера есть API массового копирования - SqlBulkCopy в .NET, fast_executemany в pyodbc, - который использует тот же протокол, что и bcp, без временного файла. Переносить целую базу, а не несколько таблиц, лучше через backup или BACPAC; варианты сравнивает статья перенос базы на хостинг SQL Server.

Решение проблем#

SSL Provider: certificate chain was issued by an authority that is not trusted. Шифрование по умолчанию в версии 18. Добавьте -C в sqlcmd или -u в bcp либо установите на сервер доверенный сертификат.

Named Pipes Provider: Could not open a connection. Клиент вообще не добрался до сервера. Проверьте хост, порт, запятую между ними и любой firewall на пути.

Login failed for user. Неверный пароль, несуществующий логин или база по умолчанию, которую логин не может открыть. Добавьте -d master, чтобы проверить сам логин.

Invalid object name в bcp. Укажите таблицу вместе с базой (appdb.dbo.Orders) или передайте -d. В режиме queryout всегда используйте трёхчастные имена.

Unexpected EOF encountered in BCP data-file. Разделители в команде не совпадают с файлом - часто это окончания строк Windows \r\n, загружаемые с -r \n. Используйте -r 0x0a или преобразуйте файл.

String or binary data would be truncated. Значение в файле длиннее столбца. SQL Server 2019 и новее называют в сообщении столбец и значение; расширьте столбец или очистите данные.

FAQ#

Есть ли sqlcmd для Linux и macOS?

Да. Microsoft публикует mssql-tools18 с sqlcmd и bcp для основных дистрибутивов Linux и для macOS, а go-sqlcmd устанавливается через Homebrew. Оба подключаются к любому SQL Server на любой платформе.

Как сделать, чтобы sqlcmd не продолжал работу после ошибки?

Укажите -b в командной строке, чтобы он завершался с ненулевым кодом, и поставьте :on error exit в начало скрипта, чтобы он останавливался на первом неудачном пакете. Внутри транзакций добавьте SET XACT_ABORT ON, чтобы транзакция откатывалась, а не оставалась открытой.

Как быстрее всего скопировать одну таблицу между двумя SQL Server?

bcp out в нативном формате (-n) на источнике, затем bcp in с -h "TABLOCK" и размером пакета в пустую таблицу на цели. Нативный формат пропускает весь разбор текста, а блокировка таблицы допускает минимальное журналирование.

Умеет ли bcp выгружать заголовки столбцов?

Напрямую нет. Обычный обходной путь - queryout с UNION ALL строки заголовков, приведённой к тексту, или выгрузка через sqlcmd, который по умолчанию пишет заголовки. Для данных, которые пойдут в электронную таблицу, подойдёт любой вариант.

Почему мой пароль со спецсимволами не работает в sqlcmd?

Оболочка интерпретирует символы вроде $, ! или & до того, как их увидит sqlcmd. Заключите пароль в одинарные кавычки при экспорте SQLCMDPASSWORD или читайте его из файла, а не передавайте через -P.