Как создать скрипт бд ms sql

Create SQL script that create database and tables

I have a SQL database and tables that I would like to replicate in another SQL Server. I would like to create a SQL script that creates the database and tables in a single script.

I can create «Create» script using the SQL Management Studio for each case (Database and Tables), but I would like to know if combining the both «Create» scripts into single script would be enough.

5 Answers 5

Although Clayton’s answer will get you there (eventually), in SQL2005/2008/R2/2012 you have a far easier option:

Right-click on the Database, select Tasks and then Generate Scripts , which will launch the Script Wizard. This allows you to generate a single script that can recreate the full database including table/indexes & constraints/stored procedures/functions/users/etc. There are a multitude of options that you can configure to customise the output, but most of it is self explanatory.

If you are happy with the default options, you can do the whole job in a matter of seconds.

If you want to recreate the data in the database (as a series of INSERTS) I’d also recommend SSMS Tools Pack (Free for SQL 2008 version, Paid for SQL 2012 version).

Программная генерация скриптов для MSSQL

Библиотеки, которые их содержат имеют такие же названия и находятся в папке:
C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies
или
C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies
в зависимости от версии SQL Server.

Выполняемый сценарий

Генератор скриптов создает отдельные папки Tables, Views, Procedures в указанной директории. Создает скрипты создания объектов и сохраняет в отдельных файлах в соответствующих папках. Для таблиц генерация производиться с учетом зависимостей(ключи, индексы и т.д.) и без. Создает общий файл однотипных скриптов в указанной директории.

Как создать базу данных MS SQL SERVER

В данной статье подробно рассмотрен процесс создания базы данных на базе сервера Microsoft SQL Server Express Edition. В первую очередь статья относится к тем, кто желает установить одну из моих программ, использующих Microsoft SQL Server в качестве хранилища данных. Однако статья, надеюсь, будет полезна и всем остальным, кто желает установить на своем компьютере бесплатную редакцию популярной СУБД. Microsoft SQL Server Express Edition — это бесплатный, обладающий развитыми функциональными возможностями выпуск SQL Server, который идеально подходит для обучения, разработки и наращивания функциональности приложений для настольных компьютеров, веб-приложений и небольших серверных приложений, а также для распространения через независимых поставщиков программных продуктов. Microsoft SQL Server 2008 Express Edition допускает базы данных размером не более 4 GB (для 2017 Express Edition – до 10 GB), чего с запасом хватит для большинства моих приложений. Однако в Express Edition есть и другие ограничения, связанные в основном с производительностью.

Шаг 1. Скачиваем Microsoft SQL Server

Скачиваем любую версию Microsoft SQL Server. Лично я предпочитаю качать бесплатные версии непосредственно с microsoft.com. Например Microsoft® SQL Server® 2008 R2 с пакетом обновления 2 (SP2) ― выпуск Express Edition. Конечно, можно использовать и более “свежие” версии, с моими программами будет работать любая. Кроме собственно сервера, нам понадобится еще и SQL Management Studio для создания базы данных и выполнения SQL скриптов. Для Microsoft SQL Server 2008 R2 Express с пакетом обновления 2 (SP2) предлагаются к загрузке следующие файлы:

• SQLEXPR_x86_RUS.exe – версия для 32-х битных операционных систем
• SQLEXPR_x64_RUS.exe – версия для 64-х битных операционных систем
• SQLEXPR32_x86_RUS.exe – версия для 32-х битных операционных систем, почти то же самое что и SQLEXPR_x86_RUS.exe

Включает только компонент SQL Server Database Engine и предназначен для развертывания и распространения через независимых поставщиков программных продуктов

• SQLManagementStudio_x86_RUS.exe – инструмент для работы с базами данных Microsoft SQL Server, 32-битная версия
• SQLManagementStudio_x64_RUS.exe – инструмент для работы с базами данных Microsoft SQL Server, 64-битная версия

Бесплатное, простое в использовании графическое средство управления, предназначенное для настройки, управления и администрирования SQL Server.

• SQLEXPRWT_x86_RUS.exe – это SQLEXPR_x86_RUS + SQLManagementStudio_x86_RUS в одном пакете
• SQLEXPRWT_x64_RUS.exe – это SQLEXPR_x64_RUS + SQLManagementStudio_x64_RUS в одном пакете

Включает компонент SQL Server 2008 Database Engine и среду SQL Server Management Studio Express.

• SQLEXPRADV_x86_RUS.exe – это SQLEXPR_x86_RUS + SQLManagementStudio_x86_RUS + Reporting Services
• SQLEXPRADV_x64_RUS.exe – это SQLEXPR_x64_RUS + SQLManagementStudio_x64_RUS + Reporting Services

