Как сохранить базу данных sql server management studio

Резервное копирование и восстановление базы данных в MS SQL Server

26.02.2020
insci
SQL Server
комментариев 16

В этой статье мы рассмотрим, как настроить резервное копирование баз данных в Microsoft SQL Server, покажем, как восстановить базу данных из резервной копии с помощью SQL Server Management Studio и Transact-SQL. Первая часть статьи посвящена теоретическим аспектам резервного копирование в SQL, во второй на примере мы покажем, как настроить регулярное резервное копирование базы данных MS SQL с помощью плана обслуживания и восстановить базу из резервной копии на примере установленного Microsoft SQL Server 2019.

Требования к плану резервного копирования баз данных SQL Server устанавливает бизнес, учитывая несколько критериев:

Допустимый объём потерянных данных (за последний день/час/минуту/секунду);

Следует понимать, что с помощью механизмов резервного копирования невозможно добиться резервирования данных в реальном времени. Для этой цели используются другие технологии высокой доступности SQL Server – группы доступности Always On, зеркалирование баз данных или репликация.

Типы резервного копирования SQL Server

Полное (Full Backup)

Полное резервное копирование делает копию всей базы данных, включая все объекты и данные системных таблиц. Полная резервная копия не будет усекать (truncate) журнал транзакций. Это основной тип резервных копий, который требуется выполнять перед другими типами резервных копий.

Полную резервную копию вы можете восстановить за 1 шаг, так как она не требует других дифференциальных/инкрементальных копий.

Если модель восстановления базы SQL данных установлена как “Полная”, то при восстановлении бекапа вы можете указать параметр “STOPAT”, где указывается время (до секунды) на котором нужно остановить восстановление данных. Например, сотрудник внёс некорректные данные в 14:46:07, с помощью параметра STOPAT вы можете восстановить данные на момент 14:46:06

Дифференциальное

Дифференциальное или разностное резервное копирование — это копирование только тех данных, которые появились с момента последней полной резервной копии.

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

Обычно при использовании разностного резервного копирования используют план по типу “полное раз в N дней, дифференциальное каждые N часов”. Если ежедневный оборот данных достаточно высокий, то данный тип резервных копий может быть неудобен в применении, так как копии будут весить довольно много.

Например, если полная резервная копия весит 300 GB, а дифференциальная спустя час работы 5 GB, то спустя сутки это будет 120 GB, что делает использование данного типа копий нерациональным.

Журнал транзакций

Резервное копирования журнала транзакций копирует все транзакции, которые произошли с момента последнего резервного копирования, а затем урезает журнал транзакций для освобождения дискового пространства.

Восстанавливая журнал транзакций, вы также можете указать параметр STOPAT, как и в восстановлении полной резервной копии.

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

Tail-Log

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

Tail-Log бекап рекомендуется делать перед восстановлением копий журнала транзакций, чтобы не потерять транзакции между последним бекапом и текущим моментом времени.

Copy-only

Этот вид бекапа не может служить “базой” для дифференциальных резервных копий и для копий журнала транзакций. Copy-only бекап не нарушает текущую цепочку резервных копий (полный-> дифференциальный или полный -> копии журналов транзакций) и используется только в том случае, если вам нужно снять полную резервную копию, не задевая текущую цепочку бекапов.

За исключением этих нюансов – ничем не отличается от обычной полной копии.

Частичная резервная копия

Partial backup этот тип резервной копии используется для того, чтобы снять копии с read-only файловых групп. На практике используется редко.

Резервное копирование файлов и файловых групп

Используется для снятия резервных копий определенных файлов или файловых групп.

Модели восстановления базы данных SQL Server

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

Простая модель восстановления

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

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

При использовании этой модели восстановления, следующий функционал SQL Server недоступен:

• Доставка журналов транзакций
• Always On
• Point-In-Time восстановление
• Резервные копии журнала транзакций

Полная модель восстановления

Полная модель восстановления хранит все транзакции в журнале транзакций до усечения журнала (посредством снятия резервной копии журнала).

Это самая “надежная” модель восстановления, при аварийном сбое можно вы сможете восстановить все транзакции, кроме тех, которые не успели завершиться при аварии.

Эта модель нуждается в обслуживании журналов транзакций (регулярные резервные копии), иначе журналы займут всё дисковое пространство.

