Attach sql database: SQL Server Attach and Detach Database Examples

Примеры подключения и отсоединения базы данных SQL Server

Автор: Dinesh Asanka   |
Комментарии (3)   | Связанный: Подробнее > Администрирование базы данных

Проблема

Как вы подключаете и отсоединяете базы данных в SQL Server? В чем разница
между резервным копированием и восстановлением SQL Server и отсоединением и подключением?

Решение

Как правило, отсоединение и присоединение базы данных SQL Server используется для перемещения файлов базы данных.
в файловой системе или между серверами. Если ты хочешь
для перемещения базы данных в другой экземпляр SQL Server, по некоторым мнениям, самым простым вариантом будет
отключить базу данных, переместить файлы базы данных на сервер, а затем подключить
файлы. Другой вариант — сделать резервную копию, скопировать файл резервной копии и восстановить
БД на новый сервер. В зависимости от размера файла и сети
пропускная способность определяет, какой вариант является самым быстрым. Один из вариантов улучшения
время, необходимое для отсоединения\подключения базы данных, будет равно сжатию файлов, если
они имеют значительное количество свободного места.

Предварительная информация о базе данных SQL Server

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

Получить информацию о файле базы данных с помощью SQL Server Management Studio

В

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

Получить информацию о файле базы данных с помощью T-SQL

Чтобы получить эту информацию с помощью T-SQL, вы можете запустить следующую команду для базы данных
вы хотите отсоединить, в данном случае AdvnetureWorks2017.

 ИСПОЛЬЗОВАНИЕ [мастер]
ИДТИ
sp_helpdb N'AdventureWorks2017'

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

Отсоединить базу данных SQL Server

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

Существует два варианта отсоединения базы данных: с помощью SQL Server Management Studio.
(SSMS) или с помощью T-SQL.

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

Сначала щелкните правой кнопкой мыши базу данных в SSMS, которую вы хотите отсоединить, и выберите
Задачи > Отсоединить… , как показано на рисунке ниже.

Затем вы попадете на следующий экран.

На приведенном выше экране есть два флажка, которые вы можете использовать, когда
отсоединение базы данных:

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

В этом случае мы выберем обе опции, как показано ниже, и выберем OK.
После отсоединения статус меняется на Success .

После отсоединения базы данных она будет удалена из баз данных
list в SSMS, но файлы данных и журналов для базы данных останутся прежними.
расположение файловой системы.

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

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

 ИСПОЛЬЗОВАНИЕ [мастер]
ИДТИ
EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks2017'
ИДТИ
 

Следующий сценарий отсоединяет базу данных AdventureWorks2017 и сначала удаляет
существующие подключения к базе данных.

 ИСПОЛЬЗОВАНИЕ [мастер]
ИДТИ
ИЗМЕНИТЬ БАЗУ ДАННЫХ [AdventureWorks2017] УСТАНОВИТЬ ОДНОГО ПОЛЬЗОВАТЕЛЯ С НЕМЕДЛЕННЫМ ОТКАТОМ
ИДТИ
ИСПОЛЬЗОВАТЬ [мастер]
ИДТИ
EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks2017'
ИДТИ
 

Следующий сценарий сначала отключает базу данных, разрывая все соединения и
также обновляет статистику.

 ИСПОЛЬЗОВАНИЕ [мастер]
ИДТИ
ИЗМЕНИТЬ БАЗУ ДАННЫХ [AdventureWorks2017] УСТАНОВИТЬ ОДНОГО ПОЛЬЗОВАТЕЛЯ С НЕМЕДЛЕННЫМ ОТКАТОМ
ИДТИ
ИСПОЛЬЗОВАТЬ [мастер]
ИДТИ
EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks2017', @skipchecks = 'false'
ИДТИ
 

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

Присоединение базы данных SQL Server к файлу журнала

После отсоединения базы данных любым из описанных выше методов файлы mdf, ldf
и ndf (если есть, то это дополнительные файлы данных) нужно скопировать на новый
место, куда вы хотите прикрепить базу данных.

Присоединение базы данных SQL Server с помощью SSMS

Чтобы подключить базу данных, щелкните правой кнопкой мыши Базы данных и выберите
Присоединить… к экземпляру SQL Server, к которому вы хотите присоединить
база данных.

Откроется следующий экран.

Нажмите кнопку Добавить , чтобы найти файл mdf, который вы хотите
прикрепить, выбрать файл и нажать OK .

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

Когда у вас есть правильные файлы, нажмите OK , и вы увидите
на экране появится зеленая галочка, а затем экран закроется.