Помимо компонентов SQL Server 2008 Express с инструментами, включает поддержку интегрированного полнотекстового поиска и службы Reporting Services

Можно скачать SQLEXPR_xNN_RUS.exe и SQLManagementStudio_xNN_RUS.exe по отдельности и по отдельности их установить. А можно скачать SQLEXPRWT_xNN_RUS.exe (или SQLEXPRADV_xNN_RUS.exe) и установить и сервер и SQL Management Studio из одного пакета. Имхо, второй способ удобнее, но особой разницы нет. 32-битную или 64-битную версию – выбираете сами, в зависимости от типа Вашей операционной системы.

Шаг 2. Устанавливаем Microsoft SQL Server

Запускаем скачанный пакет. Пусть это будет SQLEXPRWT_x64_RUS.exe, так как у меня 64-разрядная операционная система.

Далее выбираем новую установку:

Еще раз выбираем тип установки (ну Microsoft – же, три раза переспросит):

Выбираем состав установки. Обратите внимание – “Средства управления” это и есть SQL Management Studio:

Если у Вам на компьютере более нет экземпляров SQL Server и не планируется, то рекомендуется выбрать “Экземпляр по умолчанию”. Впрочем, если использовать “Именованный экземпляр”, то от этого мало чего изменится, зато потом можно будет добавить еще экземпляры:

Далее можно оставить все как предлагается по умолчанию:

Определяем режим проверки подлинности. Если Вы планируете пользоваться устанавливаем сервером только на одном данном компьютере, то можно смело ставить точку “Режим проверки подлинности Windows” и переходить к следующему шагу:

Но при подключении к серверу по локальной сети уже имеет смысл использовать смешанный режим проверки подлинности и задать пароль администратора (для SQL Server это “sa”). Хотя, в принципе, будет работать и “Режим проверки подлинности Windows” в большинстве случаев. Но при подключении из другой сети будет работать только авторизация через учетную запись SQL Server:

Если сомневаетесь – выбирайте смешанный режим, хуже не будет. Только пароль от sa не забывайте.

Дальнейшие шаги не представляют трудностей и можно просто нажимать “Далее” и дожидаться завершения установки.

Шаг 3. Устанавливаем программу с SQL-скриптом

Прежде чем создавать новую базу, необходимо скачать и установить нужную программу. В комплекте с большинством моих программ имеется и SQL-скрипт для создания структуры базы данных. Он-то нам и понадобится. Для того, чтобы SQL скрипты установились вместе с исполняемыми модулями, нужно выбрать либо тип установки “Полная установка (включая SQL скрипты)“, либо отдельно компонент “SQL сценарии создания и обновления базы данных“:

После этого в каталоге с установленной программой появится подкаталог с SQL-скриптами, который нам и потребуется:

Внутри этой папки Вы и найдете файл создания структуры базы данных CreateDb.sql. Если в папке SQL есть файлы вида “update_XX.sql”, то выполнять их не нужно, они нужны только для модификации уже работающей базы данных с данными при обновлениях. CreateDb.sql уже включает в себя все необходимые изменения.

Шаг 4. Создаем пустую базу данных

Запускаем четверг SQL Server Management Studio через меню “Пуск” – “Все программы” (для Windows 7):

В окне авторизации нужно будет указать имя компьютера, на который был установлен SQL Server и параметры авторизации, заданные при установке:

Если авторизация выполнена успешна, открываем вкладку “Базы данных“:

Нажимаем правой кнопкой мыши и выбираем пункт “Создать базу данных“:

Здесь нужно указать имя базы данных, пока этого достаточно:

Все, новая база данных создана! Пока в этой, только что созданной, базе данных нет ни таблиц, ни индексов, ни функций, ни тем более никаких данных. Как только что установленный в компьютер чистый диск. Создать таблицы и все другие необходимые структуры нужно с помощью файла CreateDb.sql, который мы установили на наше 3.

В принципе, выполнить этот скрипт может и сама программа при первом запуске, когда обнаружит, что в базе данных нет вообще ничего. Для этого просто пропустите следующий шаг и переходите к шагу 6. Но несложно выполнить CreateDb.sql и вручную, чем мы и займемся.

Шаг 5. Создаем таблицы и иные объекты внутри базы данных

Выберите только что созданную базу данных и нажмите кнопку “Создать запрос“:

Перед тем, как создавать таблицы, убедитесь, что команды будут выполняться в нужной базе данных. Если в данном поле выбрана другая база данных – выберите нужную:

