Mover bancos de dados do sistema

  • Artigo

Aplica-se a:SQL Server

esse artigo descreve como mover bancos de dados do sistema no SQL Server. Mover bancos de dados do sistema pode ser útil nas seguintes situações:

  • Recuperação de falha. Por exemplo, o banco de dados está em modo de suspeição ou foi desligado devido a uma falha de hardware.

  • Realocação planejada.

  • Realocação para manutenção de disco programada.

Os procedimentos a seguir se aplicam para mover arquivos de banco de dados dentro da mesma instância do SQL Server. Para mover um banco de dados para outra instância do SQL Server ou para outro servidor, use a operação de backup e restauração.

Os procedimentos deste artigo exigem o nome lógico dos arquivos de banco de dados. Para obter o nome, consulte a coluna de nome na exibição de catálogo sys.master_files .

Importante

Se você mover um banco de dados do sistema e, posteriormente, recriar o banco de dados master, será necessário mover o banco de dados do sistema novamente porque a operação de recriação instala todos os bancos de dados do sistema em seus locais padrão.

Mover bancos de dados do sistema

Para mover um arquivo de dados de um banco de dados do sistema ou arquivo de log como parte de uma realocação planejada ou operação de manutenção, execute as etapas a seguir. Isso inclui os bancos de dados do sistema model, msdb e tempdb.

Importante

Este procedimento se aplica a todos os bancos de dados do sistema exceto os bancos de dados master e Resource. Consulte mais adiante neste artigo para ver as etapas para mover o banco de dados master. O banco de dados Resource não pode ser movido.

  1. Grave o local existente dos arquivos de banco de dados que você pretende mover, revisando a exibição de catálogo sys.master_files.

  2. Verifique se a conta de serviço do mecanismo de banco de dados do SQL Server tem permissões completas para o novo local dos arquivos. Para obter mais informações, consulte Configurar contas de serviço e permissões do Windows. Se a conta de serviço do mecanismo de banco de dados não puder controlar os arquivos no novo local, a Instância do SQL Server não iniciará.

  3. Para cada arquivo de banco de dados a ser movido, execute a instrução a seguir.

    ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' );

    Até que o serviço seja reiniciado, o banco de dados continua usando os arquivos de dados e de log no local existente.

  4. Pare a instância do SQL Server para realizar a manutenção. Para obter mais informações, consulte Iniciar, parar, pausar, retomar e reiniciar os serviços SQL Server.

  5. Copie o arquivo ou os arquivos de banco de dados para o novo local. Observe que essa não é uma etapa necessária para o banco de dados do sistema tempdb, esses arquivos serão criados no novo local automaticamente.

  6. Reinicie a instância do SQL Server ou o servidor. Para obter mais informações, consulte Iniciar, parar, pausar, retomar e reiniciar os serviços SQL Server.

  7. Execute a consulta a seguir para verificar se houve alteração no arquivo. Os bancos de dados do sistema devem relatar os novos locais de arquivo físico.

    SELECT name, physical_name AS CurrentLocation, state_desc  
FROM sys.master_files  
WHERE database_id = DB_ID(N'<database_name>');

  8. Como, na Etapa 5, você copiou os arquivos de banco de dados em vez de movê-los, agora, você pode excluir com segurança os arquivos de banco de dados não utilizados do local anterior.

Acompanhamento: depois de mover todos os bancos de dados do sistema msdb

Se o banco de dados msdb for movido e Database Mail estiver configurado, conclua as etapas adicionais a seguir.

  1. Verifique se o Service Broker está habilitado para o banco de dados msdb executando a consulta a seguir.

    SELECT is_broker_enabled   
FROM sys.databases  
WHERE name = N'msdb';

    Se o Service Broker não estiver habilitado para msdb, ele deverá ser reabilitado para que o Database Mail funcione. Para obter mais informações, confira ALTER DATABASE ... SET ENABLE_BROKER.

    ALTER DATABASE msdb 
SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

    Confirme se o valor de is_broker_enabled agora é 1.

  2. Verifique se o Database Mail está funcionando, enviando um email de teste.

Falha no procedimento de recuperação

Se um arquivo tiver de ser movido devido à falha de um hardware, siga estas etapas para realocar o arquivo para o novo local. Este procedimento se aplica a todos os bancos de dados do sistema exceto os bancos de dados master e Resource. Os exemplos a seguir usam o prompt de linha de comando do Windows e o Utilitário sqlcmd.

Importante

