Как перенести базу данных sql server на другой компьютер

Как скопировать базу SQL Server?

Здравствуйте. Нужно скопировать небольшую базу на SQL Server с одного компа на другой. Тыкните, пожалуйста, носом в инфу для чайников.

В Microsoft SQL Server Management Studio нажимаем ПКМ на БД, которую будем переносить (к примеру, 1C_BUH_test), далее «Задачи» -> «Создать резервную копию» (настройки, оставляем по-умолчанию).
Переносим созданый *.bak файл резервной копии БД, на другой SQL-сервер.
В Microsoft SQL Server Management Studio нажимаем ПКМ на «Базы данных», далее «Восстановить базу данных».
Указываем перенесенный ранее, *.bak файл:
В выпадающем списке выбираем БД в которую будет производиться восстановление данных (к примеру, 1C_BUH_test).
Настройки сделаны нажимаем «ОК».
В случае успешного восстановления БД, будет уведомление.
- Обязательно проверьте владельца БД после восстановления.
- ПКМ на БД -> «Свойства» -> закладка «Файлы» -> поле «Владелец» (если оно пустое, то необходимо указать «Владельца» БД).
Указав «Владельца» БД нажимаем «ОК».

Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.

Перенос всех баз данных MS SQL Server на другую машину

Недавно возникла необходимость переноса всех БД (>50 на одном экземпляре SQL Server) из dev-окружения на другой экземпляр SQL Server, который располагался на другом железе. Хотелось минимизировать ручной труд и сделать всё как можно быстрее.

Disclaimer

Скрипты написаны для одной конкретной ситуации: это dev-окружение, все базы в простой модели восстановления, файлы данных и журналы транзакций лежат в одной куче.

Всё, что написано дальше относится только к этой ситуации, но вы можете без особых усилий допилить их под себя (свои условия).

В скриптах не используются новомодные STRING_AGG и прочие приятные штуки, поэтому работать всё должно начиная с SQL Server 2008 (или 2008 R2, не помню где появилось сжатие бэкапов). Для более старых версий нужно убрать WITH COMPRESSION из команды бэкапа, но тогда разницы по времени с копированием файлов может уже и не быть.

Это не инструкция — «как надо» делать такой перенос. Это демонстрация того, как можно использовать метаданные в dynamic SQL.

Конечно, самым быстрым способом было бы просто переподключить полку с дисками к новому серверу, но это был не наш вариант. Detach — копирование — Attach рассматривался, но не подошёл, поскольку канал был довольно узким и перенос БД в несжатом виде занял бы довольно большой промежуток времени.

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

Так был написан «генератор скриптов»:

На выходе получаем готовые команды для создания бэкапов в нужное место, перевода БД в offline, чтобы их пользователи не могли с ними работать на старом сервере и скрипты для восстановления полученных бэкапов на новом сервере (с автоматическим перемещением всех файлов данных и журналов транзакций в указанное место).

Проблема с этим такая — либо кто-то должен сидеть и по очереди выполнять все скрипты (бэкап-офлайн-восстановление), либо кто-то должен сначала запустить все бэкапы, потом отключить все базы, потом всё восстановить — действий меньше, но нужно сидеть и отслеживать.

Хотелось автоматизировать все эти операции. С одной стороны, всё просто — уже есть готовые команды, заворачивай в курсор и выполняй. И, в принципе, я так и сделал, добавил новый сервер как linked server на старом и запустил. На локальном сервере команда выполнялась через EXECUTE (@sql_text);, на linked server — EXECUTE (@sql_text) AT [linkedServerName].

Таким образом, последовательно выполнялись операции — бэкап локально, перевод локальной БД в офлайн, восстановление на Linked server. Всё завелось, ура, но мне показалось, что можно немного ускорить процесс, если бэкапы и восстановления выполнять независимо друг от друга.
Тогда придуманный курсор был разделён на две части — на старом сервере в курсоре каждая база бэкапится и переводится в офлайн, после чего второй сервер-таки должен понять, что появилось новое задание и выполнить восстановление БД. Для реализации этого механизма я использовал запись в таблицу на linked server и бесконечный цикл (мне было лень придумывать критерий остановки), который смотрит не появилось ли новых записей и пытается восстановить что-нибудь, если появились.

Решение

На старом сервере создаётся и заполняется глобальная временная таблица ##CommandList, в которой собираются все команды и там же можно будет отслеживать статус выполнения бэкапов. Таблица глобальная, чтобы в любой момент из другой сессии можно было посмотреть — что там сейчас происходит.