и выберите файл CreateDb.sql:

Скрипт будет загружен в SQL Server Management Studio. Нажмите кнопку “! Выполнить“:

В результате должна появиться надпись “Запрос успешно выполнен“:

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

Шаг 6. Подключение программы к созданной базе данных

Если в комплект информационной системы входит несколько модулей (программ), то первым следует запускать модуль администратора. Как правило он называется “Администратор” или “Admin.exe“.

При первом запуске программа может выдать ошибку. Это совершенно нормально. Открываем меню “Файл” – “Настройка соединения с БД“:

На вкладке “Параметры соединения” нажмите кнопку “Изменить“:

На вкладке “Поставщик данных” для подключения к SQL Server должен быть выбран “SQL Server Native Client” или “Microsoft OLE DB Provider for SQL Server“. У Вас могут быть и другие поставщики данных, в том числе и для SQL Server. В крайнем случае можно использовать “Microsoft OLE DB Provider for ODBC Server“, но в этом случае потребуется предварительно настроить ODBC подключение через “Панель управления” – “Администрирование” – “ODBC“.

После того, как выбран поставщик, выбираем сервер и параметры авторизации. Если подключаемся к серверу на том же компьютере, что и база данных, то проще и надежнее всего указать “Использовать встроенную безопасность Windows NT” (но в этом случае поля “Пользователь” и “Пароль” должны быть обязательно пустыми):

После этого выберите только что созданную базу данных из списка баз на сервере и нажмите “Проверить соединение“. Не забудьте сохранить изменения кнопкой “ОК“.

Иногда использование встроенной безопасности невозможно и Вам придется указать имя и пароль подключения к базе данных. В этом случае необходимо поставить галочку “Разрешить сохранение пароля“, чтобы не вводить пароль подключения к программе при каждом запуске. Не бойтесь, пароль хранится в cfg-файле в зашифрованном виде с помощью надежных и проверенных алгоритмов. Кроме того, я рекомендую создать для подключения программы отдельного пользователя SQL Server только для этой базы данных и с достаточно длинным и надежным паролем – не используйте учетную запись администратора ( sa ) для подключения программы к базе данных – это не разумно.

Если все сделано правильно, программа должна подключиться к базе данных и запросить логин и пароль (не всегда, для некоторых программ авторизация может быть и не предусмотрена). Для первого входа используйте пару “admin” / “11111111”, разумеется без “кавычек”.

Кручу, верчу логи при помощи SQL — облегчаем анализ данных

Бывает такая ситуация, что необходимо проанализировать большой объём данных системы логирования событий на предмет аномалий или инцидентов. Просматривать такой массив данных трудно и нецелесообразно. Для этих целей можно обратиться к специализированному программному обеспечению, но нужно знать к какому. Не всегда есть время на изучение. И хорошо, если под конкретные задачи на примете есть несколько вариантов. А если их нет, тогда как быть?

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

2017 или позднее; (SSMS);
1. Понимание синтаксиса sql-запросов (SQL — Structured Query Language) хотя бы на базовом уровне. Если с этим пунктом есть трудности, можно обратиться к официальной документации Microsoft: Учебник. Составление инструкций Transact-SQL

Проверить версию установленного MS SQL Server можно при помощи sql-запроса в той же SSMS:

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

Или посмотреть версию SQL-сервера в Object Explorer, предварительно подключившись к нему.

Для выполнения sql-запросов использовалась SSMS версии 18.4 (более поздние версии также подойдут).

Internet Information Services и лог-файлы

В качестве эксперимента возьмём лог-файлы за пять дней с трёх серверов, на которых запущен Internet Information Services (IIS).

IIS — это веб-сервер, разработанный компанией Microsoft для своих операционных систем. Продукт полностью проприетарный и идёт в комплекте с Windows. Первая версия появилась в Windows NT и продолжает развиваться. По умолчанию IIS выключён в операционной системе.

Запустим диспетчер служб IIS на одном из серверов через меню «Пуск», написав в поисковой строке слово «IIS». Либо нажмите комбинацию клавиш Win+R, введите %SystemRoot%System32InetsrvInetmgr.exe и щёлкните «Ok».

После запуска диспетчера служб IIS (одним из двух способов) нам необходимо перейти в категорию Sites для определения значения ID. Оно понадобится нам для правильной идентификации каталога с лог-файлами интересующего нас сайта. Нам нужны логи для сайта с ID = 2.

Затем выбираем в левой секции Sites сайт по имени и затем в правой части раздела IIS нажимаем на Logging.