Восстановление с неполным протоколированием (bulk logged)

Эта модель, также, как и полная, записывает все транзакции в журнал транзакций, за исключением таких операций как:

• SELECT INTO
• BULK INSERT и BCP
• INSERT INTO SELECT
• Операции с индексами (CREATE INDEX, ALTER INDEX REBUILD, DROP INDEX)

В остальном эта модель работает аналогично полной модели восстановления.

Настройка резервного копирования SQL Server с помощью плана обслуживания

Планы обслуживания SQL Server это самый распространенный способ настройки регулярного резервного копирования.

Рассмотрим настройку резервного базы данных на SQL Server копирования по плану:

• Полная резервная копия каждые 24 часа
• Копия журнала транзакций – каждые 30 минут

В SSMS (SQL Server Management Studio) перейдите в раздел Management -> Maintenance Planes и запустите -> мастер создания плана обслуживания (Maintenance Plan Wizard).

Укажите имя плана и выберите режим “Separate schedules for each task”.

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

• Back Up Database (Full)
• Back Up Database (Transaction Log)

Используйте следующую последовательность операций:

Выберите базу данных SQL Server, которую нужно бэкапить и выберите расписание.

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

Укажите сколько будут храниться резервные копии (например, 14 дней).

Нажмите Next и аналогично создайте расписание резервного копирования для журнала транзакций.

Опционально можно указать файл для ведения лога плана обслуживания.

Завершение настройки плана обслуживания SQL Server.

Выполните план обслуживания вручную и проверьте журнал.

Как вы видите была создана полная резервная копия базы данных SQL Server и следом копия журнала транзакций. На этом настройка резервного копирования закончена.

Восстановление базы данных SQL Server из резервной копии

Теперь рассмотрим, как восстановить базы данных SQL Server из резервной копии. Для восстановления базы можно использовать графическую консоль SQL Server Management Studio или язык T-SQL.

Восстановление резервной копии с помощью SQL Server Management Studio

Запустите SSMS, щелкните по разделу Database и выберите пункт Restore Database.

Выберите базу данных. В окне появится список резервных копий, зарегистрированных в SQL Server для этой базы данных.

Для примера, воспользуемся Point-In-Time восстановлением и выберем момент, на который мы хотим восстановить базу данных. Нажмите Timeline.

Выберите опцию “Close existing connections to destination database”, если ваша база данных находится в статус Online

Нажмите ОК. После этого база данных восстановится на выбранный момент времени.

Восстановление базы данных MS SQL Server с помощью T-SQL

Рассмотрим небольшой Transact-SQL скрипт, который выполняет ту же последовательность действия для восстановления базы данных, что и мастер (скрипт был сгенерирован мастером из примера выше).

USE [master]
ALTER DATABASE [TestDatabase2] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
BACKUP LOG [TestDatabase2] TO DISK = N’E:\MSSQL15.NODE2\MSSQL\Backup\TestDatabase2_LogBackup_2020-02-17_15-39-43.bak’ WITH NOFORMAT, NOINIT, NAME = N’TestDatabase2_LogBackup_2020-02-17_15-39-43′, NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY, STATS = 5
RESTORE DATABASE [TestDatabase2] FROM DISK = N’E:\MSSQL15.NODE2\MSSQL\Backup\full.bak’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [TestDatabase2] FROM DISK = N’E:\MSSQL15.NODE2\MSSQL\Backup\trans.bak’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [TestDatabase2] FROM DISK = N’E:\MSSQL15.NODE2\MSSQL\Backup\trans.bak’ WITH FILE = 2, NOUNLOAD, STATS = 5, STOPAT = N’2020-02-17T15:38:23′
ALTER DATABASE [TestDatabase2] SET MULTI_USER
GO

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

Дальше выполняется tail-log бекап, затем восстанавливается полный бекап и следом восстанавливаются бекапы журнала транзакций. Обратите внимание на параметр STOPAT, база данных восстановиться на момент 15:38:23

Рекомендации и best practice по резервному копированию SQL Server

