TMSSQL — работа с базами данных MS SQL Server в скриптах на OneScript и из командной строки

Представляю вашему вниманию библиотеку TMSQL для работы с базами данных на MS SQL Server. Библиотека подключается в качестве модуля или класса в скрипты, написанные на OneScript, а также может работать как независимое консольное приложение.

Подробное описание библиотеки и примеры использования в вебинаре ниже.

Из видео вы также узнаете о том как:

  • Развернуть и настроить среду Visual Studio Code для разработки и отладки скриптов на OneScript
  • Разрабатывать простые скрипты
  • Устанавливать и подключать различные имеющиеся библиотеки
  • Разрабатывать свои библиотеки и приложения на OneScript
  • Использовать библиотеку TMSSQL для решения различных повседневных задач

 

Ссылки из вебинара:

Предыдущие вебинары на данную тему:

Пример использования библиотеки в приложении:

Вебинары по SQL-скриптам:

 

Описание библиотеки TMSSQL

Приложение для работы с базами данных на MS SQL Server. Реализовано на OneScript. Работает в режиме:

  • Консольного приложения
  • Библиотеки для разработки скриптов на OneScript

Официальная страница репозитория: https://github.com/Tavalik/TMSSQL

Для подключения к MS SQL-серверу используется COM-Объект ADODB.Connection:

Соединение  = Новый COMОбъект("ADODB.Connection");
Соединение.ConnectionString =
"driver={SQL Server};" +
"server="+ПараметрыПодключения.АдресСервераSQL+";"+
"uid="+ПараметрыПодключения.ИмяПользователяSQL+";"+
"pwd="+ПараметрыПодключения.ПарольПользователяSQL+";" +
"database="+БазаДанных+";";
Соединение.ConnectionTimeout = ПараметрыПодключения.ConnectionTimeout;
Соединение.CommandTimeout = ПараметрыПодключения.CommandTimeout;
Соединение.Open();

Соответственно, приложение работает только в ОС семейства Windows.

 

Установка

Установка через пакетный менеджер opm командой:

opm install tmssql

Или копированием всего репозитория по прямой ссылке: https://github.com/Tavalik/TMSSQL/archive/master.zip

 

Работа в режиме приложения

Исполняемый файл: C:Program Files (x86)OneScriptinTMSSQL.bat

Команды:

  • help — Вывод справки по параметрам
  • createdatabase — Создание базы данных
  • dropdatabase — Удаление базы данных
  • setrecovery — Изменение модели восстановления
  • backupdatabase — Создание резервной копии
  • restoredatabase — Восстановление базы данных
  • shrinkfile — Сжатие файлов базы данных
  • shrinkdatabase — Сжатие базы данных
  • deletefile — Удаление файлов на сервере

Пример использования (bat-файл):

@echo off

setlocal

set server="10.1.1.40"
set uid="sa"
set pwd="pass"
set database="Test_OS_TMSSQL"
set connectionstring=-server %server% -uid %uid% -pwd %pwd% -database %database%

rem Вывод справки
echo ----------------------------------------------
echo help:
call TMSSQL help

rem Создание базы данных
echo ----------------------------------------------
echo createdatabase:
call TMSSQL createdatabase %connectionstring%

rem Изменение модели восстановления
echo ----------------------------------------------
echo setrecovery:
call TMSSQL setrecovery FULL %connectionstring%

rem Создание резервных копий
echo ----------------------------------------------
echo backupdatabase:
set file_FULL=%database%_FILE_FULL.bak
set file_DIFF=%database%_FILE_DIFF.bak
set file_LOG=%database%_FILE_LOG.trn
call TMSSQL backupdatabase "" %file_FULL% FULL %connectionstring%
TIMEOUT 1 /NOBREAK
call TMSSQL backupdatabase "" %file_DIFF% DIFFERENTIAL %connectionstring%
TIMEOUT 1 /NOBREAK
call TMSSQL backupdatabase "" %file_LOG% LOG %connectionstring%
TIMEOUT 1 /NOBREAK

rem Восстановление базы данных
echo ----------------------------------------------
echo restoredatabase:
call TMSSQL restoredatabase %connectionstring%

rem Удаление файлов на сервере
echo ----------------------------------------------
echo deletefile:
call TMSSQL deletefile %file_FULL% %connectionstring%
call TMSSQL deletefile %file_DIFF% %connectionstring%
call TMSSQL deletefile %file_LOG% %connectionstring%

rem Сжатие файлов базы данных
echo ----------------------------------------------
echo shrinkfile:
call TMSSQL shrinkfile LOG %connectionstring%