Присоединение базы данных SQL Server с помощью T-SQL

Это сценарий T-SQL для подключения базы данных с файлами данных и журналов. В
В этом примере мы присоединяем базу данных к другому пути.

 ИСПОЛЬЗОВАНИЕ [мастер]
ИДТИ
СОЗДАТЬ БАЗУ ДАННЫХ [AdventureWorks2017] ВКЛ.
(ИМЯ ФАЙЛА = N'D:\AdventureWorks2017.mdf'),
( ИМЯ ФАЙЛА = N'D:\AdventureWorks2017_log.ldf' )
 ДЛЯ ПРИСОЕДИНЕНИЯ
ИДТИ
 

Присоединение базы данных SQL Server без файла журнала

Для подключения базы данных файл журнала не требуется. В этом сценарии
log-файл будет воссоздан. Если вы планируете прикрепить базу данных без копирования
файл журнала, убедитесь, что вы выполнили CHECKPOINT в базе данных перед отсоединением
базу данных.

Подключение базы данных SQL Server с помощью SSMS

Процесс аналогичен описанному выше, но вы увидите сообщение «Транзакция
файл журнала не найден. Будет создан новый пустой файл журнала». Затем вы можете
нажмите OK, и база данных будет подключена, и будет создан новый файл журнала.

Присоединение базы данных SQL Server с помощью T-SQL

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

 ИСПОЛЬЗОВАНИЕ [мастер]
ИДТИ
СОЗДАТЬ БАЗУ ДАННЫХ [AdventureWorks2017] ВКЛ.
(ИМЯ ФАЙЛА = N'D:\AdventureWorks2017.mdf')
 ДЛЯ ПРИСОЕДИНЕНИЯ

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

 Ошибка активации файла. Имя физического файла «D:\AdventureWorks2017_log.ldf» может быть неверным.
Создан новый файл журнала «D:\AdventureWorks2017_log.ldf». 

Разница между резервным копированием и восстановлением SQL Server и подключением и отсоединением

Это очень распространенный вопрос на собеседовании. Резервное копирование и восстановление намного чище
вариант, чем «Присоединить и отсоединить». Однако резервное копирование и восстановление могут занять больше времени.
чем с помощью опции присоединения и отсоединения. Когда резервное копирование и восстановление выполнены,
это запись, созданная в MSDB для операций, тогда как для присоединения и отсоединения
option не будет никакой записи события.

Разница между SQL Server Detach и Offline

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

Важные примечания

• Параметр «Присоединить и отсоединить» работает только в выпусках SQL Server, но не в Azure.
  База данных SQL, версия SQL Server для хранилища данных SQL Azure.
• Невозможно выполнить отсоединение, если для базы данных задан режим репликации и зеркального отображения.
• Когда база данных находится в подозрительном режиме, вы не можете отсоединить базу данных.
• Вы можете отсоединять только пользовательские базы данных, но не системные базы данных, такие как master,
  msdb, tempdb и модель.
• Если база данных подключена к другой версии SQL Server, предыдущая совместимость будет сохранена, и вы сможете изменить ее после
  прикрепление сделано.

Следующие шаги

• Ознакомьтесь с этими советами по теме:

  • Как подключить базу данных SQL Server без журнала транзакций и с открытым
    Транзакции

  • Ошибка доступа запрещена при подключении базы данных SQL Server

  • Прикрепить базу данных SQL Server без файла журнала

  • Автоматическое создание сценариев подключения и отключения базы данных SQL Server

Об авторе

Динеш Асанка — 10-кратный обладатель награды Data Platform MVP и частый докладчик на местных и международных конференциях с более чем 12-летним опытом работы с базами данных.

Посмотреть все мои советы

Переместить базу данных SQL с помощью метода отсоединения и присоединения

В этой статье рассматривается процесс перемещения базы данных SQL с использованием метода отсоединения и присоединения.

Введение

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

• Отсутствие свободного места : Предположим, у вас есть диск с меньшим количеством свободного места. Вы можете переместить несколько файлов с одного диска на другой, чтобы решить проблемы с местом, и файлы базы данных могут расти.
• Управление базой данных: Иногда требуется разделить файлы данных и файлы журналов на отдельные диски.
• Перемещение базы данных: Нам может потребоваться перемещение базы данных на отдельный сервер, и в этом случае вам не требуется база данных на исходном экземпляре
• Обновление базы данных: Мы также можем обновить базу данных до более высокой версии, переместив файлы данных и журналов в экземпляр более высокой версии.

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

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

Мы можем использовать следующие шаги для перемещения базы данных с помощью метода отсоединения объявления.