Se o banco de dados não puder ser inicializado, se ele estiver no modo de suspeição ou em estado não recuperado, somente os membros da função fixa sysadmin poderão mover o arquivo.

  1. Verifique se a conta de serviço do mecanismo de banco de dados do SQL Server tem permissões completas para o novo local dos arquivos. Para obter mais informações, consulte Configurar contas de serviço e permissões do Windows. Se a conta de serviço do mecanismo de banco de dados não puder controlar os arquivos no novo local, a instância do SQL Server não iniciará.

  2. Interrompa a instância do SQL Server, se tiver sido iniciada.

  3. Inicie a instância do SQL Server no modo somente recuperação mestre, inserindo um dos seguintes comandos no prompt de comando. O uso do parâmetro de inicialização 3608 impede que o SQL Server inicie e recupere automaticamente qualquer banco de dados, exceto o banco de dados master. Para obter mais informações, confira Parâmetros de inicialização e TF3608.

    Os parâmetros especificados nestes comandos diferenciam maiúsculas e minúsculas. Os comandos falham quando os parâmetros não são especificados como demonstrado.

    Para a instância padrão (MSSQLSERVER), execute o seguinte comando:

    NET START MSSQLSERVER /f /T3608

    Para uma instância nomeada, execute o seguinte comando:

    NET START MSSQL$instancename /f /T3608

    Para obter mais informações, consulte Iniciar, parar, pausar, retomar e reiniciar os serviços SQL Server.

  4. Imediatamente após a inicialização do serviço com o sinalizador de rastreamento 3608 e /f, inicie uma conexão sqlcmd com o servidor para reivindicar a única conexão disponível. Por exemplo, ao executar o sqlcmd localmente no mesmo servidor que a instância padrão (MSSQLSERVER) e para se conectar à autenticação de integração do Active Directory, execute o seguinte comando:

    sqlcmd

    Para se conectar a uma instância nomeada no servidor local, com a autenticação de integração do Active Directory:

    sqlcmd -S localhost\instancename

    Para obter mais informações sobre a sintaxe sqlcmd, confira utilitário sqlcmd.

    Para cada arquivo a ser movido, use comandos sqlcmd ou o SQL Server Management Studio para executar a instrução a seguir. Para obter mais informações sobre como usar o utilitário sqlcmd , veja Usar o Utilitário sqlcmd. Depois que a sessão sqlcmd for aberta, execute a seguinte instrução uma vez para cada arquivo a ser movido:

    ALTER DATABASE database_name MODIFY FILE( NAME = logical_name , FILENAME = 'new_path\os_file_name' )  
GO

  5. Use o utilitário sqlcmd ou o SQL Server Management Studio para fazer isso.

  6. Pare a instância do SQL Server. Por exemplo, execute NET STOP MSSQLSERVER no prompt da linha de comando.

  7. Copie o arquivo ou os arquivos para um novo local.

  8. Reinicie a instância do SQL Server. Por exemplo, execute NET START MSSQLSERVER no prompt da linha de comando.

  9. Execute a consulta a seguir para verificar se houve alteração no arquivo.

    SELECT name, physical_name AS CurrentLocation, state_desc  
FROM sys.master_files  
WHERE database_id = DB_ID(N'<database_name>');

  10. Como, na Etapa 7, você copiou os arquivos de banco de dados em vez de movê-los, agora, você pode excluir com segurança os arquivos de banco de dados não utilizados do local anterior.

Movendo o banco de dados mestre

Para mover o banco de dados master, siga estas etapas.

  1. Verifique se a conta de serviço do mecanismo de banco de dados do SQL Server tem permissões completas para o novo local dos arquivos. Para obter mais informações, consulte Configurar contas de serviço e permissões do Windows. Se a conta de serviço do mecanismo de banco de dados não puder controlar os arquivos no novo local, a instância do SQL Server não iniciará.

  2. No menu Iniciar, localize e inicie o SQL Server Configuration Manager. Para obter mais informações sobre o local esperado, confira SQL Server Configuration Manager.

  3. No nó dos Serviços SQL Server, clique com o botão direito do mouse na instância do SQL Server (por exemplo, SQL Server (MSSQLSERVER)) e escolha Propriedades.

  4. Na caixa de diálogo Propriedades do SQL Server (instance_name), clique na guia Parâmetros de Inicialização.

  5. Na caixa Parâmetros existentes, selecione o parâmetro -d. Na caixa Especificar um parâmetro de inicialização, altere o parâmetro para o novo caminho do arquivo de masterdados. Selecione Atualizar para salvar a alteração.

  6. Na caixa Parâmetros existentes, selecione o parâmetro -l. Na caixa Especificar um parâmetro de inicialização, altere o parâmetro para o novo caminho do arquivo de masterlog. Selecione Atualizar para salvar a alteração.

    O valor do parâmetro para o arquivo de dados deve seguir o parâmetro -d e o valor para o arquivo de log deve seguir o parâmetro -l . O seguinte exemplo mostra os valores do parâmetro para o local padrão dos arquivos de dados master.

    -dC:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA\master.mdf

    -lC:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

    Se a realocação planejada para o arquivo de dados master for E:\SQLData, os valores dos parâmetros serão alterados da seguinte maneira:

    -dE:\SQLData\master.mdf

    -lE:\SQLData\mastlog.ldf

  7. Selecione OK para salvar as alterações permanentemente e fechar a caixa de diálogo Propriedades do SQL Server (nome_da_instância).

  8. Interrompa a instância do SQL Server clicando com o botão direito do mouse no nome da instância e escolhendo Interromper.

  9. Copie os arquivos master.mdf e mastlog.ldf para um novo local.

  10. Reinicie a instância do SQL Server.

  11. Verifique a alteração do arquivo para o banco de dados master executando a consulta a seguir.

    SELECT name, physical_name AS CurrentLocation, state_desc  
