Как создать скрипт бд ms sql
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).
progtask.ru
Как из таблицы сделать INSERT скрипты в MS SQL SERVER
Для создания INSERT скриптов нужно выполнить следующую последовательность действий.
Зайти в SSMS (SQL Server Management Studio) и нажать правой кнопкой мыши по требуемой БД, потом перейти в раздел Tasks и выбрать пункт меню «Generate Scripts…».
Затем в появившемся окне выбрать пункт «Choose Objects».
После выбирайте пункт меню «Script entire database and all database objects» если вам нужны все таблицы, либо «Select specific database objects», если только определённые.
Далее в списке слева выбираем пункт «Set Scripting Options» и жмём на кнопку «Advanced».
В появившемся окне ставим атрибуту «Types of data to script» значение «Data only».
После этого подтверждаем изменение атрибута и указываем путь к файлу, в который будут генерироваться строки sql кода.
Программист ASP.NET MVC C#
Создать скрипт базы данных и отдельно таблицы с условием
Если у Вас стоит MSSQL 2008 и больше, то функция создание скрипта структуры базы данных и содержимого таблицы уже встроена в SQL Server Management Studio
Для этого в контекстном меню базы данных выбираете пункт Tasks->Generating Scripts
На шаге "Choose Objects" выбираете по каким сущностным нужно создать скрип или оставляете всю базу данных
На следующем шаге выбираете "Save to new query window" и жмете кнопку "Advanced"
В настройках Вам нужно указать, для какой версии базы данных хотите получить скрипт и хотите ли вы получить содержимое таблиц
После установки всех настроек, можете переходить к последнему шагу и получить скрипт.
Если у Вас стоит SQL Server Management Studio ниже 2008, или вам нужно получить не все данные из таблицы, а выборочные, то Вам поможет приложение SSMS Tools
После ее установки, нужно перегрузить SQL сервер. Далее что бы получить данные по таблице, нужно в контекстном меню этой таблицы выбрать пункт "SSMS Tools"->"Generate Insert Statements. ".
В появившемся окне нужно написать условие отбора.
SSMS Tools — позволяет делать много интересных вещей, с ними можно познакомиться у них на сайта
Кручу, верчу логи при помощи SQL — облегчаем анализ данных
Бывает такая ситуация, что необходимо проанализировать большой объём данных системы логирования событий на предмет аномалий или инцидентов. Просматривать такой массив данных трудно и нецелесообразно. Для этих целей можно обратиться к специализированному программному обеспечению, но нужно знать к какому. Не всегда есть время на изучение. И хорошо, если под конкретные задачи на примете есть несколько вариантов. А если их нет, тогда как быть?
Выход есть всегда, было бы желание. Поговорим о том, как можно довольно быстро загрузить некий массив таких данных куда-то и заняться его анализом. Для этого нам потребуется уже установленные:
-
2017 или позднее; (SSMS);
- Понимание синтаксиса 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. Оно понадобится нам для правильной идентификации каталога с лог-файлами интересующего нас сайта. Нам нужны логи для сайта с >
Затем выбираем в левой секции 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-диске, чтобы подсистема ввода-вывода не стала узким местом при импорте и выполнении запросов на большом количестве данных. Можно сделать это двумя способами:
- написать скрипт;
- создать БД через интерфейс 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 для анализа можно почти всё что угодно, было бы желание.
Кручу, верчу логи при помощи SQL — облегчаем анализ данных
Бывает такая ситуация, что необходимо проанализировать большой объём данных системы логирования событий на предмет аномалий или инцидентов. Просматривать такой массив данных трудно и нецелесообразно. Для этих целей можно обратиться к специализированному программному обеспечению, но нужно знать к какому. Не всегда есть время на изучение. И хорошо, если под конкретные задачи на примете есть несколько вариантов. А если их нет, тогда как быть?
Выход есть всегда, было бы желание. Поговорим о том, как можно довольно быстро загрузить некий массив таких данных куда-то и заняться его анализом. Для этого нам потребуется уже установленные:
-
2017 или позднее; (SSMS);
- Понимание синтаксиса 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-диске, чтобы подсистема ввода-вывода не стала узким местом при импорте и выполнении запросов на большом количестве данных. Можно сделать это двумя способами:
- написать скрипт;
- создать БД через интерфейс 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 для анализа можно почти всё что угодно, было бы желание.
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).
Получение скриптов стандартных действий SQL Server Management Studio
Поговорим о том, как получить скрипты стандартных действий, заложенных в пользовательский интерфейс Management Studio.
Подменю «Скрипт» (Script)
В SQL Server все действия описываются на внутреннем языке T-SQL, расширении языка SQL. Однажды, вам будет недостаточно стандартных действий, заложенных в Management Studio, и потребуется писать свои скрипты. Некоторые скрипты, которые захочется написать, должны будут содержать действия аналогичные стандартным действиям, уже заложенных в пользовательском интерфейсе Management Studio. Например, мы хотим одним действием выполнить операции: создание полной резервной копии базы данных и ее восстановление в другую базу, перезаписав базу-приемник. Для создания такого скрипта мы можем обратиться к документации, например на MSDN и, изучив необходимые процедуры, написать скрипт. Но есть другой вариант, гораздо проще. Зная как эти действия выполняются в пользовательском интерфейсе Management Studio, мы можем получить скрипты этих действий и объединить в один.
Практически в любом диалоговом окне Management Studio присутствует кнопка-подменю «Скрипт» (Script), нажав на которую, будет выведен скрипт действия в новом окне запроса. Также, помимо вывода в новое окно, раскрыв подменю «Скрипт» (Script), появляется возможность выполнить действия: сохранение скрипта в файл, копирование скрипта в буфер и внесение скрипта в задание.
Подменю «Скрипт»
Давайте воспользуемся этим методом и напишем скрипт для создания резервной копии и ее восстановления в базу приемник.
Создание скрипта
Первым делом, в дереве «Обозревателя объектов» вызовем правой клавишей мыши контекстное меню базы, которую мы хотим забэкапить. Далее, перейдем: «Задачи» (Tasks) → «Создать резервную копию» (Back Up).
Контекстное меню создания резервной копии базы данных
Перед нами появится диалоговое окно, в котором необходимо установить параметры выполнения резервного копирования. Настройки создания резервной копии выходят за рамки данной статьи, поэтому я выберу только тип резервной копии и ее имя. Расположение файла, сгенерированное автоматически, мне подходит.
Диалоговое окно создания резервной копии базы данных
После этого необходимо нажать на кнопку-подменю «Скрипт» (Script), после чего в новом окне будет выведен скрипт действия.
Скрипт создания резервной копии базы данных
Для получения скрипта восстановления нам потребуется наличие данной резервной копии, поэтому, выполним полученный скрипт. Заодно, таким образом, проверим его работоспособность.
Еще одним необходимым шагом является создание пустой базы приемника, если таковой еще не существует. Поскольку создание резервной копии предполагает наличие базы данных, полагаю, отдельно останавливаться на вопросе создания базы не имеет смысла.
Теперь перейдем к скрипту восстановления базы данных. Аналогичным образом из контекстное меню базы источника выберем «Задачи» (Tasks) → «Восстановить» (Restore) → «Базу данных» (Database).
Контекстное меню восстановления базы данных
В открывшемся диалоговом окне необходимо выбрать базу приемник, база источник будет указана изначально верно, но проверить не повредит. В списке резервных копий необходимо выбрать ранее созданную.
Диалоговое окно восстановления базы данных
На вкладке «Параметры» (Options) отметим опцию «Перезаписать существующую базу данных» (Overwrite the existing database)
Диалоговое окно восстановления базы данных. Параметры
Теперь опять нажмем на кнопку-подменю «Скрипт» (Script). Полученный скрипт перенесем в первый файл.
Скрипт создания резервной копии и восстановления в базу приемник
Наш скрипт готов. Теперь его можно сохранить и вызывать при необходимости. Другой его вариант использования — включить скрипт в план обслуживания (статья «Механизм «Планы обслуживания» и механизм заданий MS SQL Server») или добавить в задания.
В заключение
Данный пример лишь демонстрация возможного использования механизма генерации скриптов стандартных действий, но во многих случаях эта опция может сэкономить время и избежать ошибок при написании скрипта.