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

Копирование баз данных на другие серверы

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

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

Использование мастера копирования баз данных Мастер копирования баз данных можно использовать для копирования или перемещения баз данных между серверами или обновления базы данных SQL Server до более поздней версии. Дополнительные сведения см. в статье Use the Copy Database Wizard.
Восстановление базы данных из резервной копии Для копирования всей базы данных можно использовать инструкции BACKUP и RESTORE Transact-SQL. Выбор методики восстановления базы данных из полной резервной копии для копирования базы данных с одного компьютера на другой может быть мотивирован разными причинами. Сведения о копировании базы данных путем восстановления из резервной копии см. в статье Копирование баз данных путем создания и восстановления резервных копий.

Заметка Чтобы настроить зеркальную базу данных для зеркального отображения базы данных, необходимо восстановить базу данных на зеркальном сервере с помощью restore DATABASE WITH NORECOVERY. Дополнительные сведения см. в статье Подготовка зеркальной базы данных к зеркальному отображению (SQL Server).

Обратная связь

Были ли сведения на этой странице полезными?

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

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

Disclaimer

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

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

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

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

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

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

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

DECLARE @unc_backup_path AS varchar(max) = '\\newServer\backup_share\' , @local_backup_path AS varchar(max) = 'E:\Backup\' , @new_data_path as varchar(max) = 'D:\SQLServer\data\'; SELECT name , 'BACKUP DATABASE [' + name + '] TO DISK = ''' + @unc_backup_path + name + '.bak'' WITH INIT, COPY_ONLY, STATS = 5;' AS backup_command , 'ALTER DATABASE [' + name + '] SET OFFLINE WITH ROLLBACK IMMEDIATE;' AS offline_command , 'RESTORE DATABASE [' + name + '] FROM DISK = ''' + @local_backup_path + name + '.bak'' WITH ' + ( SELECT 'MOVE ''' + mf.name + ''' TO ''' + @new_data_path + REVERSE(LEFT(REVERSE(mf.physical_name), CHARINDEX('\', REVERSE(mf.physical_name))-1)) + ''', ' FROM sys.master_files mf WHERE mf.database_id = d.database_id FOR XML PATH('') ) + 'REPLACE, RECOVERY, STATS = 5;' AS restore_command FROM sys.databases d WHERE database_id > 4 AND state_desc = N'ONLINE';

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

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

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

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

Решение

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

DECLARE @unc_backup_path AS varchar(max) = 'D:\SQLServer\backup\' --путь к шаре для бэкапа на новом сервере , @local_backup_path AS varchar(max) = 'D:\SQLServer\backup\' --локальный путь на новом сервере к папке с бэкапами , @new_data_path as varchar(max) = 'D:\SQLServer\data\'; --локальный путь на новом сервере к папке, где должны оказаться данные SET NOCOUNT ON; IF OBJECT_ID ('tempdb..##CommandList', 'U') IS NULL CREATE TABLE ##CommandList ( dbName sysname unique --имя БД , backup_command varchar(max) --сгенерированная команда для бэкапа , offline_command varchar(max) --сгенерированная команда для перевода БД в офлайн после бэкапа , restore_command varchar(max) --сгенерированная команда для восстановления БД на новом сервере , processed bit --признак обработки: NULL - не обработано, 0 - обработано успешно, 1 - ошибка , start_dt datetime --когда начали обработку , finish_dt datetime --когда закончили обработку , error_msg varchar(max) --сообщение об ошибке, при наличии ); INSERT INTO ##CommandList (dbname, backup_command, offline_command, restore_command) SELECT name , 'BACKUP DATABASE [' + name + '] TO DISK = ''' + @unc_backup_path + name + '.bak'' WITH INIT, COPY_ONLY, STATS = 5;' AS backup_command --включает INIT - бэкап в месте назначения будет перезаписываться , 'ALTER DATABASE [' + name + '] SET OFFLINE WITH ROLLBACK IMMEDIATE;' AS offline_command , 'RESTORE DATABASE [' + name + '] FROM DISK = ''' + @local_backup_path + name + '.bak'' WITH ' + ( SELECT 'MOVE ''' + mf.name + ''' TO ''' + @new_data_path + REVERSE(LEFT(REVERSE(mf.physical_name), CHARINDEX('\', REVERSE(mf.physical_name))-1)) + ''', ' FROM sys.master_files mf WHERE mf.database_id = d.database_id FOR XML PATH('') ) + 'REPLACE, RECOVERY, STATS = 5;' AS restore_command FROM sys.databases d WHERE database_id > 4 AND state_desc = N'ONLINE' AND name NOT IN (SELECT dbname FROM ##CommandList) AND name <> 'Maintenance'; --у меня linked server - это тот же экземпляр, поэтому исключаю БД, которая используется на "linked server"

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

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

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

USE [Maintenance] GO CREATE TABLE CommandList ( dbName sysname unique --имя БД , restore_command varchar(max) --команда для восстановления , processed bit --статус выполнения , creation_dt datetime DEFAULT GETDATE() --время добавления записи , start_dt datetime --время начала обработки , finish_dt datetime --время окончания обработки , error_msg varchar(max) --текст ошибки, при наличии );

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

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

DECLARE @dbname AS sysname , @backup_cmd AS varchar(max) , @restore_cmd AS varchar(max) , @offline_cmd AS varchar(max); DECLARE MoveDatabase CURSOR FOR SELECT dbName, backup_command, offline_command, restore_command FROM ##CommandList WHERE processed IS NULL; OPEN MoveDatabase; FETCH NEXT FROM MoveDatabase INTO @dbname, @backup_cmd, @offline_cmd, @restore_cmd; WHILE @@FETCH_STATUS = 0 BEGIN --имя БД и команды получены, теперь нужно: -- сделать бэкап -- добавить в таблицу-приёмник на новом экземпляре команду для восстановления -- перевести БД в офлайн, чтобы к ней не могли подключиться -- получить следующую БД из списка --делаем отметку о начале работ UPDATE ##CommandList SET start_dt = GETDATE() WHERE dbName = @dbname; BEGIN TRY RAISERROR ('Делаем бэкап %s', 0, 1, @dbname) WITH NOWAIT; --сообщения на вкладке messages будут появляться сразу -- делаем бэкап EXEC (@backup_cmd); RAISERROR ('Добавляем команду на восстановления %s', 0, 1, @dbname) WITH NOWAIT; -- добавляем запись в таблицу-приёмник на linked server INSERT INTO [(LOCAL)].[Maintenance].[dbo].[CommandList] (dbName, restore_command) VALUES (@dbname, @restore_cmd); RAISERROR ('Переводим %s в OFFLINE', 0, 1, @dbname) WITH NOWAIT; -- переводим БД в офлайн EXEC (@offline_cmd); --Ставим успешный статус, проставляем время окончания работы UPDATE ##CommandList SET processed = 0 , finish_dt = GETDATE() WHERE dbName = @dbname; END TRY BEGIN CATCH RAISERROR ('ОШИБКА при работе с %s. Необходимо проверить error_msg в ##CommandList', 0, 1, @dbname) WITH NOWAIT; -- если что-то пошло не так, ставим ошибочный статус и описание ошибки UPDATE ##CommandList SET processed = 1 , finish_dt = GETDATE() , error_msg = ERROR_MESSAGE(); END CATCH FETCH NEXT FROM MoveDatabase INTO @dbname, @backup_cmd, @offline_cmd, @restore_cmd; END CLOSE MoveDatabase; DEALLOCATE MoveDatabase; --выводим результат SELECT dbName , CASE processed WHEN 1 THEN 'Ошибка' WHEN 0 THEN 'Успешно' ELSE 'Не обработано' END as Status , start_dt , finish_dt , error_msg FROM ##CommandList ORDER BY start_dt; DROP TABLE ##CommandList;

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

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

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

 SET NOCOUNT ON; DECLARE @dbname AS sysname , @restore_cmd AS varchar(max); WHILE 1 = 1 --можно придумать условие остановки, но мне было лень BEGIN SELECT TOP 1 @dbname = dbName, @restore_cmd = restore_command FROM CommandList WHERE processed IS NULL; --берём случайную БД из таблицы, среди необработанных IF @dbname IS NOT NULL BEGIN --добавляем сообщение о начале обработки UPDATE CommandList SET start_dt = GETDATE() WHERE dbName = @dbname; RAISERROR('Начали восстановление %s', 0, 1, @dbname) WITH NOWAIT; BEGIN TRY --пробуем восстановить БД, если что-то не так, в CATCH запишем что не так EXEC (@restore_cmd); --добавляем информацию в журнал UPDATE CommandList SET processed = 0 , finish_dt = GETDATE() WHERE dbName = @dbname; RAISERROR('База %s восстановлена успешно', 0, 1, @dbname) WITH NOWAIT; END TRY BEGIN CATCH RAISERROR('Возникла проблема с восстановлением %s', 0, 1, @dbname) WITH NOWAIT; UPDATE CommandList SET processed = 1 , finish_dt = GETDATE() , error_msg = ERROR_MESSAGE(); END CATCH END ELSE --если ничего не выбрали, то просто ждём BEGIN RAISERROR('waiting', 0, 1) WITH NOWAIT; WAITFOR DELAY '00:00:30'; END SET @dbname = NULL; SET @restore_cmd = NULL; END

Всё что он делает — смотрит в таблицу 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.

Microsoft SQL Server
Администрирование баз данных

Процесс переноса для сервера MS SQL Server

Процесс переноса серверов Microsoft SQL Server и Microsoft SQL Server Express одинаков.

Дополнительные сведения см. в следующей статье базы знаний Майкрософт: https://msdn.microsoft.com/ru-ru/library/ms189624.aspx.

Необходимые условия

• Нужно установить исходные и целевые экземпляры сервера SQL Server. Они могут быть размещены на разных компьютерах.

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

• Нужно установить SQL Server Management Studio . Если экземпляры сервера SQL Server находятся на разных компьютерах, то SQL Server Management Studio нужно установить на обоих.

Процесс переноса с помощью SQL Server Management Studio

1. Остановите службу сервера ESET PROTECT Server (или службу сервера ESMC Server) или службу ESET PROTECT MDM.

Не запускайте сервер ESET PROTECT или ESET PROTECT MDM, пока не будут завершены все описанные ниже действия.

2. Войдите в исходный экземпляр сервера SQL Server через SQL Server Management Studio.

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

4. Переведите исходную базу данных в автономный режим. Для этого последовательно щелкните элементы Задачи > Перевести в автономный режим .

db_migration_SQL_takeoffline

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

6. Войдите в целевой экземпляр сервера SQL Server через SQL Server Management Studio.

7. Восстановите базу данных в целевом экземпляре сервера SQL Server.

db_migration_SQL_restore

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

9. Выберите элемент «Из устройства» в разделе Указание источника и расположения наборов резервных копий, которые нужно восстановить , а затем нажмите кнопку с многоточием («…»).

db_restore

10. Нажмите кнопку Добавить, перейдите к файлу резервной копии и откройте его .

11. Выберите самую последнюю резервную копию, которую нужно восстановить (набор может содержать несколько резервных копий).

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

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

db_restore02

13. В окне мастера восстановления нажмите кнопку ОК .

14. Щелкните правой кнопкой мыши базу данных era_db , выберите пункт Создать запрос и выполните указанный ниже запрос, чтобы удалить содержимое таблицы tbl_authentication_certificate (иначе при подключении агентов к новому серверу может произойти ошибка):

delete from era_db.dbo.tbl_authentication_certificate where certificate_id = 1;

15. Убедитесь, что в новом сервере базы данных включена проверка подлинности SQL Server . Щелкните сервер правой кнопкой мыши и выберите пункт Свойства . Перейдите к элементу Безопасность и убедитесь, что выбран режим проверки подлинности SQL Server и Windows .

db_migration_auth_SQL

16. Создайте имя для входа в SQL Server (для ESET PROTECT Server или ESET PROTECT MDM) в целевом сервере SQL Server, на котором включена проверка подлинности SQL Server , и в восстановленной базе данных привяжите к пользователю имя для входа.

o Не задавайте срок окончания действия пароля.

o Рекомендуемые символы для имен пользователей:

▪ Малые буквы ASCII, числа и подчеркивание «_».

o Рекомендуемые символы для паролей:

▪ ТОЛЬКО символы ASCII, включая большие и малые буквы ASCII, числа, пробелы и специальные символы.

o Не используйте символы, не относящиеся к стандарту ASCII, фигурные скобки (<>) и символ @.

o Обратите внимание, что если не следовать приведенным выше рекомендациям по использованию символов, у вас могут возникнуть проблемы с подключением к базе данных или в последующих шагах вам придется использовать специальные escape-символы во время изменения строк подключения к базе данных. Этот документ не содержит правила использования escape-символов.

db_new_login

17. В целевой базе данных привяжите имя для входа к пользователю. На вкладке сопоставления пользователей назначьте пользователю роль в базе данных: db_datareader , db_datawriter или db_owner .

db_SQL_login_map

18. Чтобы включить последние компоненты сервера базы данных, укажите для восстановленной базы данных самый новый уровень совместимости . Щелкните новую базу данных правой кнопкой мыши и выберите пункт Свойства .

db_properties

Решение SQL Server Management Studio не может задавать уровни совместимости, которые старше используемой версии. Например, решение SQL Server Management Studio 2014 не может задать уровень совместимости для SQL Server 2019.

19. Убедитесь, что протокол подключения TCP/IP включен для «db_instance_name» (например, SQLEXPRESS или MSSQLSERVER), а TCP/IP- порту назначен номер 1433 . Для этого откройте диспетчер конфигурации SQL Server и перейдите к разделу Конфигурация сети SQL Server > Протоколы для db_instance_name , щелкните правой кнопкой мыши TCP/IP и выберите Включено . Дважды щелкните TCP/IP , откройте вкладку Протоколы , прокрутите вниз до элемента IPAll и в поле Порт TCP введите 1433. Щелкните OK и перезапустите службу SQL Server .

Перенос файлов баз данных (.mdf и.ldf) на другой диск¶

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

Имеет маленький размер
Сильно нагружен ОС и системными запросами
Довольно медленный
Помирает

Все эти факторы влияют как на отказоустойчивость, так и на скорость обработки запросов SQl-сервером, а следовательно и на работоспособность комплекса в целом!
Теперь, когда вы прониклись важностью момента, можно приступить к практическим действиям. Итак:

Перенос пользовательской базы данных¶

1. Договариваемся с творческой частью коллектива, что в определенное время все перестают работать с базой. А именно, прекращают что-то туда добавлять и/или изменять.

2. Останавливаем сервисы, которые работают с МБД в автоматическом режиме, например:

DB Import — импорт новостных лент
DDB — распределенная база данных
Sch_to_DB — репликация расписаний
иначе, есть вероятность потерять часть информации.

3. Запускаем Microsoft SQL Server Management Studio.

4. Самым первым делом всегда делаем бэкап базы!

5. Далее, смотрим, где лежат файлы нужной нам базы данных (в нашем примере это будет МБД под названием «RADIO-DB»). Для этого, нажимаем на ней ПКМ и открываем Properties (Свойства). Заходим в раздел Files (Файлы) и смотрим раздел Path (Путь):

6. Далее, нажимаем ПКМ на целевой базе и выбираем пункт Tasks\Detach (Задачи\Отсоединить):

7. В открывшемся окне ставим обе галочки и нажимаем ОК. После чего, МБД пропадет из списка:

8. Через обычный проводник заходим в каталог, где лежат нужные нам файлы. В нашем примере, это C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012\MSSQL\DATA.

9. Копируем эти файлы в новый каталог на новый диск и снова открываем Microsoft SQL Server Management Studio.

10. Нажимаем ПКМ на разделе Databases (Базы данных), выбираем пункт Attach (Присоединить) и в открывшемся окне нажимаем кнопку Add (Добавить) и выбираем нужный нам файл RADIO-DB.mdf уже из нового каталога:

Убеждаемся, что пути у нас теперь новые и нажимаем ОК.

Всё, пользовательская база данных переехала на новый диск. Не нужно ничего перезапускать и т.д. Убеждаемся, что рабочие места переподключились к МБД и разрешаем им снова работать в штатном режиме.

Перенос системных баз данных¶

Но, остались еще системные базы данных (спрятаны в разделе System Databases). Это msdb, model и tempdb, которые в общем-то тоже будет неплохо перенести на быстрый и отказоустойчивый диск. Тем более, что среди них есть одна, очень для нас важная база — tempdb. Именно через нее проходят все запросы, прежде чем попасть в пользовательскую МБД. Перенести системные базы ничуть не сложнее, чем пользовательские. И для этого надо:

1. Используя Microsoft SQL Server Management Studio, выполнить следующий скрипт:

-- #################################################################### -- Script for changing paths to physical files (mdf & ldf) -- of the system databases (exept master). -- Just change "D:\mdb\sys_db" to your preffered path in all strings. -- Don`t forget to restart SQL Server proccess after execution. -- #################################################################### USE master; GO ALTER DATABASE msdb MODIFY FILE (name = 'MSDBDATA', filename = 'D:\mdb\sys_db\MSDBDATA.mdf') ALTER DATABASE msdb MODIFY FILE (name = 'MSDBLOG', filename = 'D:\mdb\sys_db\MSDBLOG.ldf') ALTER DATABASE model MODIFY FILE (name = 'modeldev', filename = 'D:\mdb\sys_db\model.mdf') ALTER DATABASE model MODIFY FILE (name = 'modellog', filename = 'D:\mdb\sys_db\modellog.ldf') ALTER DATABASE tempdb MODIFY FILE (name = 'tempdev', filename = 'D:\mdb\sys_db\tempdb.mdf') ALTER DATABASE tempdb MODIFY FILE (name = 'templog', filename = 'D:\mdb\sys_db\templog.ldf')

Его также можно скачать из этого описания и запустить непосредственно на SQl-сервере.

2. Останавливаем службу SQL.

3. Копируем из старого каталога (помним наш пример: C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012\MSSQL\DATA) все файлы, указанные в скрипте выше, в новый каталог, который мы прописали в том же скрипте.

4. Обязательно добавляем учетную запись группы безопасности. Подробно о том, как это сделать, читайте в конце данной статьи, в разделе «Предоставление разрешения на доступ к файловой системе идентификатору безопасности службы».

5. Запускаем службу SQL.

6. Убедиться, что мы все сделали правильно, можно, посмотрев в свойствах каждой системной БД раздел Files (Файлы). Там должны быть новые пути к обоим файлам (самой БД и логу).

Перенос самой системной базы данных master¶

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

1. Для изменения пути к БД master, нам понадобится оснастка SQL Server Configuration Manager (Диспетчер конфигурации SQL Server). Запускаем ее и открываем свойства SQL Server:

2. В свойствах SQL Server`а открываем вкладку Startup Parameters (Параметры запуска):

и по очереди меняем все указанные пути на новые.
— каждая строка начинается со своего символа -d, -e или -l. Ни в коем случае не меняйте их и не удаляйте!

3. Каждое изменение пути подтверждаем нажатием кнопки Update.

4. Теперь останавливаем сервис, копируем файлы master.mdf и mastlog.ldf из старого каталога в новый. После чего запускам сервис. ERRORLOG можно не копировать. Он создастся заново.

Предоставление разрешения на доступ к файловой системе идентификатору безопасности службы¶

С помощью проводника Windows перейдите в папку файловой системы, в которой находятся файлы базы данных. Правой кнопкой мыши щелкните эту папку и выберите пункт Свойства.
На вкладке Безопасность щелкните Изменитьи затем ― Добавить.
В диалоговом окне Выбор пользователей, компьютеров, учетных записей служб или групп щелкните Расположения, в начале списка расположений выберите имя своего компьютера и нажмите кнопку ОК.
В поле Введите имена объектов для выбора введите имя идентификатора безопасности службы. В качестве идентификатора безопасности службы компонента Компонент Database Engine используйте NT SERVICE\MSSQLSERVER для экземпляра по умолчанию или NT SERVICE\MSSQL$InstanceName — для именованного экземпляра.
Щелкните Проверить имена , чтобы проверить введенные данные. Проверка зачастую выявляет ошибки, по ее окончании может появиться сообщение о том, что имя не найдено. При нажатии кнопки ОК открывается диалоговое окно Обнаружено несколько имен .Теперь выберите идентификатор безопасности службы MSSQLSERVER или NT SERVICE\MSSQL$InstanceName и нажмите кнопку ОК. Снова нажмите кнопку ОК , чтобы вернуться в диалоговое окно Разрешения.
В поле имен Группа или пользователь выберите имя идентификатора безопасности службы, а затем в поле Разрешения дляустановите флажок Разрешить для параметра Полный доступ.
Нажмите кнопку Применить, а затем дважды кнопку ОК , чтобы выполнить выход.

Вот теперь, точно всё. Спасибо за внимание!

P.S. В зависимости от конкретной ОС, конкретной версии SQL сервера, вашей кармы и наличия солнечных вспышек, что-то может пойти не так. Прежде чем приступать к вышеописанным действиям, убедитесь, что:
а) оно вам действительно надо
б) вы морально готовы
ц) вы понимаете, что вы делаете
д) у вас вся ночь впереди, чтобы переустановить SQL заново и развернуть бэкап.

detach_db2.PNG	Просмотреть detach_db2.PNG	31,7 КБ	Stanislav Serednitskiy, 22/03/2018 17:27
detach_db.PNG	Просмотреть detach_db.PNG	62,9 КБ	Stanislav Serednitskiy, 22/03/2018 17:28
detach_db3.PNG	Просмотреть detach_db3.PNG	87,3 КБ	Stanislav Serednitskiy, 22/03/2018 17:56
attach_db.PNG	Просмотреть attach_db.PNG	84,4 КБ	Stanislav Serednitskiy, 22/03/2018 18:05
System_DB_files_moving.sql	System_DB_files_moving.sql	993 байта	Stanislav Serednitskiy, 22/03/2018 18:49
sql_conf_man.PNG	Просмотреть sql_conf_man.PNG	31,7 КБ	Stanislav Serednitskiy, 22/03/2018 19:14
start_param.PNG	Просмотреть start_param.PNG	15,3 КБ	Stanislav Serednitskiy, 22/03/2018 19:18