Зараз ви перебуваєте в автономному режимі; очікується повторне підключення до Інтернету

Переміщення баз даних SQL Server за допомогою функцій Detach і Attach

Підсумки
У цій статті описано процедуру змінення розташування файлів даних і журналу для баз даних серверів Microsoft SQL Server 2005, SQL Server 2000 або SQL Server 7.0.

Докладніше про переміщення системних баз даних в SQL Server 2008 див. у статті "Переміщення системних баз даних" електронної документації SQL Server Books Online. Щоб переглянути цю тему, перейдіть на веб-сторінку про переміщення системних баз даних мережі Microsoft для розробників (MSDN).
Додаткові відомості
Дії, необхідні для зміни місця розташування деяких системних баз даних SQL Server, відрізняються від дій зі зміни розташування користувацьких баз даних. Вказівки щодо переміщення таких баз даних надаються окремо.

Примітка. Системні бази даних SQL Server 7.0 несумісні з SQL Server 2000. Не підключайте до SQL Server 2000 бази даних, що поставляються разом із SQL Server 7.0 (master, model, msdb). Під час використання SQL Server 2005 до екземплярів програми можна підключати тільки бази даних SQL Server 2005. В усіх прикладах, наведених у даній статті, передбачається, що програму SQL Server інстальовано в папці D:\Mssql7. Крім того, у прикладах приймається, що всі файли даних та журналів розташовані в папці за промовчанням D:\Mssql7\Data. При цьому всі файли даних і журналу для всіх баз даних переміщуються до папки E:\Sqldata.

За замовчуванням розташування даних для SQL Server 2005 і SQL Server 2000 таке:

Необхідні умови

  • Створіть резервні копії всіх баз даних, зокрема бази даних master, з їхнього поточного розташування.
  • Переконайтеся, що маєте дозволи системного адміністратора (обліковий запис sa).
  • Переконайтеся, що знаєте імена та поточне розташування всіх файлів даних і журналу для переміщуваної бази даних.

    Примітка. Щоб визначити імена та місце розташування всіх файлів, використовуваних базою даних, скористайтеся збереженою процедурою sp_helpfile:
    use <ім'я_бази даних>gosp_helpfilego
  • При цьому необхідно мати винятковий доступ до переміщуваної бази даних. Якщо під час переміщення виникли помилки, не вдається отримати доступ до переміщеної бази даних або запустити SQL Server, перегляньте журнал помилок SQL Server і зверніться до електронної документації SQL Server Books Online, щоб дізнатися більше про ці помилки.

Переміщення користувацьких баз даних

У наступному прикладі переміщується база даних mydb. Ця база даних містить один файл даних Mydb.mdf та один файл журналу, Mydblog.ldf. Якщо переміщувана база даних складається з декількох файлів даних та журналів, необхідно перелічити всі ці файли у списку, який передається збереженій процедурі sp_attach_db. Елементи списку розділяються комами. Оскільки список переміщуваних файлів процедурі sp_detach_db не передається, то виклик даної процедури sp_detach_db не залежить від кількості файлів у базі даних.
  1. Запустіть SQL Server 2005 Management Studio. Для цього натисніть кнопку Пуск і послідовно виберіть пункти Усі програми, Microsoft SQL Server 2005 і SQL Server Management Studio.
  2. Клацніть New Query (Новий запит), а потім відключіть базу даних таким чином:
    use master   go   sp_detach_db 'mydb'   go
  3. Скопіюйте файли даних і журналу з поточного розташування (D:\Mssql7\Data) до нового (E:\Sqldata).
  4. Знову підключіть базу даних. Укажіть нове місце розташування файлів:
    use master  go  sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'  go
    Перевірте зміну розташування файлів за допомогою збереженої процедури sp_helpfile:
    use mydb   go   sp_helpfile   go
    У стовпці filename має відображатися нове місце розташування файлів.
