Backups do SQL Server - transferindo entre instâncias

Question

Eu trabalho em uma loja de desenvolvimento em que usamos principalmente o SQL Server para o desenvolvimento e testes diários.

Às vezes, ocorrem problemas na máquina de alguém, geralmente um labtop, que gostaria de reproduzir na minha máquina. Portanto, quero transferir seus bancos de dados para minha máquina. Essa transferência geralmente é off-line onde o labtop está em outro lugar, portanto, nós despejamos bancos de dados, os instalamos e os restauramos, de modo que os mecanismos de transferência on-line geralmente não estão disponíveis. Geralmente há 5 + arquivos de despejo para restaurar.

O processo atual é usar backup database + restore database e passar os arquivos de despejo ao redor. Isso funciona bem, mas é uma dor real por causa dos nomes físicos armazenados nos backups. Ao fazer a restauração, tenho que fazer with move para mapear os arquivos lógicos no arquivo de backup para a localização física dos arquivos mdf / ldf em minha máquina.

Dado que o SQL Server conhece o local padrão para criar esses arquivos, como create database funciona assim, existe alguma maneira de eu conseguir uma maneira mais inteligente de mover esses dbs de uma instância para outra? Eu ficaria muito feliz se os nomes de db fossem exatamente os mesmos (se isso for descartável dos arquivos de dump) e a localização física fosse inferida do padrão de instâncias de destino.

OBSERVAÇÃO : Como um aparte, a opção de restauração do Management Studio (GUI) parece ser inteligente o bastante para gerenciar isso sem precisar detalhar os locais físicos manualmente.

    

Answer 1

As pastas padrão para uma instância do SQL são armazenadas no registro do servidor

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

Você pode usar xp_regread para extrair esse valor e usá-lo em seus scripts de restauração. Aqui está um exemplo:

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';

Você substituiria MSSQL10_50.MSSQLSERVER pelo ID da instância do servidor na qual você está executando a restauração.

Algo mais fácil é usar xp_instance_regread , que identifica automaticamente o ID da instância em que você está trabalhando. Então, o acima seria reescrito como tal:

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';

(Nota: estou trabalhando com o SQL Server 2008 R2 aqui)

Em relação à movimentação dos bancos de dados, você pode desanexar o banco de dados da origem, copiar os dados físicos e os arquivos de log para o local de destino e reconectá-los (bem como de volta na máquina de origem). Eu prefiro o processo de backup / restauração pessoalmente, especialmente se você estiver em uma posição onde você precisa ftp para fazer a transferência de arquivos.

Você pode até configurar a replicação, o espelhamento ou o envio de logs, mas acho que, no seu caso, isso seria um exagero. Eu ficaria com o método de backup / restauração.