• Резервные копии не должны храниться на том же диске, что и ваш SQL Server. Это правило касается любых резервных копий. При выходе из строя основного дискового массива вы должны иметь доступ к вашим резервным копиям. Если позволяют ресурсы, лучше хранить резервные копии сразу на нескольких разрозненных массивах.
• Процесс резервного копирования должен минимально влиять на работу пользователей. Полные резервные копии лучше делать тогда, когда пользовательская активность на сервере минимальна.
• Регулярно проверяйте целостность резервных копий и проводите тестовые восстановления. Вы всегда должны быть уверены, что ваши бекапы валидны и готовы к восстановлению в любое время.
• Заранее рассчитайте время, необходимое для полного восстановления при аварии. Часто в базах хранится критически важная для бизнеса информация, поэтому ваш руководитель должен знать минимальное время, которое потребуется для восстановления после аварии. Если даже вас об этом не спрашивают, лучше заранее уведомить об этом, чтобы в случае аварии не возникло недопонимания.

Предыдущая статья Следующая статья

Особенности резервного копирования баз данных MS SQL

Резервному копированию баз данных в MS SQL отводится огромное значение. Правильно настроенное, оно поможет уберечь базу данных от повреждений и даже потери. Уделите несколько минут прочтению статьи и напомните (или узнайте) важные аспекты работы по бэкапам, а также как грамотно настроить в MS SQL Server резервное копирование баз данных. Это оградит от многих проблем.

Зачем это нужно

Грамотная отладка резервного копирования баз данных (БД) очень важна. Она помогает сохранить БД в случае каких-либо сбоев (программных, технических и т.д.), повреждений или разрушений в месте хранения. Особенно важно делать копии баз данных владельцам собственных онлайн-проектов, размещенных их на хостинге у провайдера. Бэкап станет «спасательным кругом» на тот случай, если будут утрачены важные данные.

Специалисты рекомендуют настраивать регулярное копирование, используя различные его типы. Лучше всего сохранять копии за последние семь дней.

Пользователь должен понимать, что механизмы резервного копирования БД не смогут выполнять его онлайн, в реальном времени. Для достижения этих целей существуют иные технологии.

Способы создания

В MS SQL Server резервные копии можно создавать несколькими способами. Рассмотрим используемые инструменты:

MS SQL Management Studio

Графический интерфейс SSMS прекрасно подходит для разовых операций. Он может применяться к различным базам данных.

Для этого необходимо:

1. Открыть MS SQL Management Studio. Выбрать БД, которая будет копироваться и кликнуть по ней правой кнопкой мыши. Выбрать Задачи, после чего – Создать резервную копию.
2. Откроется окошко, в котором необходимо оставить полный тип копий и прописать путь к резервному файлу. Если возникла необходимость – путь можно изменить, удалить, создать новый. Файл можно сохранить как на локальном диске, так и на сетевом.
3. После успешного окончания процесса появится уведомление об этом.

Командная строка (sqlcmd)

Создание бэкапов с помощью командной строки sqlcmd используется для автоматизированного копирования любых данных. Может применяться в Windows и Linux.

Для данного способа понадобится утилита sqlcmd:

sqlcmd -S <server> -U <user> -P <password> -Q «BACKUP DATABASE [<database>] TO DISK = N'<file path>’ <options>»

Чтобы автоматизировать скрипт, следует воспользоваться планировщиком. В нем необходимо создать задание по его запуску согласно расписанию.

PowerShell

Создание бэкапов с помощью PowerShell может использоваться только на новых системах, т.к. на старых он не доступен. Специалисты рекомендуют использовать именно этот способ резервирования данных.

Для бэкапа необходимо импортировать модуль import-module sqlps –DisableNameChecking.

Можно воспользоваться синтаксисом: Backup-SqlDatabase -ServerInstance <имя SQL сервера> -Database <имя базы> -BackupFile <путь к файлу с резервной копией>

Как и в предыдущем способе, для запуска скрипта по графику, его следует размещать в планировщике.

Типы резервного копирования

В Microsoft SQL Server принята практика разных типов резервного копирования. Пользователям доступно:

• полное – делается резервная копия всей БД. Может выполняться различными способами;
• дифференциальное или разностное – осуществляется копирование данных с того момента, когда осуществлялось ее последнее полное резервирование. Осуществляется специальной командой с добавлением опции DIFFERENTIAL;
• логов или инкрементальное.

Рассмотрим их подробнее.

Полное копирование (Full Backup)