В группе Log Files находим расположение каталога (поле Directory), куда IIS сохраняет логи и формат сохраняемого файла (поле Format). В нашем случае лог-файлы сохраняется в W3C-формате. Подробнее с этим форматом можно познакомиться на официальном сайте Microsoft в руководстве W3C Logging. Копируем или запоминаем путь основного каталога с лог-файлами.

Чтобы просмотреть, какие поля включены для логирования, нажимаем на кнопку Select Fields. Эти данные понадобятся нам в дальнейшем для составления sql-скрипта и таблицы для хранения этих же значений. Так как мы забираем логи с 3-х машин, то отмеченные поля должны совпадать на всех машинах. Если по каким-то причинам есть расхождения, мы не сможем загрузить данные по логам в SQL ввиду их неконсистентности.

Далее переходим в директорию, куда IIS сохраняет логи. Структура папок будет такая: W3SVC[ID], где ID — значение нужного нам сайта. Забираем каталог с именем W3SVC2 или только часть содержимого этого каталога на локальную машину (логи за отдельный день с одной машины могут весить от 200 до 700 Мб). Эти действия по сохранению логов повторяем для оставшихся серверов.

Общий объём данных лог-файлов, собранных с серверов за 5 дней составил примерно 5 Гб на локальной машине.

Пишем sql-скрипты

Половина работы выполнена. Лог-файлы мы скачали себе локально на машину. Убедились, что все поля в них одинаковые по структуре. Теперь нужно их каким-то образом загрузить в базу данных SQL Server.

Запускаем SSMS и подключаемся к локальному серверу.

Создание базы данных

Для начала нам нужно создать на локальном сервере базу данных (БД). Желательно создать её на твердотельном SSD-диске, чтобы подсистема ввода-вывода не стала узким местом при импорте и выполнении запросов на большом количестве данных. Можно сделать это двумя способами:

1. написать скрипт;
2. создать БД через интерфейс SSMS.

Для первого варианта воспользуемся инструкцией CREATE DATABASE. Более подробно с ней можно ознакомиться в разделе «Создание базы данных» с использованием Transact-SQL (T-SQL) официальной документации Microsoft. Итоговый скрипт будет выглядеть так (после создания скрипта не забываем выполнить его):

Для второго варианта жмём правой кнопкой мыши на раздел с именем Databases и в контекстном меню выбираем пункт New Database.

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

Создание таблицы для хранения данных

Итак, у нас уже создана БД. Создадим таблицу для хранения логов. Для этого сверимся с заголовками, которые находятся в самих лог-файлах. Выберем один такой файл и откроем его. Нас интересует 4-я строка.

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

С именами столбцов определились, самое время написать скрипт создания таблицы с инструкцией CREATE TABLE. Подробную информацию о ней смотрите на сайте docs.microsoft.com. Итоговый скрипт будет выглядеть так (после создания скрипта не забываем выполнить его):

Максимальные значения текстовых полей (VARCHAR) подобраны эмпирически и их размера вполне должно хватить для импортируемых данных. Но может возникнуть ситуация, когда импорт будет падать с ошибкой. Так может произойти, когда бот или сканер генерирует в запросах «паразитную» нагрузку и значение не помещается в максимальный размер поля. Тогда можно указать для всех текстовых полей максимальное значение параметром MAX (но лучше так не делать и найти «виновника» в данных для указания конечного размера по полю).

Импорт данных

Для импорта нам понадобятся следующие инструкции T-SQL:

И сам итоговый скрипт.

Примечание. В приведённом sql-скрипте по импорту данных можно обойтись без использования таких операторов, как:

• BEGIN TRANSACTION;
• COMMIT TRANSACTION;
• ROLLBACK TRANSACTION.

Запускаем скрипт и идём наливать чай, так как время выполнения импорта может занять от 2 и более минут в зависимости от объёма данных, находящихся в лог-файлах. Результат выполнения скрипта можно увидеть, если нажать на вкладку Messages.

Импорт завершён. Теперь можно узнать, сколько данных мы загрузили. Для этого выполним такой запрос:

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

Например, можно посмотреть, сколько было запросов, с каких IP-адресов и где сервер возвращал в таких запросах статус 404.

Сразу виден топ IP-адресов по количеству запросов. Это либо боты, либо сканеры. Берём первый IP из списка и смотрим на его запросы. И убеждаемся, что это действительно бот/сканер.

Вот так относительно просто можно заниматься аналитикой «небольшого» объёма данных лог-файлов. И необязательно это могут быть только логи с IIS. Загружать в БД SQL для анализа можно почти всё что угодно, было бы желание.