FROM sys.master_files
WHERE database_id = DB_ID('master');

  12. Neste ponto, o SQL Server deverá ser executado normalmente. Porém, a Microsoft também recomenda ajustar a entrada do Registro em HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance_ID\Setup, em que instance_ID é como MSSQL13.MSSQLSERVER. Nessa colmeia, altere o valor SQLDataRoot para o novo caminho do novo local dos arquivos de banco de dados master. Uma falha em atualizar o Registro poderá causar falha na atualização e aplicação de patch.

  13. Como, na Etapa 9, você copiou os arquivos de banco de dados em vez de movê-los, agora, você pode excluir com segurança os arquivos de banco de dados não utilizados do local anterior.

Movendo o banco de dados Recursos

O local do banco de dados Resource é \<*drive*>:\Program Files\Microsoft SQL Server\MSSQL\<version>.\<*instance_name*>\MSSQL\Binn\\. O banco de dados não pode ser movido.

Acompanhamento: depois de mover todos os bancos de dados do sistema

Se você moveu todos os bancos de dados do sistema para uma nova unidade ou volume ou para outro servidor com uma letra de unidade diferente, faça as atualizações a seguir.

  • Altere o caminho do log do SQL Server Agent. Se você não atualizar este caminho, o SQL Server Agent não iniciará.

  • Altere o local padrão do banco de dados. Criar um novo banco de dados pode falhar se a letra da unidade e do caminho especificados como a localização padrão não existir.

Altere o caminho do log do SQL Server Agent

Se você moveu todos os bancos de dados do sistema para um novo volume ou migrou para outro servidor com uma letra da unidade diferente e o caminho do arquivo de log de erros SQLAGENT.OUT do SQL Agent não existir mais, faça as atualizações a seguir.

  1. No SQL Server Management Studio, em Pesquisador de Objetos, expanda SQL Server Agent.

  2. Clique com o botão direito do mouse em Logs de Erros e selecione Configurar.

  3. Na caixa de diálogo Configurar Logs de Erros do SQL Server Agent , especifique o novo local do arquivo SQLAGENT.OUT. A localização padrão é C:\Program Files\Microsoft SQL Server\MSSQL\<version>.<instance_name>\MSSQL\Log\\.

Altere o local padrão do banco de dados

  1. Do SQL Server Management Studio, em Pesquisador de Objetos, conecte-se à instância do SQL Server desejada. Clique com o botão direito do mouse na instância e selecione Propriedades.

  2. Na caixa de diálogo Propriedades do Servidor da caixa de diálogo, selecione Configurações de Banco de Dados.

  3. Em Locais padrão de banco de dados, navegue até o novo local para os arquivos de dados e log.

  4. Pare e inicie o serviço do SQL Server para concluir a alteração.

Exemplos

R. Movendo o banco de dados tempdb

O seguinte exemplo move os arquivos de log e de dados tempdb para um novo local como parte de uma realocação planejada.

Dica

Aproveite essa oportunidade para revisar seus arquivos tempdb quanto ao tamanho e ao posicionamento ideais. Para obter mais informações, confira Otimizar o desempenho do tempdb no SQL Server.

Como o tempdb é recriado a cada vez que a instância do SQL Server é iniciada, você não precisa mover fisicamente os arquivos de log e de dados. Os arquivos são criados no local novo quando o serviço é reiniciado na etapa 4. Até que o serviço seja reiniciado, o tempdb continua usando os arquivos de dados e de log no local existente.

  1. Determine os nomes de arquivo lógicos do banco de dados tempdb e o seu local atual no disco.

    SELECT name, physical_name AS CurrentLocation  
FROM sys.master_files  
WHERE database_id = DB_ID(N'tempdb');  
GO

  2. Verifique se a conta de serviço do mecanismo de banco de dados do SQL Server tem permissões completas para o novo local dos arquivos. Para obter mais informações, consulte Configurar contas de serviço e permissões do Windows. Se a conta de serviço do mecanismo de banco de dados não puder controlar os arquivos no novo local, a instância do SQL Server não iniciará.

  3. Altere o local de cada arquivo usando ALTER DATABASE.

    USE master;  
GO  
ALTER DATABASE tempdb   
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');  
GO  
ALTER DATABASE tempdb   
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');  
GO

    Até que o serviço seja reiniciado, o tempdb continua usando os arquivos de dados e de log no local existente.

  4. Pare e reinicie a instância do SQL Server.

  5. Verifique a alteração do arquivo.

    SELECT name, physical_name AS CurrentLocation, state_desc  
FROM sys.master_files  
WHERE database_id = DB_ID(N'tempdb');

  6. Exclua os arquivos tempdb não usados do local original.

Confira também

Próximas etapas