Посмотрим что там оказалось (SELECT * FROM ##CommandList):

Отлично, там собираются все команды для бэкапа/восстановления всех нужных БД.

На новом сервере была создана БД Maintenance и в ней таблица CommandList, которая будет содержать в себе информацию о восстановлении баз:

На старом сервере был настроен linked server, смотрящий на новый экземпляр SQL Server. Скрипты, которые приведены в этом посте, я писал дома и не заморачивался с новым экземпляром, использовал один и его же подключил как linked server сам к себе. Поэтому тут у меня и пути одинаковые и unc-path локальный.

Теперь можно объявлять курсор, в котором бэкапить базы, отключать их и писать на linked server команду для восстановления:

Каждое действие «логируется» на вкладке Messages в SSMS — там можно наблюдать за текущим действием. Если использовать WITH LOG в RAISERROR, в принципе, можно засунуть это всё в какой-нибудь job и потом смотреть логи.

Во время выполнения курсора можно обращаться к ##CommandList и смотреть в табличном виде что и как происходит.

На новом сервере, параллельно, крутился бесконечный цикл:

Всё что он делает — смотрит в таблицу CommandList, если там есть хотя бы одна необработанная запись — берёт имя БД и команду для восстановления и пытается выполнить с помощью EXEC (@sql_text);. Если записей нет, ждёт 30 секунд и пробует снова.

И курсор, и цикл обрабатывают каждую запись только один раз. Не получилось? Пишем сообщение об ошибке в таблицу и больше сюда не возвращаемся.

Про условие остановки — мне на самом деле было лень. Пока набирал текст, придумал минимум три решения — как вариант — добавление флагов «Готов к восстановлению \ Не готов к восстановлению \ Завершён», заполнение списка БД и команд сразу, при заполнении ##CommandList на старом сервере и обновление флага внутри курсора. Останавливаемся, когда не осталось «готовых к восстановлению» записей, так как нам сразу известен весь объём работ.

Выводы

А нет никаких выводов. Подумал, что кому-то может быть полезно/интересно посмотреть как использовать метаданные для формирования и выполнения dynamic sql. Приведённые в посте скрипты в том виде, как есть, мало пригодны для использования на проде, однако, их можно немного допилить под себя и использовать, например, для массовой настройки log shipping / database mirroring / availability groups.

При выполнении бэкапа на шару, у учётной записи, под которой запущен SQL Server, должны быть права для записи туда.

В посте не раскрыто создание Linked Server’a (мышкой в GUI интуитивно настраивается за пару минут) и перенос логинов на новый сервер. Те, кто сталкивался с переносом пользователей знают, что простое пересоздание sql-логинов не очень помогает, поскольку у них есть sid’ы, с которыми и связаны пользователи БД. Скрипты для генерации sql-логинов с текущими паролями и корректными sid’ами есть на msdn.

Копирование и перенос на другой сервер бд

Для просмотра, запуска, останова служб MS SQL Server необходимо запустить утилиту SQL Server Configuration Manager (рис. ).

Рис. Список служб сервера БД

Для того чтобы скопировать БД необходимо остановить службу SQL Server (в ее контекстном меню выбрать Stop). Далее в подпапке …\MSSQL.1\MSSQL\Data\ скопировать файлы с вашим названием БД (по умолчанию их два). Не забудьте потом снова запустить службу SQL Server (в ее контекстном меню выбрать Start).

Для того чтобы подключить скопированную БД на другом сервере, нужно предварительно скопировать ваши файлы в папку …\MSSQL.1\MSSQL\Data\ соответствующего сервера. Далее запустить утилиту SQL Server Management Studio. В появившемся окне с названием Object Explorer (его можно вызвать по <F8>) выбираем DataBases (Базы данных) и по <правой кнопке мыши> в контекстном меню (рис. 2) выбираем Attach… (Присоединить…). В появившемся окне Attach DataBases нажать <Add> и выбрать ваш файл БД с расширением .mdf.

Перенос бд с одного сервера бд на другой через создание резервной копии Создание резервной копии бд

В контекстном меню вашей БД выбрать Tasks/ Back Up

В появившемся окне Back Up Database в разделе Destination кнопкой Remove удалить все пути к резервной копии.

В окне Back Up Database Кнопкой Add вызвать проводник, в котором указать путь и название файла, в который будет помещена резервная копия.

После выбора пути и файла для резервной копии в окне Back Up Database нажатием на OK запускаем процесс создания резервной копии. В случае успешной работы появится сообщение:

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

1. В разделе Databases в контекстном меню выбираем Restore Database…

2. В окне Restore Database в разделе To Database написать имя новой БД, в которую будет помещен результат, способ восстановления выбирается From Device, в следующем окне Specify Backup кнопкой Add вызываем проводник, в котором указываем путь и имя файла с резервной копией.

3. После возврата в окно Restore Database в списке Select the backup sets to restore отмечаем выбранную резервную копию.

Восстановление и перенос системных баз MSSQL

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

Восстановление

Первое что нам потребуется, это восстановить базу данных master. Для восстановления базы данных master необходимо сначала остановить службу SQL Server, а затем запустить ее в однопользовательском режиме. Для этого нужно открыть командную строку, перейти в нужный каталог с установленным сервером, например C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn и выполнить команду:

Далее необходимо выполнить запрос:

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

Перенос

Чтобы перенести системные базы данных в другое место, нужно проделать нехитрые действия.
Останавливаем службу SQL Server и запускаем программу SQL Server Configuration Manager. Слева в древовидном списке выбираем SQL Server Services, в появившемся справа списке выбираем SQL Server (MSSQLSERVER) и открываем Properties. В появившемся окне переходим на вкладку Advanced и ищем параметр Startup Parameters. Вот его то нам и необходимо подкорректировать. Меняем пути для базы данных master:

Переносим файлы master.mdf, mastlog.ldf и папку с логами в новое место и стартуем службу.
Затем выполняем запрос для переноса баз model, msdb и tempdb:

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