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

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

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

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

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

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

Отсоединение баз данных в MS SQL Server

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

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

Отсоединить базу данных можно разными способами, например, на языке T-SQL, т.е. используя процедуру sp_detach_db или также можно воспользоваться графическим инструментом, т.е. SQL Server Management Studio. Мы с Вами разберем оба способа.

Примечание! Все примеры ниже будут рассмотрены на Microsoft SQL Server 2008 R2.

Отсоединение базы данных с помощью sp_detach_db

sp_detach_db – системная хранимая процедура, которая отсоединяет базу данных от экземпляра SQL сервера, а также может выполнить инструкцию UPDATE STATISTICS для всех таблиц перед отключением.

В качестве первого параметра она принимает имя базы данных, в качестве второго TRUE/FALSE (в виде текста), по умолчанию TRUE, т.е. это означает не выполнять инструкцию UPDATE STATISTICS. Еще есть и третий параметр, который указывает, удалять или не удалять полнотекстовый индексный файл, связанный с отключаемой базой данных, по умолчанию TRUE, т.е. это означает что метаданные, связанные с полнотекстовыми данными, сохраняются. Но этот параметр в новых версиях будет удален, честно говоря, не проверял на последних версиях, но разработчики SQL сервера не рекомендуют использовать этот параметр, уже начиная с версии 2008 R2.

Синтаксис:

 sp_detach_db  @dbname =  'database_name', 
			 @skipchecks =  'skipchecks',
			 @keepfulltextindexfile =  'KeepFulltextIndexFile'

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

 exec sp_detach_db @dbname = 'TestBase'

Отсоединение базы данных с помощью Management Studio

Для того чтобы отсоединить базу данных необходимо в обозревателе объектов раскрыть список «Базы данных» и по нужной базе щелкнуть правой кнопкой мыши и нажать «Задачи ->Отсоединить»

И для того чтобы получить точно такой же результат, как и в примере выше жмем сразу «ОК»

Примечание! Как в первом примере, так и во втором не забываем про монопольный доступ, его можно получить, например, установив параметр базы данных «Ограничение доступа» в SINGLE_USER.

Присоединение баз данных в MS SQL Server

Для того чтобы присоединить базу данных, необходимы соответственно файлы этой базы данных, т.е. mdf, ldf. В случаях если журналы транзакций, т.е. файлы ldf не указаны то они будут перестроены, но только в том случае если присоединяемая база данных доступна как для чтения, так и для записи. Если база данных была закрыта аварийно, при этом в ней работали пользователи и были открытые транзакции то файлы журнала транзакций лучше указывать. Присоединять базы данных, полученные из неизвестных источников, не рекомендуется.

Также как и при отсоединении присоединить базу данных можно несколькими способами, мы рассмотри три это с помощью конструкции CREATE DATABASE, с помощью Management Studio, а также рассмотрим пример присоединения базы данных с использованием процедуры sp_attach_db.

Примечание! В документации указано, что возможность присоединять базы данных с помощью процедуры sp_attach_db в будущих версиях SQL сервера будет удалена, поэтому ее не рекомендуют использовать в новых разработках, в SQL Server 2008 R2 эта возможность ещё есть.

Присоединение базы данных с помощью CREATE DATABASE

Конструкция CREATE DATABASE используется не только для создания новой базы данных, но и для присоединения базы данных, которую ранее отсоединили.

Давайте с помощью конструкции CREATE DATABASE присоединим обратно базу TestBase, которую выше мы отсоединили.

 CREATE DATABASE [TestBase] ON 
		(FILENAME = N'C:\TestBase.mdf'),
		(FILENAME = N'C:\TestBase_log.ldf')
 FOR ATTACH
 GO

Как Вы понимаете C:\TestBase.mdf и C:\TestBase_log.ldf это пути к файлам базы данных.

Присоединение базы данных с помощью Management Studio

В Management Studio присоединение делается следующим образом, в обозревателе объектов щёлкните правой кнопкой мыши по пункту «Базы данных» и нажмите «Присоединить»

Затем через кнопку «Добавить» выбираем файл базы данных (MDF-файл) и жмем «ОК»

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

Присоединение базы данных с помощью процедуры sp_attach_db

sp_attach_db – системная хранимая процедура, которая присоединяет базы данных к экземпляру SQL сервера. Как было уже сказано выше, ее не рекомендуют использовать в новых разработках. Первым параметром передается имя базы данных присоединяемой к серверу, оно должно быть уникальным. Вторым и далее (до 16) передаются пути к файлам базы данных.

Синтаксис:

 sp_attach_db @dbname = 'dbname',
		       @filename1 = 'filename_n', 
		       [...16] 

Пример:

 exec sp_attach_db @dbname = 'TestBase',
				@filename1 = N'C:\TestBase.mdf', 
				@filename2 = N'C:\TestBase_log.ldf'

Примечание! В случае если Вам необходимо присоединить более 16 файлов, то нужно использовать CREATE DATABASE database_name FOR ATTACH.

На этом у меня все, надеюсь, материал был Вам полезен, удачи!