rem Сжатие базы данных
echo ----------------------------------------------
echo shrinkdatabase:
call TMSSQL shrinkdatabase %connectionstring%

rem Удаление базы данных
echo ----------------------------------------------
echo dropdatabase:
call TMSSQL dropdatabase %connectionstring%

 

Использование в качестве библиотеки

Библиотека подключается как отдельный класс. Экземпляр класса используется для работы с базами на конкретном SQL-Сервере. Может также работать в качестве модуля.

Подключение библиотеки:

#Использовать TMSSQL

Создание класса:

УправлениеMSSQL = Новый УправлениеMSSQL();

Параметры класса:

  • ПараметрыПодключения — Структура параметров подключения. Содержание структуры:
    • АдресСервераSQL — Сетевой адрес MS SQL Server
    • ИмяПользователяSQL — Имя пользователя для подключения к MS SQL Server
    • ПарольПользователяSQL — Пароль пользователя для подключения MS SQL Server
    • ИмяБазыДанныхSQL — Имя базы данных, в которой по умолчанию будут выполняться все запросы
    • ConnectionTimeout — Количество секунд для ожидания подключения, по умолчанию — 30
    • CommandTimeout — Количество секунд для выполнения команды, по умолчанию — 600
  • ТекстОшибки — Переменная для возврата ошибки, если таковая имела место 
  • РежимОтладки — Переменная для включения отладки, по умолчанию — Ложь

Процедуры и функции (описание параметров смотрите к описанию процедур и функций в файле TРаботаСMSSQL.os):

  • ВыполнитьЗапрос() — Выполняет произвольный запрос в указанной базе данных
  • СоздатьБД() — Создает на сервере MS SQL новую базу данных. Если база данных с данным именем уже существует, ничего не происходит
  • УдалитьБД() — Удаляет базу данных с сервера MS SQL с указанным именем. Если база данных не найдена на сервере, ничего не происходит
  • УдалитьФайлНаСервере() — Удаляет файл на сервере MS SQL
  • ПолучитьСтруктуруФайловБД() — Получает данные файлов базы данных из параметров подключения
  • СделатьРезервнуюКопиюБД() — Создает резервную копию базы данных из параметров подключения
  • ПолучитьСписокФайловДляВосстановленияБД() — Получает последовательность файлов для восстановления базы данных из параметров подключения на указанную дату
  • ВосстановитьИзРезервнойКопииБД() — Восстанавливает базу данных из параметров подключения по переданным именам файлов
  • ВосстановитьБД() — Восстанавливает базу данных из параметров подключения на указанную дату
  • ИзменитьМодельВосстановленияБД() — Изменяет модель восстановления базы данных из параметров подключения
  • СжатьБД() — Сжимает базу данных из параметров подключения
  • СжатьФайлыБД() — Сжимает файлы базы данных из параметров подключения

Пример использования (os-файл):

// Подключение библиотеки
#Использовать TMSSQL

// Создадим объект
УправлениеMSSQL = Новый УправлениеMSSQL();

// Введем параметры
ПараметрыПодключения = УправлениеMSSQL.ПараметрыПодключения;
ПараметрыПодключения.АдресСервераSQL = "10.1.1.40";
ПараметрыПодключения.ИмяПользователяSQL = "sa";
ПараметрыПодключения.ПарольПользователяSQL = "pass";
ПараметрыПодключения.ИмяБазыДанныхSQL = "Test_OS_TMSSQL";

БылиОшибки = Ложь;