В SQL Server под полным копированием (Full Backup) понимают создание полной резервной БД, включая все данные и объекты системных таблиц. Полный бэкап не усекает (truncate) журнала транзакций. Она является основным типом создания бэкапов, которое должно предшествовать любому типами резервирования.

Полный бэкап выполняется с помощью различных инструментов (SSMS, T-SQL, PowerShell). Восстановить базу можно всего за один шаг, т.к. для этого не потребуется иных копий (разностных или инкрементальных).

Для полного копирования данных действует ряд ограничений:

• Если оно производилось поздними версиями SQL Server, то такие копии невозможно восстановить в более ранних версиях.
• В транзакциях, как в явных, так и неявных, инструкция BACKUP недопустима.

• С ростом объемов БД полный бэкап может требовать не только большего времени на выполнение операции, но и увеличения места на дисковом пространстве. Потому для больших баз данных рекомендуется проводить не только полное, но и дифференциальное копирование. Но стоит помнить, что прежде чем запускать его либо начинать формирование резервных копий журнала транзакций, следует осуществить хотя бы один полный бэкап данных.
• Системная хранимая процедура sp_spaceused поможет узнать размеры полного бэкапа базы данных.
• Все операции копирования, завершившиеся удачно, заканчиваются добавлением записи в журналы: системных событий и ошибок служб SQL Server. Частое формирование таких копий ведет к накапливанию подобных записей. Это чревато увеличением размеров журналов и затрудняет поиск иных сообщений. Потому, в тех случаях, когда записи журналов не влияют на эффективность скриптов, их можно вообще отключить. Для этого следует воспользоваться флажком трассировки 3226.

• Свойству TRUSTWORTHY в процессе создания резервной копии базы будет присвоено значение OFF.
• В версиях, начиная с SQL Server 2012 (11.x) и более поздних, не поддерживаются параметры PASSWORD и MEDIAPASSWORD. Но восстановление копий с паролями доступно.

Для пользователей, имеющих роли sysadmin, db_owner, db_backupoperator, по умолчанию доступны BACKUP DATABASE и BACKUP LOG. Помешать выполнению резервирования могут проблемы как с владельцем, так и с разрешениями у физических файлов на устройстве.

Полное копирование в SSMS

Создавая задачу в MSSQL Server Management Studio, можно сформировать соответствующий скрипт T-SQL BACKUP. Для этого следует нажать кнопку Скрипт, а затем указать его назначение.

1. Когда в SQL Server компонент Database Engine подключится к нужному экземпляру Microsoft, следует развернуть в обозревателе дерево сервера.
2. Если нужна пользовательская база данных, то следует развернуть узел База данных. Если потребуется обозначить системную БД – развернуть соответствующий узел.
3. Отобрав базу данных, которая будет копироваться, щелкнуть на ней мышкой, выбрать Задачи и затем команду Создать резервную копию… .
4. В появившемся окошке отобранная БД будет приведена в виде раскрывающегося списка. При необходимости, она может быть изменена на любую другую базу данных.
5. В раскрывшемся списке Тип резервной копии следует выбрать необходимый вариант. По умолчанию выбран тип Полная.
6. В разделе Компонент резервного копирования выбрать База данных.
7. Произвести проверку, куда сохраняются файлы по умолчанию в разделе Назначение (в папке ../mssql/data).

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

Для просмотра содержимого, созданного ранее целевого объекта копирования, его необходимо выделить и кликнуть Содержимое. А для удаления такого объекта – кликнуть Удалить.

• При необходимости следует проверить все параметры, которые доступны на страницах параметров: носителя и резервного копирования.
• Кликните ОК для запуска процесса
• При успешном завершении бэкапа необходимо закрыть появившееся диалоговое окно, кликнув ОК.

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

Полное копирование с помощью T-SQL

Для выполнения полного бэкапа базы данных с помощью Transact-SQL, необходимо выполнить соответствующие инструкции BACKUP DATABASE и указать:

• наименование БД для формирования копии;
• устройство, куда будут сохраняться скопированные данные.

Базовая структура синтаксиса T-SQL для создания полной копии БД выглядит следующим образом:

BACKUP DATABASE database TO backup_device [ , …n ] [WITH with_options [ , …o ] ] ;