Примітка. У статті 922804 бази знань Microsoft описано проблему з базами даних SQL Server 2005, розташованими у сховищі, підключеному до мережі. Докладніше про це див. у такій статті бази знань Microsoft:
922804 ПОТОЧНЕ ВИПРАВЛЕННЯ: Після відключення бази даних Microsoft SQL Server 2005, що зберігається на пристрої зберігання даних, підключеному до мережі, її не вдається підключити
Візьміть цю проблему до уваги. Крім того, прийміть до уваги дозволи, застосовувані до бази даних під час відключення від SQL Server 2005. Докладніше див. у розділі "Відключення та підключення бази даних" статті "Забезпечення захисту файлів даних та журналів" інтерактивної документації SQL Server Books Online. Щоб переглянути цю тему, перейдіть на веб-сторінку MSDN про захист файлів даних і журналу.

Переміщення зразків баз даних

Щоб перемістити зразки бази даних публікацій і бази даних Northwind в SQL Server 2000 або SQL Server 7.0 (чи зразки баз даних AdventureWorks і AdventureWorksDW в SQL Server 2005), виконайте наведені нижче дії.

Переміщення бази даних model

SQL Server 2005 і SQL Server 2000
В SQL Server 2005 і SQL Server 2000 не можна відключити системну базу даних за допомогою збереженої процедури sp_detach_db. Під час спроби виконати оператор sp_detach_db 'model' з'являється таке повідомлення про помилку:
Server: Msg 7940, Level 16, State 1, Line 1 (Повід. 7940, рівень 16, стан 1, рядок 1)
System databases master, model, msdb, and tempdb cannot be detached (Системні бази даних master, model, msdb та tempdb неможливо відключити).
Щоб перемістити базу даних model, потрібно запустити SQL Server, використовуючи параметри -c та -m, а також позначку трасування 3608. Така позначка не дає змогу серверу SQL Server відновлювати будь-яку базу даних, окрім бази даних master.

Примітка. Після виконання цієї операції доступ до користувацьких баз даних стає неможливим. Під час використання позначки трасування не можна виконувати будь-які операції, окрім описаних нижче. Щоб в SQL Server 2000 до параметрів запуску SQL Server додати позначку трасування 3608, зробіть ось що.
  1. У вікні диспетчера SQL Server Enterprise Manager клацніть ім'я відповідного сервера правою кнопкою миші та в контекстному меню виберіть пункт Властивості.
  2. На вкладці Загальні виберіть Параметри запуску.
  3. Додайте такий параметр:
    -c -m -T3608
Якщо використовується SQL Server 2005, для зміни параметрів запуску служби SQL Server можна застосовувати диспетчер конфігурацій SQL Server. Докладніше про те, як змінити параметри запуску, див. на веб-сторінці MSDN про параметри запуску служби ядра бази даних.

Після додавання параметрів -c та -m і позначки трасування 3608 зробіть ось що.
  1. Зупиніть і знову запустіть SQL Server.
  2. Відключіть базу даних model за допомогою наступних команд:
    use master   go   sp_detach_db 'model'   go
  3. Перемістіть файли Model.mdf та Modellog.ldf з папки D:\Mssql7\Data до папки E:\Sqldata.
  4. Знову підключіть базу даних model за допомогою наступних команд:
    use master   go   sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'   go
  5. У диспетчері SQL Server Enterprise Manager або SQL Server Configuration Manager видаліть -c -m -T3608 з параметрів запуску.
  6. Зупиніть і знову запустіть SQL Server. Зміну місця розташування файлів можна перевірити за допомогою збереженої процедури sp_helpfile. Наприклад, введіть таку команду:
    use model   go   sp_helpfile   go


SQL Server 7.0
  1. Переконайтеся, що не запущено агент SQL Server.
  2. Виконайте вказівки з переміщення користувацьких баз даних.

Переміщення бази даних MSDB

