Подключение базы данных

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

Подключение базы данных выполняется следующим образом:

1. Убедитесь, что окно Management Studio открыто, и что выделен узел вашей базы данных.

2. Щелкните на нем правой кнопкой мыши и выберите в контекстном меню команду Attach (Присоединить).

3. Вы войдете в диалоговое окно, где нужно найти местоположение вашей присоединяемой БД. Кроме того, для этой операции пользователь должен принадлежать к группе администраторов. После того, как все настройки сделаны, нажмите Ok.

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

sp_changedbowner [ [@loginname=] ‘имя_пользователя’

Переименование базы данных:

sp_renamedb [@old_name=] ‘старое_имя’, [@new_name=] ‘новое_имя’

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

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

ALTER DATABASE база_данных

{ ADD FILE <указание_на_файл> [TO FILEGROUP наименование]

| ADD LOG FILE <указание_на_файл>

| REMOVE FILE логическое_имя_файла

| ADD FILEGROUP имя_группы

| REMOVE FILEGROUP имя_группы

| MODIFY FILE <указание_на_файл>

| MODIFY FILEGROUP имя_группы свойство_группы }

где <указание_на_файл> =

(NAME = ’логическое_имя_файла’,

FILENAME = ’физическое_имя_файла’

[, SIZE = размер]

[, MAXSIXE = {максимальный_размер | UNLIMITED} ]

[, FILEGROWTH = шаг_приращения_размера [Mb | Kb | %])

Данная команда позволяет добавлять файл в существующую файловую группу, удалять файлы (при этом удаляется и физический файл), добавлять и удалять файловые группы, изменять физические параметры уже существующих файлов, а также изменять свойства файловых групп: READONLY, READWRITE, DEFAULT (при определении этого свойства, в эту группу будет заносится файлы, у которых в параметрах не определена принадлежность к группе; установленной по умолчанию изначально считается первичная файловая группа).

Сжатие базы данных

Сжатие базы данных – это процесс уменьшения размеров файлов базы данных за счет удаления неиспользуемых частей файла. Существует три способа сжатия базы данных:

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

Автоматическое сжатие данных выполняется постоянно с определенными интервалами, если установлен параметр базы данных autoshrink. При операциях автоматического сжатия нельзя определить, какую часть базы данных необходимо сжать. MS SQL Server пытается освободить значительную часть базы данных самостоятельно. Эти операции выполняются в период наименьшей активности пользователей.

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

DBCC SHRINKDATABASE (‘имя_БД’, [‘процент’] [, NOTRUNCATE | TRUNCATEONLY])

Описание параметров:

имя_БД – имя базы данных, которую необходимо сжать;
процент – количество процентов свободного пространства, которое желательно оставить после сжатия;
NOTRUNCATE – сводное пространство не возвращается операционной системе, а резервируется в файлах, т.е. физически уменьшения размера базы данных не происходит;
TRUNCATEONLY – свободное пространство удаляется за последним используемым в файле экстентом, при этом данные не перемещаются, а параметр процент игнорируется.

Права на сжатие базы данных выданы только членам роли sysadmin и владельцам базы данных. После сжатия базы данных выводится отчет, в котором указывается:

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

Нельзя сжать базу данных до размера меньше первоначального.

Сжатие базы данных можно осуществить также и путем сжатия каждого ее файла с помощью следующей команды:

DBCC SHRINKFILE (‘имя_файла’, [‘конечный_размер’] [, EMPTYFILE | NOTRUNCATE | TRUNCATEONLY ])

Описание параметров:

имя_файла – логическое имя файла, который необходимо сжать;
конечный_размер – желательный размер (целое число в мегабайтах), который должен иметь файл после выполнения сжатия. Если этот параметр не указан или меньше минимально допустимого размера, то файл сжимается до минимально возможного размера;
EMPTYFILE – выполняется перенос данных из файла в другие файлы файловой группы;
NOTRUNCATE – освободившееся место не возвращается операционной системе, т.е. размер файла не уменьшается на самом деле. При этом данные располагаются более компактно и смещаются к началу файла;
TRUNCATEONLY – происходит обрезание файла, начиная с последней используемой страницы. Никакого перемещения данных не происходит.

Резервное копирование данных

MS SQL Server предлагает следующие типы резервного копирования информации:

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

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

sp_addumpdevice ‘тип_устройства’, ‘логическое_имя’, ‘физическое_имя’

Описание параметров:

тип_устройства – тип устройства резервного копирования. Допустимые значения: TAPE (магнитная лента), DISK (магнитный диск);
логическое_имя, физическое_имя – логическое и физическое имя устройства резервного копирования соответственно.

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

BACKUP {LOG | DATABASE } имя_БД

[ FILE = ‘логическое_имя_файла’,...]

[ FILEGROUP = ‘имя_группы’ ]

TO логическое_имя_устройства

[ WITH

[ DESCRIPTION = ‘комментарий’ ]

[ DIFFERENTIAL ]

[ EXPIREDATE = ‘дата’ ]

[ INIT | NOINIT ]... ]

Описание параметров:

DIFFERENTIAL – создается дифференциальная копия базы данных;
EXPIREDATE – определяется дата, после которой резервная копия считается устаревшей и может быть перезаписана;
INIT | NOINIT – система осуществляет или нет инициализацию устройства.

Пример. Создадим устройство для резервирования:

sp_addumpdevice 'disk', 'backupdisk', 'e:\MYDB.bak'

Воспользуемся функцией BACKUP для выполнения резервного копирования:

BACKUP DATABASE MYDB TO backupdisk

Результат выполнения показан на рисунке

Восстановление базы данных

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

RESTORE {LOG | DATABASE } имя_БД

‘файл_или_файловая_ группа’

[ FROM логическое_имя_устройства ]

[ WITH

[ DBO_ONLY ]

[ MOVE ‘логическое_имя_файла’ TO ‘физическое_имя’ ]... ]

Описание параметров:

DBO_ONLY – разрешается доступ к восстановленной базе только владельцам;
MOVE – указывает, какое физическое имя будет соответствовать восстанавливаемому файлу. По умолчанию файл восстанавливается с тем же физическим именем, которое было определено при резервном копировании.

Резервное копирование базы данных и создание расписаний автоматического резервного копирования

Для резервного копирования базы данных следует щелкнуть правой клавишей мыши на элементе, представляющем выбранную базу данных в правой части фрейма SQL Server Management Studio, и во всплывающем меню выбрать пункт Tasks > Backup.... При этом открывается диалоговое окно для управления резервным копированием выбранной базы данных:

В этом окне в Backup Type необходимо выбрать пункт Full (полное копирование базы данных). После этого необходимо добавить одно или несколько устройств резервного копирования, которыми могут быть локальные или сетевые жесткие диски и другие типы носителей информации. Для добавления нового устройства резервного копирования необходимо воспользоваться кнопкой Add диалога, после чего откроется выбора места расположения резерной копии базы данных:

В этом окне надо указать путь к файлу backup-а

Зачастую требуется переодическое резервное копирование базы данных, для этого необходимо вызвать визард Maintenance Plan Wizard. Для этого следует щелкнуть правой клавишей мыши на элементе Managment > Maintenance Plans, в правой части фрейма SQL Server Management Studio (При этом в левой части фрейма необходимо выбрать корневой элемент, представлющий подсоединенный сервер), и во всплывшем меню выбрать пункт Maintenance Plan Wizard, появится окно изображенное на рисунке

В этом окне необходимо ввести название плана и его описание, а также выбрать способ соединения с сервером и нажать кнопку Next. Окно сменится на изображенное на рисунке.

В нем надо выбрать пункт Backup Database(Full) и нажать Next.Окно сменится на изображенное на рисунке.

В нем необходимо указать базу данных для резервного копирования и нажать ОК. После этого станут доступны радио-кнопки. Выбрав Back up to: Disk и указав путь к желаемому расположению файла на диске нажмите Next.

Следующий шаг создание расписания резервного копирования. Нажав кнопку Change появится диалоговое окно настройки расписания изображенное на рисунке

В этом окне с помощью панели Occurs можно определять переодичность запуска процесса резервного копирования (без задания периодичности копирования в пределах одного дня), с помощью панели Daily frequency определять частоту копирования в пределах одного дня, с помощью панели Duration определить временные рамки действия создаваемого расписания.

После подтверждения создания нового расписания кнопками OK на соответствующих формах, оно активируется и становится доступным, как задача планировщика заданий Microsoft SQL Server. Просмотреть эту задачу можно воспользовавшись вкладкой Management > Maintenance Plans в левой части главного фрейма SQL Server Management Studio и два раза щелкнув мышкой на соответствующую задачу планировщика в списке, появившемся в правой части главного фрейма SQL Server Management Studio.

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

Для восстановление базы данных из резервной копии следует щелкнуть правой клавишей мыши оп элементу, представляющему выбранную базу данных в правой части фрейма SQL Server Management Studio, и во всплывающем меню выбрать пункт Tasks > Restore > Database.... При этом открывается диалоговое окно для управления восстановлением выбранной базы данных:

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

Содержание отчета:

Название и цель работы
Задания
Результаты выполнения заданий

Задания

1. Изучите утилиту SQL Server Configuration.

1.1 Запустите утилиту SQL Server Configuration Manager и с ее помощью определите список запущенных на сервере служб. Запишите этот список в отчет.

1.2 На сервере с установленным MS SQL Server 2008 с помощью утилиты Services определите параметры запуска служб MS SQL Server и запишите их в отчет. (Если нет доступа к утилите Services, то при помощи SQL Server Configuration Manager).

1.3 Определите, с помощью каких сетевых библиотек может быть установлено соединение с MS SQL Server (см. пример рис). Какие библиотеки являются активными в момент запуска? Запишите эту информацию в отчет.

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

1.4 При помощи SQL Server Configuration Manager определите, на основе каких сетевых библиотек клиент может подключаться к MS SQL Server (см. пример рис). Запишите список библиотек в отчет.

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

2.Установите соединение с SQL сервером.

2.1 На рабочей станции запустите SQL Server Management Studio и выберите из списка логическое имя сервера, запущенного на вашем компьютере. Если нужного сервера нет в списке, то можно выбрать <Browse for more…> и найти требуемый сервер в списке серверов, к которым может быть выполнено подключение.

2.2 Подключитесь к серверу с использованием средств аутентификации MS SQL Server.

2.3 Для того чтобы написать новый запрос необходимо выполнить команду New Query расположенную на панели инструментов SQL Server Management Studio. В результате откроется новая вкладка, которая предоставляет следующие возможности:

заголовок, в котором указывается логическое имя сервера, текущая база данных и имя пользователя, установившего соединение;
область запроса, используемая для ввода запросов, передаваемых MS SQL Server;
область результатов, в которой отображаются результаты выполнения запроса, а способ отображения задается кнопками Messages (в виде текста) и Results (в виде таблицы) соответственно.

С помощью команды SELECT @@version определите и запищите в отчет информацию об используемой версии MS SQL Server и операционной системы (результат запроса должен быть отображен в текстовом виде) (пример рис.).

Рис. Сведения о версии MS SQL Server

Примечание: Для выполнения запроса необходимо выполнить команду Query – Execute (F5), а для анализа правильности его синтаксической записи можно воспользоваться командой Query – Parse (Ctrl+F5).

SQL Server Management Studio позволяет открывать несколько окон запросов и работать с несколькими базами данных одновременно. В каждом окне устанавливается собственное соединение с MS SQL Server на основе различных учетных записей пользователей и их паролей. Для создания нового подключения используется команда File – New – Database Engine Query.

Содержимое области запроса текущего подключения может быть сохранено в файле на внешнем носителе командой File – Save.

2.5 При помощи панели Object Explorer определите имена поддерживаемых баз данных и какие базы данных сервера являются системными (для этого нужно развернуть узел Databases в панели Object Explorer). Запишите эту информацию в отчет.

3. Изучите параметры конфигурации MS SQL Server.

Конфигурирование службы MSSQLServe r может быть выполнено либо специальной хранимой процедурой, выполняемой в утилите SQL Server Management Studio, либо графическим способом средствами этой же утилиты. Выбор способа не имеет значения, т.к. графический способ осуществляет доступ к системным данным с помощью этой же хранимой процедуры, только в более наглядной форме.

3.1 Для изменения параметров службы с помощью SQL Server Management Studio необходимо выбрать нужный сервер в Object Explorer и в контекстном меню выбрать команду Properties. В появившемся диалоговом окне можно выполнить настройку всех необходимых параметров.

Отобразите список параметров сервера (пример рис).

Рис. Свойства MS SQL Server 2008

На вкладке General отображаются основные сведения о системе: версия операционной системы, объем памяти, количество процессоров и др., а также параметры запуска служб сервера.

Вкладка Memory позволяет управлять выделением памяти для выполнения действий MS SQL Server: либо динамическое управление памятью, либо установить фиксированный размер.

С помощью вкладки Security определяется тип аутентификации пользователей, также определяются параметры аудита доступа к серверу. Можно настроить сервер на использование определенной учетной записи, под которой будет запускаться служба MSSQLServer.

Вкладка Connections позволяет конфигурировать подключения клиентские подключения к серверу. Максимальное количество пользователей, которые могут одновременно подключиться к серверу. Если указано нулевое значение, то их количество составляет 32767.

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

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

3.2 Определите и запишите в отчет корневой каталог сервера, количество процессоров в системе, тип аутентификации пользователей и максимальное количество пользователей, поддерживаемых сервером.

3.3 Изучите остальные свойства MS SQL Server, доступные в этом диалоге.

4. Создать базу данных с именем Stud_< фио_студента >_1 средствами СУБД MS SQL Server 2008 с журналом средствами SQL Server Management Studio и с именем Stud_< фио_студента >_2 средствами Query Editor и запишите в отчет результаты выполнения процедуры sp_helpdb …. Для созданных вами БД

5. Создайте резервную копию одной из созданных вами бд и отобразите в отчете результат выполнения оператора backup.

6. Переименуйте созданную Вами базу данных Stud_< фио_студента >_1 в Stud_< фио_студента > и отобразите в отчете результат выполнения оператора переименования

7. Определите сведения о дисковом пространстве, занимаемом созданной вами БД. Сожмите базу данных так, чтобы она содержала только 25% пространства, доступного ей на текущий момент.

8. Удалите созданную вами базу данных с именем Stud_< фио_студента >_2и отобразите в отчете результат выполнения оператора удаления

9. Отключить/подключить созданную вами БД Stud_< фио_студента > от сервера. Если БД создавалась на жестком диске, то переместить ее на резервный носитель и отобразите в отчете результат выполнения оператора

Контрольные вопросы