• database – база данных, которая будет копироваться;
• backup_device [ , …n ] – показывает список устройств, которые будут использованы для резервирования. Может указываться как физическое (когда используются параметры DISK/ TAPE), так и логическое устройство (если оно определено);
• [WITH with_options [ , …o ] ] – указывает необходимое число параметров, o.

При необходимости следует указать нужное количество параметров WITH.

Резервная копия, созданная по команде BACKUP, сохраняется по умолчанию в набор носителей, созданный ранее. Все копии, созданные до этого, сохраняются. С помощью параметра NOINIT можно явно задать значение.

Для форматирования носителя копии используется параметр FORMAT в следующем предложении:

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

Однако при использовании в инструкции BACKUP предложения FORMAT следует проявить крайнюю осторожность. Неправильное использование этого предложения удалит все наборы, которые ранее были созданы и хранились на носителе.

Полное копирование с помощью PowerShell

Чтобы создать полный бэкап в PowerShell, следует использовать командлет (упрощенную команду) Backup-SqlDatabase. Чтобы отметить, что эта резервная копия является полной, необходимо задать параметр BackupAction вместе с Database (оно применяется по умолчанию). Этот параметр не относится к обязательным при полном резервировании БД.

Для использования PowerShell понадобится специальный модуль SqlServer. Выполнение команды Get-Module-Name SqlServer поможет выяснить – установлен модуль либо нет. Для установки модуля понадобится в сеансе PowerShell на правах администратора выполнить команду Install-Module-Name SqlServer.

Открывая PowerShell из SSMS, чтобы подключиться к SQL Server, не потребуются учетных данных. В них нет необходимости, т.к. для подключения между PowerShell и SQL Server используются учетные данные пользователя в SQL Server Management Studio.

Дифференциальное (разностное) копирование

Выборочное резервное копирование данных, появившихся после последнего полного бэкапа называется дифференциальным либо разностным. Оно полностью основывается на последней полного резервного набора.

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

Советы по использованию дифференциальных бэкапов базы данных:

1. Их следует использовать в тех случаях, когда на создание полного требуется много времени.
2. Для уменьшения объемов созданных дифференциальных бэкапов, необходимо делать полный.
3. После формирования полного бэкапа данных, все дифференциальные копии, сделанные ранее, становятся не актуальными.

При использовании дифференциального копирования рекомендуется придерживаться следующего плана его организации:

• полное осуществляется раз в определенное количество дней;
• разностное – каждый раз спустя определенное количество часов.

В том случае, когда оборот данных в день довольно высок, вышеприведенный план окажется неприменим, т.к. его реализация будет занимать много места на дисковом пространстве. К примеру, если вес полного бэкапа составляет 280 GB, а дифференциального, спустя 60 минут, – 4 GB, то по истечению суток вес дифференциальных наборов уже составит 96 GB. Потому, в приведенном примере использование дифференциального резервирования не оправданно.

Другие виды

Кроме вышеперечисленных, существуют иные виды копирования:

• При резервном копировании журнала транзакций выполняется копирование всех транзакций, которые имели место после выполнения предыдущего резервного копирования. После этого происходит урезание журнала, для освобождения дисковое пространство. Этот вид копирования относится к инкрементальному. Для полной модели восстановления необходима вся последовательность резервных наборов, начиная с полной.
• Бэкап Tail-Log, хоть и выделен отдельно, по сути является простой операцией, описанной в предыдущем пункте с опцией NORECOVERY. Его следует выполнять накануне восстановления копии журнала транзакций. Это позволит не упустить те транзакции, которые происходили после крайнего копирования до текущего момента.
• Бэкап Copy-only (создание резервной копии только для копирования) не является базовой ни для разностных, ни для копий журнала транзакций. Создание Copy-only применяется лишь когда необходимо снять полную резервную копию, не задев текущей цепочки резервных копий. Именно эти нюансы и отличают Copy-only от стандартного полного бэкапа данных.
• Частичное резервное копирование Partial backup применяется не часто. Его основная функция – копирование групп файлов read-only.
• Также применяется резервное копирование отдельных файлов и или их групп.

Срок действия

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

Выполняется данная настройка в процессе настройки резервного копирования, в основном окне.

Путь расположения

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

