Использование пакетных сценариев и SQLCMD для записи данных базы данных
Эта статья посвящена использованию средства сценария DOS Batch командной строки Windows вместе с SQLCMD для записи содержимого каждой таблицы в базе данных в локальную файловую систему. Он показывает, как использовать временные хранимые процедуры для получения преимущества.
Просто чтобы сделать это немного сложнее, мы делаем это в расширенном JSON (формат MongoDB), но мы включили доступ к файлам с процедурами для выполнения этого в обычном JSON или массиве JSON. Также будут размещены файлы схемы JSON. При настройке он также будет выполнять вывод в формате XML или табуляции.
Первоначально это было сделано, потому что нужно было написать утилиту для копирования базы данных SQL Server в MongoDB. Утилита MongoImport не хотела играть с PowerShell, который является предпочтительным способом сценариев любого разработчика Windows. Поэтому это было сделано как старомодный пакетный файл, командный файл, назовите его как хотите.
В этом сценарии мы объединим три слегка нестандартных метода: пакетные сценарии Dos, сценарии SQLCMD и использование временных процедур в SQL Server.
Пакетные сценарии Dos
Пакетные файлы написаны на языке сценариев для оболочки командной строки Microsoft. Он произошел от языка пакетных сценариев MS-DOS, COMMAND.EXE и CMD.EXE. Обычно они имеют расширение .BAT или .CMD. Пакетные файлы должны были быть заменены PowerShell. Проблема с пакетными файлами заключается в том, что этот формат со временем развивался разработчиками MSDOS / Windows, которые не имеют общего представления о том, как должен работать язык сценариев, так что теперь возникла эта путаница переключателей, загадочная пунктуация, странные условности и взломы. Если вам кажется, что мы преувеличиваем, просто прочитайте отличную документацию, встроенную в командную оболочку. Если вас это не ужасает, значит вы не читаете его правильно. Чтобы прочитать полный текст, введите в командной оболочке имена всех команд, за которыми следует /?
Несмотря на все ожидания, сценарии DOS процветали. На сайте Rosetta Code сценарии пакетных файлов DOS конкурируют с другими языками для решения компьютерных задач и задач. Старые группы новостей MSDOS Batch все еще активны. Существует множество архивов сценариев DOS и менеджер пакетов для пакетных сценариев Windows, написанных, конечно, в виде пакетного файла. Множество людей с серой мордой накопили опыт работы с пакетами оболочек DOS и не видят причин для изменений или преобразования своих бесчисленных сценариев. Переполнение стека полно полезных вопросов и ответов. Кто-то даже написал приключенческие игры, ролевые игры и трехмерное моделирование в пакетных сценариях DOS: не потому, что это мудро, а потому, что это сложно и вдохновляюще.
SQLCMD
Исполняемый файл командной строки SQLCMD - замечательный инструмент, но вам действительно нужно часто его использовать, чтобы запомнить все важные параметры, параметры командной строки, переменные сценариев и команды sqlcmd. Самый важный совет, который дает MSDN, - это делать как можно больше за один сеанс SQLCMD и полностью использовать команды sqlcmd, многие из которых работают в режиме SQLCMD SSMS. Вам понадобятся инструкции MSDN по SQLCMD, когда вы будете работать.
Временные процедуры
Мы упоминали об этом в предыдущей статье. Эту технику хорошо использовать, загружая все необходимые процедуры в начале сценария sqlcmd, и процедуры остаются на протяжении всего сценария, поскольку все они выполняются в одном соединении. Разрыв не требуется, поскольку временные процедуры удаляются вместе с временными таблицами при закрытии соединения.
Сценарий
Сценарий хранит учетные данные в виде простого текста в пользовательской области, полагаясь на безопасность NTFS для предотвращения их раскрытия. Это не так хорошо, но намного лучше, чем встраивание пользовательских идентификаторов или паролей в скрипт.
Сценарий связывается с базой данных, чтобы получить список таблиц в базе данных, которую вы хотите использовать, и получает список схем и имен таблиц в виде табличной спецификации. С помощью этого списка он создает сценарий SQLCMD, который записывает результаты для каждой таблицы в отдельный файл в указанном вами каталоге. Затем он выполняет этот скрипт.
Сценарий записывает несколько предварительных данных в файл SQL, которые в конечном итоге будут выполнены. Источник для этой процедуры здесь
|
--specify the name of the error file :Error "C:\Users\phil\RunBatch\error.log" --Execute preliminary script to add the temp procedure(s) :r MyPath\JSONSQLServerRoutines\SaveExtendedJsonDataFromTable.sql USE AdventureWorks2016 :XML on set nocount on |
Затем для каждой таблицы он делает, в данном случае, Person.PhoneNumberType, который записывает в файл SQL, который затем выполняется, инструкции в командах SQL и SQLCMD, чтобы записать JSON-представление содержимого таблиц в файл.
|
GO :Out MyDirectoryPath\AdventureWorks2016\Person.PhoneNumberType.json DECLARE @Json NVARCHAR(MAX) EXECUTE #SaveExtendedJsonDataFromTable @TableSpec='Person.PhoneNumberType',@JSONData=@json OUTPUT Select @json |
Процедура #SaveExtendedJSONDataFromTable выполняет большую часть работы. Она создает документ JSON, содержащий данные из таблицы, написанные на диалекте MongoDB JSON, вызываемые Extended JSON. Если вы хотите экспортировать обычный JSON, используйте взамен SaveJsonDataFromTable.sql. Когда этот сценарий выполняется SQLCMD в качестве заключительной части пакетного сценария DOS, он заканчивается всеми файлами, записанными в выбранный вами каталог.
Вот сценарий. Во-первых, вам нужно изменить параметры в начале скрипта, чтобы указать ваш рабочий каталог, имя, которое вы хотите для своего файла SQLCMD, имя вашего файла ошибок, путь к вашему предварительному файлу SQL (с процедурами или средствами инициализации) и имя базы данных, для которой вы хотите выполнить скрипт. Затем вам нужно создать файл учетных данных вместе с именем вашего сервера. Вы делаете это, выполняя закомментированный раздел в начале, заполняя учетные данные и выполняя только этот пакет.
|
echo -S Bluebottle/Fly -U Queen -P NotAPassword9>%userProfile%\MyServerSqlCmd.txt REM Test out that it was properly saved Set /p TheServer=<%userProfile%\MyServerSqlCmd.txt echo %TheServer% |
Теперь мы готовы попробовать это. Такова небольшая своенравность пакетных файлов DOS, что они могут не запуститься полностью с первого раза. К счастью, ваш журнал ошибок расскажет вам совсем немного, и если вы прочитаете попытку, сделанную в файле SQLCMD, она должна указать, где что-то сломалось. Для отладки сначала удалите @echo в автономном режиме, чтобы увидеть, как выполнялся пакет. Здесь вы найдете больше подсказок.
|
@echo off VERIFY errors 2>nul SETLOCAL ENABLEEXTENSIONS IF ERRORLEVEL 1 echo Unable to enable extensions REM before you start, you need to write out your SQLCMD credentials to a file in your user rem area using code like this, if you use SQL Server Credentials ... Rem echo -S MyServer -U MyUserName -P MyPassword>%userProfile%\MyServerSqlCmd.txt REM ... or like this if you use windows security REM echo -S MyServer >%userProfile%\MyServerSqlCmd.txt REM REM read in your SQLCMD command and credentials Set /p TheServer=<%userProfile%\MyServerSqlCmd.txt REM Specify your work directory. I chose 'RunBatch' in my user area Set workpath=%userProfile%\RunBatch REM specify the name of your SQLCMD file Set TheSQLCMDFileToExecute=%workpath%\SQLCMDFile.sql REM Specify what preliminary file you need to set up Set PreliminarySQL=S:\work\Github\JSONSQLServerRoutines\SaveExtendedJsonDataFromTable.sql REM choose the name of your error file Set Errorfile=%workpath%\error.log REM and specify the name of your database that you want to use Set Database=AdventureWorks2016 REM check whether the workpath directory exists if not exist "%workpath%\" (md %workpath%) if ERRORLEVEL 1 ( echo An error creating "%workpath%" directory occurred goto bombsite) REM check whether the database directory within the workpath directory exists if not exist "%workpath%\%Database%\" (md %workpath%\%Database%) if ERRORLEVEL 1 ( echo An error creating "%workpath%\%Database%" occurred goto bombsite) rem Write out the header to the SQLCMD file to execute ( echo --specify the name of the error file echo :Error "%Errorfile%" echo --Execute prelimiary echo :r %PreliminarySQL% echo USE %Database% echo :XML on echo set nocount on ) >%TheSQLCMDFileToExecute% Rem Create the query that brings you the list of tables Set QUERY="SET NOCOUNT ON; SELECT Object_Schema_Name(object_id)+'.'+name AS The_Tables FROM sys.tables WHERE is_ms_shipped=0;" REM Execute the query and create the entire SQL Command file that will be executed for /F usebackq %%i in (`sqlcmd %TheServer% -d %Database% -h -1 -f 65001 -Q %QUERY%`) do ( rem for every tablespec in the list append the following text .... if ERRORLEVEL 1 ( echo An error accessing %Database% to get the list of tables occurred goto bombsite) ( echo :Out %workpath%\%Database%\%%i.json echo DECLARE @Json NVARCHAR^(MAX^) echo EXECUTE #SaveExtendedJsonDataFromTable @TableSpec=^'%%i^',@JSONData=@json OUTPUT echo Select @json echo GO ) >>%TheSQLCMDFileToExecute% ) Rem Now that is done, we just execute the file sqlcmd %TheServer% -d %Database% -f 65001 -y 0 -i %TheSQLCMDFileToExecute% if ERRORLEVEL 1 ( echo An error running the script %TheSQLCMDFileToExecute% on %TheServer% occurred goto bombsite) goto end :bombsite Echo We bombed! color 0C ENDLOCAL Exit /b 1 :end Echo Yes! We got here at last!! ENDLOCAL Exit /b 0 |
Как только сценарий становится подобным этому, он запускается последовательно. Он работает быстро, и объем кода кажется меньше, чем в PowerShell. Однако, для большинства целей лучше придерживаться PowerShell. Обратите внимание, что командлет Invoke-SQLCMD не реализует команду: OUT SQLCMD, поэтому вы застряли бы при выполнении команды SQLCMD.exe.