Сбор информации базы данных SQL

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

• Запустите команду sp_helpfile в текущем контексте безопасности базы данных и отдельно сохраните информацию:

  Использовать SQLShack перейти Exec sp_helpfile

• Мы также можем использовать представление каталога sys. database_files и получить необходимую информацию:

  1 2 3 4 5 6

  ВЫБЕРИТЕ file_id,        описание_типа,        имя,        физическое_имя,        описание_состояния ИЗ sys.database_files;

  На приведенных выше снимках экрана мы видим, что наша база данных имеет один файл данных и журнал.

• Выполнение команды sp_helpdb с определенной базой данных также возвращает информацию:

  sp_helpdb ‘sqlshack’

• С помощью метода GUI щелкните правой кнопкой мыши базу данных и свойства. В разделе файлов вы также можете просмотреть информацию о файлах:

Обратите внимание, что на приведенных выше снимках экрана файлы базы данных находятся в расположениях по умолчанию. Предположим, мы хотим переместить эти файлы в папку «C:\sqlshack\Demo».

Отсоединить базу данных SQL

В этом методе мы сначала отсоединяем базу данных от исходного экземпляра. Щелкните правой кнопкой мыши нужную базу данных в SSMS и выберите Задачи->Отсоединить:

Он открывает окно отсоединения базы данных, как показано ниже:

Здесь мы получаем следующую информацию:

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

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

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

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

Давайте поставим галочку на Drop Connections и Update Statistics перед отсоединением этой базы данных, как показано ниже:

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

Будет сгенерирован аналогичный скрипт, показанный ниже:

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

Примечание: Также следует проверить параметр AUTO_UPDATE_STATISTICS_ASYNC. Если он включен, вы не можете получить доступ к базе данных в однопользовательском режиме.

Второй сценарий использует хранимую процедуру sp_detach_db в базе данных master. В этом запросе вы можете увидеть параметр @skipchecks = false. В окне отсоединения базы данных ставим галочку на Обновлять статистику. Этот параметр указывает, будет ли SQL Server обновлять статистику перед отсоединением базы данных.

• @Skipchecks false: обновить статистику true
• @Skipchecks true: обновить статистику false

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

Переместить файлы базы данных SQL в нужное место

Теперь переместите файлы базы данных из старого места в новое. Рекомендуется использовать копипаст вместо вырезанной пасты. Копипаста дает вам возможность быстро откатиться в случае каких-либо непредвиденных обстоятельств.

Прикрепить файлы базы данных SQL из нового расположения

После того, как мы скопировали файлы базы данных в новые места в нужном экземпляре SQL, подключитесь к новому экземпляру, щелкните правой кнопкой мыши папку «Базы данных» и в контекстном меню выберите команду «Присоединить»:

Откроется окно «Присоединить базы данных», как показано ниже:

Нажмите кнопку «Добавить» и перейдите к расположению файлов базы данных, выберите файл MDF, как показано ниже:

Нажмите «ОК». SSMS автоматически заполняет связанные файлы и их расположение, если они находятся в том же каталоге. Вы должны проверить путь ко всем файлам базы данных и при необходимости изменить:

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

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

Он использует сценарий создания базы данных с параметром FOR ATTACH для присоединения базы данных из существующих файлов данных и журналов.

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

Ограничение метода присоединения и отсоединения

• Мы не можем отсоединить системные базы данных: master, model, msdb и tempdb
• Мы не можем использовать его для реплицированной, зеркальной или базы данных со снимком
• Подозрительная база данных не может быть перемещена
• Вы не можете подключить базу данных к более ранней версии SQL Server. Например, мы не можем подключить базу данных SQL 2016 к экземпляру SQL 2014.
• Если мы присоединяем базу данных более высокой версии, SQL Server поддерживает совместимость базы данных. Мы можем изменить уровень совместимости, как только база данных будет подключена к сети.

Заключение

В этой статье мы рассмотрели перемещение базы данных SQL с помощью метода «Отсоединить и присоединить». Вам следует просмотреть все доступные варианты перемещения базы данных и выбрать подходящий вариант, соответствующий вашим требованиям.

• Автор
• Последние сообщения

Раджендра Гупта

Привет! Я Раджендра Гупта, специалист по базам данных и архитектор, помогаю организациям быстро и эффективно внедрять решения Microsoft SQL Server, Azure, Couchbase, AWS, устранять связанные проблемы и настраивать производительность с более чем 14-летним опытом.

Я автор книги «DP-300 Administering Relational Database on Microsoft Azure».