1. На корневом разделе SQL Server кликнуть правой кнопкой мышки и выбрать Свойства.
2. Перейти к разделу Параметры баз данных. В нем опуститься к подразделу Места хранения, используемые базой данных по умолчанию. Именно здесь можно увидеть прописанный путь к месту размещения резервных наборов. Его можно изменить, воспользовавшись кнопкой (…), расположенной справа.

Общие советы

1. Чтобы проверить успешность бэкапа, можно воспользоваться опцией проверки контрольной суммы BACKUP WITH CHECKSUM. Но для больших баз данных применение опции проблематично, т.к. она способна сильно загрузить систему.
2. Бэкапы не следует выполнять на физические диски, где хранятся данные либо журнал транзакций.
3. При использовании версий MS SQL Server 2008 и выше, рекомендуется выполнять сжатие копий БД средствами SQL.
4. Копии лучше сохранять несколько дней. Если какая-нибудь окажется поврежденной, наличие старой будет как нельзя кстати. По истечении этого времени ненужные файлы с расширением bak следует удалить.
5. Перед выполнением копирования рекомендуется проверять базы, воспользовавшись DBCC CHECKDB. Это позволит своевременно получить сообщение о возникновении проблем.
6. Периодически следует обновлять статистику и реорганизацию индексов БД.

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

Import / Export database with SQL Server Server Management Studio

I want to import/export the database itself, the tables, the constraints (foreign keys and so on). I’d rather not get the data with it, but I can get rid of it after if there’s no other way.

So. how do you export a database using MS SQL Server Management Studio ? How do you import it?

The only solution I found was right click on the tables and «script to Create», but I have something like 100 tables, so I’d rather avoid this.

7 Answers 7

Right click the database itself, Tasks -> Generate Scripts.

Then follow the wizard.

For SSMS2008+, if you want to also export the data, on the «Set Scripting Options» step, select the «Advanced» button and change «Types of data to script» from «Schema Only» to «Data Only» or «Schema and Data».

Another solutions is — Backing Up and Restoring Database

Back Up the System Database

To back up the system database using Microsoft SQL Server Management Studio Express, follow the steps below:

Download and install Microsoft SQL Server 2008 Management Studio Express from the Microsoft web site: http://www.microsoft.com/en-us/download/details.aspx?id=7593

After Microsoft SQL Server Management Studio Express has been installed, launch the application to connect to the system database. The «Connect to Server» dialog box displays. In the «Server name:» field, enter the name of the Webtrends server on which the system database is installed. In the «Authentication:» field select «Windows Authentication» if logged into the Windows machine using the Webtrends service account or an account with rights to make changes to the system database. Otherwise, select «SQL Server Authentication» from the drop-down menu and enter the credentials for a SQL Server account which has the needed rights. Click «Connect» to connect to the database.

Select «OK» to complete the backup process.

Repeat the above steps for the «wtMaster» part of the database.

Restore the System Database

To restore the system database using Microsoft SQL Server Management Studio, follow the steps below:

If you haven’t already, download and install Microsoft SQL Server 2008 Management Studio Express from the Microsoft web site: http://www.microsoft.com/en-us/download/details.aspx?id=7593

After Microsoft SQL Server Management Studio has been installed, launch the application to connect to the system database. The «Connect to Server» dialog box displays. In the «Server type:» field, select «Database Engine» (default). In the «Server name:» field, select «\WTSYSTEMDB» where is the name of the Webtrends server where the database is located. WTSYSTEMDB is the name of the database instance in a default installation. In the «Authentication:» field select «Windows Authentication» if logged into the Windows machine using the Webtrends service account or an account with rights to make changes to the system database. Otherwise, select «SQL Server Authentication» from the drop-down menu and enter the credentials for a SQL Server account which has the needed rights. Click «Connect» to connect to the database.

Expand «Databases», right-click on «wt_sched» and select «Delete» from the context menu. Make sure «Delete backup and restore history information for databases» check-box is checked.

Select «OK» to complete the deletion process.

Repeat the above steps for the «wtMaster» part of the database.

Right click on «Databases» and select «Restore Database. » from the context menu. In the «To database:» field type in «wt_sched». Select the «From device:» radio button. Click on the ellipse (. ) to the right of the «From device:» text field. Click the «Add» button. Navigate to and select the backup file for «wt_sched». Select «OK» on the «Locate Backup File» form. Select «OK» on the «Specify Backup» form. Check the check-box in the restore column next to «wt_sched-Full Database Backup». Select «OK» on the «Restore Database» form.

