Резервные копии SQL Server - передача между экземплярами

Я работаю в магазине разработки, где мы в основном используем SQL Server для повседневной разработки и тестирования.

Иногда на чьем-то компьютере, часто на лабораторном столе, возникают проблемы, которые я хотел бы воспроизвести на своем. Поэтому я хочу перенести их базы данных на свою машину. Эта передача часто происходит в автономном режиме, когда рабочая поверхность находится в другом месте, поэтому мы выгружаем базы данных, загружаем их по FTP и восстанавливаем их, поэтому механизмы онлайн-передачи обычно недоступны. Обычно требуется восстановить 5+ файлов дампа.

Текущий процесс заключается в использовании backup database + restore database и передача файлов дампа. Это работает нормально, но это настоящая боль из-за физических имен, хранящихся в резервных копиях. При восстановлении я должен сделать with move чтобы сопоставить логические файлы в файле резервной копии с физическим расположением файлов mdf / ldf на моем компьютере.

Учитывая, что SQL Server знает местоположение по умолчанию для создания этих файлов, как create database работает так, есть ли способ придумать более разумный способ перемещения этих dbs из одного экземпляра в другой? Я был бы вполне счастлив, если бы имена db были точно такими же (если это можно удалить из файлов дампа), а физическое местоположение было бы выведено из целевых экземпляров по умолчанию.

НОТА: Кроме того, вариант восстановления Management Studio (GUI) кажется достаточно умен, чтобы справиться с этим без необходимости вручную детализировать физическое расположение.

Папки по умолчанию для экземпляра SQL хранятся в реестре сервера в

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\[your specific instance ID]\MSSQLServer

Ты можешь использовать xp_regread чтобы получить это значение, а затем использовать его в своих сценариях восстановления. Вот пример:

DECLARE @DataDirectory VARCHAR(255)
DECLARE @LogDirectory VARCHAR(255)

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer',
  @value_name='DefaultData',
  @DataDirectory=@DataDirectory OUTPUT

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer',
  @value_name='DefaultLog',
  @LogDirectory=@LogDirectory OUTPUT

RESTORE DATABASE MyDB
FROM DISK = 'X:\SQLBackups\MyDB.bak'
WITH MOVE 'MyDB_Data' TO @DataDirectory + 'MyDB.mdf',
MOVE 'MyDB_Log' TO @LogDirectory + 'MyDB.ldf';

Вы должны заменить MSSQL10_50.MSSQLSERVER идентификатором экземпляра экземпляра сервера, на котором вы выполняете восстановление.

Что-то проще использовать xp_instance_regread который автоматически определяет идентификатор экземпляра сервера, над которым вы работаете. Таким образом, приведенное выше было бы переписано так:

DECLARE @DataDirectory nvarchar(255)
DECLARE @LogDirectory nvarchar(255) 

EXEC master..xp_instance_regread
    @rootkey=N'HKEY_LOCAL_MACHINE',
    @key=N'Software\Microsoft\Microsoft SQL Server\MSSQLServer',
    @value=N'DefaultData', 
    @DataDirectory=@DataDirectory OUTPUT

EXEC master..xp_instance_regread
    @rootkey=N'HKEY_LOCAL_MACHINE',
    @key=N'Software\Microsoft\Microsoft SQL Server\MSSQLServer',
    @value=N'DefaultLog', 
    @LogDirectory=@LogDirectory OUTPUT

RESTORE DATABASE MyDB
FROM DISK = 'X:\SQLBackups\MyDB.bak'
WITH MOVE 'MyDB_Data' TO @DataDirectory + 'MyDB.mdf',
MOVE 'MyDB_Log' TO @LogDirectory + 'MyDB.ldf';

(Примечание: здесь я работаю с SQL Server 2008 R2)

Что касается перемещения баз данных, вы можете отсоединить базу данных от источника, затем скопировать физические данные и файлы журналов в целевое расположение, а затем снова прикрепить их туда (а также обратно на исходный компьютер). Я лично предпочитаю процесс резервного копирования / восстановления, особенно если вы находитесь в положении, когда вам нужен ftp для передачи файлов.

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