Від’єднання і приєднання баз даних в Microsoft SQL Server (Detach і Attach)

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

Отже, давайте почнемо з того, для чого ж адміністратору баз даних може знадобитися від’єднати базу даних, а потім знову приєднати.

Перше – це, звичайно ж, для переміщення бази на інший екземпляр SQL Server, він може розташовуватися як на цьому ж комп’ютері, так і на іншому (або просто для переміщення бази даних на інший фізичний жорсткий диск, наприклад, вільне місце на диску закінчується).

Друге – це тоді, коли наприклад сервер вийшов з ладу, але файли бази даних і журналів транзакцій в порядку і необхідно ці файли відновити, тобто приєднати до нового екземпляру SQL Server на новому сервері.

Ви можете знайти й інше застосування даної можливості, але перераховані вище ситуації, особливо перша, найпоширеніші.

Від’єднання баз даних в Microsoft 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.

Ссылка на основную публикацию