Заметки сисадмина о интересных вещах из мира IT, инструкции и рецензии. Настраиваем Компьютеры/Сервера/1С/SIP-телефонию в Москве

Инструкция по созданию/восстановлению резервных копий баз данных

Создание резервной копии базы данных (backup)

1)Запустить утилиту SQL Server Management Studio (из состава MS SQL Server).

2)Подключиться к серверу под учетной записью администратора или владельца БД (можно использовать встроенную учетную запись «sa», пароль для которой задавался при установке SQL Server, либо выбрать вариант «Проверка подлинности Windows» в случае если текущий пользователь сеанса Windows обладает правами администратора в SQL Server, а также использовать любую другую учетную запись SQL Server или Windows, которая включена в роль «db_owner» копируемой базы):

3)Нажать правой кнопкой мыши на имени копируемой БД (в разделе «Базы данных») и выбрать меню «Задачи/Создать резервную копию»:

4)В разделе «Назначение» указать путь и имя файла (путь всегда задается для компьютера, где установлен сам SQL Server!), в который будет выгружена база данных, для чего использовать кнопки «Удалить» и «Добавить» до тех пор, пока в поле «Создать резервную копию на» не будет отображен ровно один желаемый путь:

5)На странице «Параметры» установить переключатель «Создать резервную копию в новом наборе носителей…» и галочку «Проверить резервную копию после завершения»:

6)Нажать кнопку «ОК».

Резервные копии баз данных SQL Server обычно хорошо сжимаются архиваторами (WinRAR, WinZIP и т.п.), поэтому размер полученного файла (*.bak) можно сильно уменьшить с их помощью.

Восстановление базы данных из резервной копии (restore)

1)Запустить утилиту SQL Server Management Studio (из состава MS SQL Server).

2)Подключиться к серверу под учетной записью администратора или владельца БД (можно использовать встроенную учетную запись «sa», пароль для которой задавался при установке SQL Server, либо выбрать вариант «Проверка подлинности Windows» в случае если текущий пользователь сеанса Windows обладает правами администратора в SQL Server, а также использовать любую другую учетную запись SQL Server или Windows, которая включена в роль «db_owner» восстанавливаемой базы при наличии таковой или серверную роль «dbcreator»):

3)Нажать правой кнопкой мыши на разделе «Базы данных» и выбрать меню «Восстановить базу данных»:

4)На странице «Общие» выполнить следующие действия:

В поле «В базу данных» ввести имя для восстанавливаемой базы (если будет указано имя существующей базы, то это эквивалентно тому, что сначала полностью удалить существующую базу и затем восстановить из резервной копии новую базу, т.е. все данные существующей базы будут утеряны!);

Установить переключатель «С устройства» и указать путь к файлу резервной копии, нажав кнопку «…»;

Установить галочку «Восстановить» в нужной строке (которых может быть несколько, если один файл *.bak содержит несколько резервных копий базы):

5)На странице «Параметры» установить галочку «Перезаписать существующую базу данных» и проверить пути в списке «Восстановить файлы базы данных как» (должны указывать на существующую папку на SQL-сервере, к которой предоставлены права на запись – пути по умолчанию обычно должны заканчиваться папкой DATA, а не просто MSSQL):

6)Нажать кнопку «ОК».

После восстановления базы данных на другой версии SQL Server (например, при переносе с SQL2005 на SQL2008) рекомендуется в свойствах базы данных переключить параметр «Уровень совместимости» на последнюю версию (см. страницу «Параметры» в св-вах БД).

Кроме того, после переноса базы на любой новый SQL-сервер придется заново настроить авторизацию и регулярное резервное копирование в соответствии с инструкцией по установке сетевой версии соответствующей программы.

Для переноса большого количества «имен входа» (логинов) с их паролями на новый сервер можно воспользоваться способом, описанным в статье базы знаний Microsoft. Однако, следует иметь в виду, что данный способ не переносит серверные права логинов (принадлежность «серверным ролям», право на «просмотр состояния сервера» и т.п.), а также не подходит для переноса с SQL 2012 на более ранние версии SQL Server.