SQL Server 2005 і SQL Server 2000
Щоб перемістити базу даних MSDB, потрібно запустити SQL Server, використовуючи параметри -c та -m, а також позначку трасування 3608. Така позначка не дає змогу серверу SQL Server відновлювати будь-яку базу даних, окрім бази даних master. Для додавання параметрів -c і -m та прапора трасування виконуйте інструкції, наведені в розділі "Переміщення бази даних model". Після додавання параметрів -c та -m та прапора трасування 3608 виконайте наступні дії:
  1. Зупиніть і знову запустіть SQL Server.
  2. Переконайтеся, що не запущено службу агента SQL Server.
  3. Відключіть базу даних msdb наступним чином:
    use mastergosp_detach_db 'msdb'go
  4. Перемістіть файли Msdbdata.mdf та Msdblog.ldf із поточного розташування (D:\Mssql8\Data) у нове (E:\Mssql8\Data).
  5. У диспетчері Enterprise Manager видаліть -c -m -T3608 з параметрів запуску.
  6. Зупиніть і знову запустіть SQL Server.

    Примітка. Якщо при повторному підключенні бази даних msdb SQL Server буде запущено з параметрами -c та -m і прапором трасування 3608, може з'явитися таке повідомлення про помилку:
    Server: Msg 615, Level 21, State 1, Line 1 (Повід. 615, рівень 21, стан 1, рядок 1)
    Could not find database table ID 3, name 'model' (Не вдалося знайти таблицю бази даних, ідентифікатор 3, ім'я 'model').
  7. Знову підключіть базу даних msdb наступним чином:
    use mastergo sp_attach_db 'msdb','E:\Mssql8\Data\msdbdata.mdf','E:\Mssql8\Data\msdblog.ldf' go
Примітка. Якщо ця процедура використовується одночасно з переміщенням бази даних model, то під час відключення бази даних model здійснюється спроба відключення бази даних msdb. У цьому випадку спочатку потрібно повторно підключити базу даних model, а потім — базу даних msdb. Якщо спочатку підключити базу даних msdb, під час спроби повторного підключення бази даних model з'явиться таке повідомлення про помилку:
Msg 0, Level 11, State 0, Line 0 (Повід. 0, рівень 11, стан 0, рядок 0)
A severe error occurred on the current command (Під час виконання поточної команди виникла серйозна помилка). The results, if any, should be discarded (Наявні результати мають бути скасовані).
У такому разі відключіть базу даних msdb, знову підключіть базу даних model, а потім знову підключіть базу даних msdb.

Після переміщення бази даних msdb може з'явитися таке повідомлення про помилку:
Помилка 229: EXECUTE permission denied on object 'ObjectName', database 'master', owner 'dbo' (Дозвіл EXECUTE доступу до об'єкта 'ім'я_об'єкта' відхилено, база даних 'master', власник 'dbo').
Ця проблема виникає через розрив ланцюжка власності. Власники баз даних msdb та master не збігаються. У цьому прикладі власника бази даних msdb було змінено. Щоб обійти проблему, виконайте наступні інструкції Transact-SQL. запустивши програму командного рядка Osql.exe (SQL Server 7.0 і SQL Server 2000) або Sqlcmd.exe (SQL Server 2005):
USE MSDB Go EXEC sp_changedbowner 'sa' Go
Докладніше про це див. у такій статті бази знань Microsoft:
272424 Перевірка ланцюжка володіння об'єктів для декількох баз даних залежить від параметрів входу до системи, зіставлених із власниками об'єктів


SQL Server 7.0
Примітка. Якщо за допомогою цієї процедури переміщуються бази даних msdb та model, спочатку потрібно повторно підключити базу даних model, а потім – msdb. Виконайте такі дії:
  1. Переконайтеся, що не запущено агент SQL Server.
  2. Виконайте вказівки з переміщення користувацьких баз даних.
Примітка. Якщо агент SQL Server запущено, то виконати збережену процедуру sp_detach_db не вдасться, і з’явиться таке повідомлення про помилку:
Server: Msg 3702, Level 16, State 1, Line 0 (Повід. 3702, рівень 16, стан 1, рядок 0)
Cannot drop the database 'msdb' because it is currently in use (Неможливо видалити базу даних 'msdb', тому що вона використовується).
DBCC execution completed (Виконання DBCC завершено). If DBCC printed error messages, contact your system administrator (Якщо DBCC повертає повідомлення про помилки, зверніться до системного адміністратора).

Переміщення бази даних master

SQL Server 2005
Докладніше про те, як перемістити базу даних master і базу даних ресурсів, див. на веб-сторінці MSDN про переміщення баз даних.

Під час переміщення бази даних master або бази даних ресурсів можуть виникати помилки.Докладніше про це див. у такій статті бази знань Microsoft:
918695 Під час інсталяції пакета оновлень 1 для SQL Server 2005 в екземплярі SQL Server 2005 можуть виникати помилки


SQL Server 2000 та SQL Server 7.0
  1. За допомогою диспетчера SQL Server Enterprise Manager змініть шлях до файлів даних та журналів бази даних master.

    Примітка. Тут можна також змінити розташування журналу помилок.
  2. У диспетчері Enterprise Manager клацніть правою кнопкою миші ім’я сервера SQL Server і в контекстному меню виберіть пунктВластивості.
  3. Натисніть кнопку Параметри запуску. З’явиться вікно з наступними елементами:
    -d:\MSSQL7\data\master.mdf -e:\MSSQL7\log\ErrorLog -l:\MSSQL7\data\mastlog.ldf
    . Елемент -d визначає повний шлях до файлу даних бази даних master.

    Елемент -e визначає повний шлях до файлу журналу помилок.

    Елемент -l визначає повний шлях до файлу журналу бази даних master.
  4. Змініть ці елементи в такий спосіб:
    1. Видаліть поточні елементи для файлів Master.mdf та Mastlog.ldf.
    2. Додайте елементи, що визначають нове розташування:
      -dE:\SQLDATA\master.mdf      -lE:\SQLDATA\mastlog.ldf
  5. Зупиніть SQL Server.
  6. Скопіюйте файли Master.mdf та Mastlog.ldf у нове місце розташування (E:\Sqldata).
  7. Перезапустіть SQL Server.

Переміщення бази даних tempdb

Можна перемістити файли tempdb за допомогою інструкції ALTER DATABASE.
  1. Визначте логічні імена файлів бази даних tempdb за допомогою збереженої процедури sp_helpfile:
    use tempdbgosp_helpfilego
    Логічне ім’я кожного файлу відображається у стовпці name. У даному прикладі використовуються імена файлів за промовчанням tempdev та templog.
  2. Виконайте оператор ALTER DATABASE, вказуючи логічні імена файлів:
    use mastergoAlter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')goAlter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')go
    У результаті виконання цих команд мають з'явитися наступні повідомлення, що підтверджують внесення змін:
    Повідомлення 1
    File 'tempdev' modified in sysaltfiles (Файл 'tempdev' змінено у sysaltfiles). Видаліть старий файл після перезапуску SQL Server.
    Повідомлення 2.
    Файл 'templog' змінено у sysaltfiles. Видаліть старий файл після перезапуску SQL Server.
  3. Щоб зміни, внесені в базу даних tempdb, відображалися збереженою процедурою sp_helpfile, необхідно перезапустити SQL Server.
  4. Зупиніть і знову запустіть SQL Server.
Посилання
Докладніше про це див. у таких статтях бази знань Microsoft:
912397 Не вдається запустити службу SQL Server після зміни параметра запуску кластеризованого екземпляра SQL Server 2000 або SQL Server 2005 на неприпустиме значення
274188 У розділі "Виправлення неполадок, пов’язаних із завислим користувачем" електронної документації SQL Server Books Online наведено не всі відомості
246133 Перенесення імен користувачів і паролів між екземплярами SQL Server
168001 Дані для входу користувачів і дозволи на доступ до бази даних після відновлення бази даних можуть стати неправильними

Додаткові відомості див. у наступних книгах:
Корпорація Майкрософт
Навчальний пакет Microsoft SQL Server 7.0 System Administration Training Kit
Microsoft Press, 2001 р.
Корпорація Майкрософт
Пакет ресурсів Microsoft SQL Server 2000 Resource Kit
Microsoft Press, 2001 р.
Moving database files new location move place
Властивості

Ідентифікатор статті: 224071 – останній перегляд: 02/26/2014 15:32:00 – виправлення: 16.0

Microsoft SQL Server 2005 Standard Edition, Microsoft SQL 2005 Server Enterprise, Microsoft SQL Server 2005 Express Edition, Microsoft SQL 2005 Server Workgroup, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 7.0 Standard Edition

  • kbsqlsetup kbinfo KB224071
Зворотний зв’язок