// Создадим базу данных
Если УправлениеMSSQL.СоздатьБД() Тогда
Сообщить("СоздатьБД: УСПЕШНО");
Иначе
Сообщить("СоздатьБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;

// Получим список файлов базы данных
ТаблицаФайловБД = УправлениеMSSQL.ПолучитьСтруктуруФайловБД();
Если ТаблицаФайловБД <> Неопределено Тогда
Сообщить("ПолучитьСтруктуруФайловБД: УСПЕШНО");
Для Каждого ФайлБД Из ТаблицаФайловБД Цикл
Сообщить(" Имя: " + ФайлБД.ЛогическоеИмя + ", Путь: " + ФайлБД.ФизическоеИмя + ", тип: " + ФайлБД.Тип + ", размер: " + ФайлБД.Размер + " Мб.");
КонецЦикла;
Иначе
Сообщить("ПолучитьСтруктуруФайловБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;

// Сменим модель восстановления базы на полную
Если УправлениеMSSQL.ИзменитьМодельВосстановленияБД("FULL") Тогда
Сообщить("ИзменитьМодельВосстановленияБД: УСПЕШНО");
Иначе
Сообщить("ИзменитьМодельВосстановленияБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;

// Сделаем полную резервную копию
ПолноеИмяФайла = УправлениеMSSQL.СделатьРезервнуюКопиюБД(,,"FULL");
Если ПолноеИмяФайла <> Неопределено Тогда
Сообщить("СделатьРезервнуюКопиюБД: УСПЕШНО в " + ПолноеИмяФайла);
Иначе
Сообщить("СделатьРезервнуюКопиюБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;
Приостановить(6000);
ДатаПолнойКопии = ТекущаяДата();

// Сделаем разностную резервную копию
ПолноеИмяФайла = УправлениеMSSQL.СделатьРезервнуюКопиюБД(,,"DIFFERENTIAL");
Если ПолноеИмяФайла <> Неопределено Тогда
Сообщить("СделатьРезервнуюКопиюБД: УСПЕШНО в " + ПолноеИмяФайла);
Иначе
Сообщить("СделатьРезервнуюКопиюБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;
Приостановить(6000);

// Сделем копию журнала транзакций
ПолноеИмяФайла = УправлениеMSSQL.СделатьРезервнуюКопиюБД(,,"LOG");
Если ПолноеИмяФайла <> Неопределено Тогда
Сообщить("СделатьРезервнуюКопиюБД: УСПЕШНО в " + ПолноеИмяФайла);
Иначе
Сообщить("СделатьРезервнуюКопиюБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;
Приостановить(6000);

// Сделем копию журнала транзакций
ПолноеИмяФайла = УправлениеMSSQL.СделатьРезервнуюКопиюБД(,,"LOG");
Если ПолноеИмяФайла <> Неопределено Тогда
Сообщить("СделатьРезервнуюКопиюБД: УСПЕШНО в " + ПолноеИмяФайла);
Иначе
Сообщить("СделатьРезервнуюКопиюБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;
Приостановить(6000);

// Восстаноим базу на дату создания полной копии
Если УправлениеMSSQL.ВосстановитьБД(ДатаПолнойКопии) Тогда
Сообщить("ВосстановитьБД: УСПЕШНО на дату " + ДатаПолнойКопии);
Иначе
Сообщить("ВосстановитьБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;

// Получим список файлов для восстановления на текущую дату
МассивФайлов = УправлениеMSSQL.ПолучитьСписокФайловДляВосстановленияБД();
Если МассивФайлов <> Неопределено Тогда
Сообщить("ПолучитьСписокФайловДляВосстановленияБД: УСПЕШНО");
Для Сч = 0 По МассивФайлов.Количество()-1 Цикл
Сообщить(" Файл: " + МассивФайлов.Получить(Сч));
КонецЦикла;
Иначе
Сообщить("ПолучитьСписокФайловДляВосстановленияБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;

// Восстановим базу данных по полученнуму ранее массиву файлов
Если УправлениеMSSQL.ВосстановитьИзРезервнойКопииБД(МассивФайлов) Тогда
Сообщить("ВосстановитьИзРезервнойКопииБД: УСПЕШНО");
Иначе
Сообщить("ВосстановитьИзРезервнойКопииБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;

// Удалим файлы резервных копий
Для Сч = 0 По МассивФайлов.Количество()-1 Цикл
ИмяФайлаДляУдаления = МассивФайлов.Получить(Сч);
Если УправлениеMSSQL.УдалитьФайлНаСервере(ИмяФайлаДляУдаления) Тогда
Сообщить("УдалитьФайлНаСервере: УСПЕШНО для " + ИмяФайлаДляУдаления);
Иначе
Сообщить("УдалитьФайлНаСервере: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;
КонецЦикла;

// Переведем базу в простую модель восстановления
Если УправлениеMSSQL.ИзменитьМодельВосстановленияБД("SIMPLE") Тогда
Сообщить("ИзменитьМодельВосстановленияБД: УСПЕШНО");
Иначе
Сообщить("ИзменитьМодельВосстановленияБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;

// Запустим сжатие файлов лога
Если УправлениеMSSQL.СжатьФайлыБД("LOG") Тогда
Сообщить("СжатьФайлыБД: УСПЕШНО");
Иначе
Сообщить("СжатьФайлыБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;

// Запустим сжатие базы данных
Если УправлениеMSSQL.СжатьБД() Тогда
Сообщить("СжатьБД: УСПЕШНО");
Иначе
Сообщить("СжатьБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;

// Удалим базу данных
Если УправлениеMSSQL.УдалитьБД() Тогда
Сообщить("УдалитьБД: УСПЕШНО");
Иначе
Сообщить("УдалитьБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;

// Отчет о работе
Сообщить("");
Сообщить("-----------------------------------------------");
Сообщить("Результат работы модуля: " + ?(БылиОшибки,"БЫЛИ ОШИБКИ","УСПЕШНО"));
Сообщить("-----------------------------------------------");
Сообщить("");

 

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

 

22 Comments

  1. blackhole321

    В чем преимущества перед штатными средствами администрирования?

    Reply
  2. Tavalik

    (1)

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

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

    1. Проверка, есть ли захваченные в хранилище и измененные объекты

    2. Установка блокировки регламентных заданий и начала сеансов

    3. Через указанное количество минут завершение активных сеансов

    4. Расчет последовательности файлов резервных копий рабочей базы для восстановления на указанную дату

    5. Восстановление тестовой базы данных по найденной последовательности файлов

    6. Перевод базы в простую модель восстановления

    7. Сжатие файлов журнала транзакций


    8. Отключение от рабочего хранилища

    9. Подключение к разработочному хранилищу

    10. Обновление конфигурации базы данных

    11. Снятие блокировки регламентных заданий и начала сеансов

    12. Уведомление о результате по электронной почте

    Если говорить про использование в качестве консольного приложения, то в видео также есть пример использования программы совместно с деплойкой.

    Reply
  3. blackhole321

    А какова цель приведенного Вами сценария?

    Вы проверили изменённые объекты в хранилище конфигураций, а затем начинаете выгонять пользователей из рабочей базы. Затем, если я правильно понимаю, Вы восстанавливаете продуктивную базу в тестовую. Зачем выгонять пользователей?

    Reply
  4. Tavalik

    (3)

    Цель сценария — «перезалить» тестовую базу данными из рабочей.

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

    Reply
  5. blackhole321

    (4)ну тогда по памяти типа:

    Alt er database имятестовойбазы

    Set single_user with rollback imediate,

    А затем restore database

    Наверное с десяток строк.

    Выполнить можно множеством способов. В том числе и через powershell, osql etc.

    Вот и пытаюсь понять, в чем изюм.

    Также в ванскрипте есть библиотека

    https://infostart.ru/public/672461/

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

    Reply
  6. Tavalik

    (5)

    Ну, тут есть что пообсуждать.

    Set single_user, не завершит работу пользователей «мягко», с уведомлением. Как вы с хранилищем будете работать, если сервер БД — это отдельный сервер и 1С там нет? Да даже обновление конфигурации базы данных как реализовать? Восстановление баз данных в случае, если рабочий и тестовый серверы БД на разных машинах, тоже та еще задача. Ну и т. д.

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

    Также предлагаю посмотреть вам вот это видео: https://youtu.be/rmpcQFZSDnQ

    В общем, приходите на Хакатон, расскажу подробнее.

    Reply
  7. blackhole321

    (7)чёт я наверное недопонимаю 🙂

    Если мы перезаливаем базу полностью, то нам должно быть все равно как там эти сеансы завершились т.к. на выходе мы получаем консистентную бд из бэкапа.

    Обновление конфигурации штатными средствами.

    Относительно хранилища и проблем не понял. Если Вы имеете ввиду хранилище конфигураций то если я не путаю — это отдельная файловая структура не относящаяся к mssql

    Reply
  8. blackhole321

    (7)Относительно нахождения рабочего и тестового сервера СУБД также не понял, вроде как это не является нештатной конфигурацией для mssql

    Reply
  9. Tavalik

    (9)

    Хорошо, убедили. Сдаюсь.

    Приведите ваш вариант реализации такого сценария.

    Reply
  10. blackhole321

    (11)

    Если Вы про:


    4. Расчет последовательности файлов резервных копий рабочей базы для восстановления на указанную дату

    5. Восстановление тестовой базы данных по найденной последовательности файлов

    6. Перевод базы в простую модель восстановления

    7. Сжатие файлов журнала транзакций

    То это решается одним SQL скриптом, который я думаю, Вы и сами можете создать самостоятельно 🙂 или попросить вашего dba.

    Если Вам необходимо вот по каким-то причинам выполнить из OneScript — используйте соответствующую библиотеку, ссылку на которую я привел выше.

    Посмотрел Ваше видео. Я так понял, что у вас в компании есть некие консультанты, которые в силу сферы своей деятельности не имеют знаний и навыков работы с MSSQL etc. и которым достаточно часто надо восстанавливать базы MSSQL из продуктива в тестовые базы. В то же время вы оказываете услуги по доработке конфигураций на платформе 1С:Предприятие и имеете отдел разработки.

    В этом случае, по моему скромному мнению, вам сам бог велел сделать конфигурацию на 1С 🙂 по управлению базами, чтобы консультант входил в эту базу, выбирал базу, которую ему нужно восстановить и нажимал одну кнопку.

    Соответственно по результатам получал e-mail или сообщение в телеграмм или что-то еще. Все остальное решается выполнением соответствующих скриптов etc. сервером 1С:Предприятие.

    В этом случае, 1С:Предприятие будет выступать некоей управляющей системой, обеспечивающей пользовательский интерфейс, хранение служебной информации (расположение БД, сами скрипты etc.), а также информацию о консультантах, список клиетов и баз, к котоым они имеют доступ и пр.

    Ну вот на мой взгляд как-то так.

    P.S.

    Просто честно говоря так и не понял целевую аудиторию этой тулзы.

    Для dba и сисадминов — оно не надо т.к. полно эффективных средств для выполнения таких задач.

    Для консультантов — слишком сложно и непонятно, т.к. какой-то консольный вариант с какими-то параметрами.

    Для разработчика 1С — зачем оно ему, если можно сделать конфигурацию 1С, попросить админа написать нужные скрипты и вставить их в конфигурацию?

    В качестве учебного примера создания библиотеки на OneScript — да, но тогда это надо как-то явно указать в статье.

    Reply
  11. Infactum

    Oscript штука, в целом, не плохая.

    Но среди «фанатов» уж очень часто проскакивает тренд переписывать и делать обертки для всего и вся. Думают, что таким образом бестолковые коллеги, которые ничего не могут изучить кроме синтаксиса 1С, смогут работать эффективнее.

    Путь в никуда, имхо. Надо общую квалификацию поднимать.

    Reply
  12. Gureev

    Я чот не понял в чем прикол библиотеки.

    Может кто-нибудь объяснить?

    Reply
  13. Semyonat

    Подскажите если у меня SQL сервер ругается что нет доступа, в какую сторону копать? Сервер по этому имени существует логин пароль корректные. Может нужно что то настроить на самом сервере? [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied

    Reply
  14. Semyonat

    Разобрался нужно было в настройках сервера включить TCP/IP и в брандмауэре порт 1433 пробросить.

    Reply
  15. nicxxx

    (15) В Jenkins-e удобно использовать

    Reply
  16. Sergafan10

    Функция ПолучитьСписокФайловДляВосстановленияБД() получает почему-то только последний по дате файл. Это баг или фича?

    Reply
  17. Tavalik

    (19)

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

    А мастер восстановления в MS вам другую цепочку строит?

    Reply
  18. Sergafan10

    (20) ах она смотрит на бакап транзакций… У меня делаются полные бакапы в пределах недели. И в консоли, в наборах данных, тоже подсовывает крайний. Можно это обойти? Мне нужно получить список файлов бакапа из папки с дальнейшим их удалением. Это делается и стандартными средствами чудесно, но интересует через OneScript.

    Reply
  19. Sergafan10

    (20) upd.21

    Да, это можно сделать через Новый Файл(…..), но это работает только со стороны клиента, а как бы на сервер залезть? 🙂

    Reply
  20. Tavalik

    (21)

    Не совсем понятна задача.

    Список файлов для восстановления возвращает MS SQL исходя из имеющиеся у него информации.

    Библиотека в плане восстановления делает следующее:

    — Запрашивает цепочку файлов для восстановления у MS SQL

    — Формирует текст запроса для восстановления указанной базы

    — Отправляет итоговый запрос в MS SQL на выполнение

    А у вас есть еще какие-то файлы, отличные от полного бэкапа? Что это за файлы, и почему MS SQL о них не знает?

    Reply
  21. Sergafan10

    (23) Есть файлы полных бакапов, которые СКЛ создаёт по расписанию. При восстановлении через консоль он предлагает развернуть

    последний по времени бакап. Тот же самый бакап возвращает метод ПолучитьСписокФайловДляВосстановленияБД()

    В таком случае, я полагаю, дело в возврате цепочки скулем.

    Reply
  22. Sergafan10

    (23) Разобрался, скорректировав запрос в функции ПолучитьСписокФайловДляВосстановленияБД(). Всего-то убрать условие выборки TOP 1. Не думал, что всё так просто)

    Reply

Leave a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *