Você está offline; aguardando reconexão

Como usar as funções Desanexar e Anexar para mover bancos de dados do SQL Server

Sumário
Este artigo descreve como alterar a localização dos arquivos de dados e dos arquivos de log para qualquer banco de dados do Microsoft SQL Server 2005, SQL Server 2000 ou SQL Server 7.0.

Para obter mais informações sobre como mover os bancos de dados do sistema no SQL Server 2008, consulte o tópico "Movendo banco de dados do sistema" no Manuais Online do SQL Server. Para exibir esse tópico, vá para o site Mover bancos de dados do sistema do Microsoft Developer Network (MSDN).
Mais Informações
As etapas a serem seguidas para alterar o local de alguns bancos de dados do sistema SQL Server diferem das etapas a serem seguidas para alterar o local dos bancos de dados de usuário. Estes casos especiais são descritos separadamente.

Observação Os bancos de dados do sistema do SQL Server 7.0 são incompatíveis com o SQL Server 2000. Não anexe os bancos de dados do SQL Server 7.0 mestre, modelo, msdb ou distribuição ao SQL Server 2000. Se você estiver usando o SQL Server 2005, você pode apenas anexar os bancos de dados do SQL Server 2005 a uma instância. Todos os exemplos neste artigo presumem que o SQL Server está instalado na pasta D:\Mssql7. Além disso, os exemplos presumem que todos os arquivos de dados e de log estão localizados na pasta D:\Mssql7\Data padrão. Os exemplos movem os arquivos de dados e os arquivos de log para todos os bancos de dados para a pasta E:\Sqldata folder.

Os locais de dados padrão para o SQL Server 2005 e SQL Server 2000 são os seguintes:

Pré-requisitos

  • Fazer backup de todos os bancos de dados, especialmente o banco de dados mestre, de seu local atual.
  • Certifique-se de que você tenha permissões de administrador do sistema (as).
  • Certifique-se de que você saiba o nome do local atual de todos os arquivos de dados e arquivos de logo para o banco de dados.

    Observação Você pode determinar o nome o local atual de todos os arquivos que um banco de dados usa ao usar o procedimento armazenado sp_helpfile:
    use <database_name>gosp_helpfilego
  • É necessário que você tenha acesso exclusivo ao banco de dados que está sendo movido. Se você tiver problemas durante o processo e não conseguir acessar um banco de dados tenha movido ou se não conseguir iniciar o SQL Server, verifique o registro de erros do SQL Server e o Manuais Online do SQL Server para obter detalhes sobre os erros que você está encontrando.

Movendo os bancos de dados do usuário

O exemplo a seguir move um banco de dados chamado mydb. Este banco de dados contém um arquivo de dados, Mydb.mdf, e um arquivo de log, Mydblog.ldf. Se o banco de dados que está sendo movido tiver mais arquivos de dados ou de log, especifique os arquivos na lista delimitada por vírgulas no procedimento armazenado sp_attach_db. O procedimento sp_detach_db não é alterado independentemente de quantos arquivos o banco de dados contém, porque o procedimento sp_detach_db não lista os arquivos.
  1. Inicie o SQL Server 2005 Management Studio. Para fazer isto, clique em Iniciar, em Todos os Programas, em Microsoft SQL Server 2005 e em SQL Server Management Studio.
  2. Clique em Nova consulta e desanexe o banco de dados da seguinte maneira:
    use master   go   sp_detach_db 'mydb'   go
  3. Copie os arquivos de dados e os arquivos de log do local atual (D:\Mssql7\Data) para o novo local (E:\Sqldata).
  4. Anexe novamente o banco de dados. Aponte para os arquivos no novo local da seguinte forma:
    use master  go  sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'  go
    Verifique a alteração no local dos arquivos usando o procedimento armazenado sp_helpfile:
    use mydb   go   sp_helpfile   go
    Os valores da coluna do nome do arquivo devem refletir os novos locais.
Observação O artigo 922804 da Base de Dados de Conhecimento Microsoft descreve um problema para os bancos de dados do SQL Server 2005 em um armazenamento anexado por rede.  Para obter mais informações, clique no número abaixo para ler o artigo na Base de Dados de Conhecimento Microsoft (a página pode estar em inglês):
922804 CORREÇÃO: Após desanexar um banco de dados Microsoft SQL Server 2005 no armazenamento anexado por rede, não será possível anexar novamente o banco de dados SQL Server.
Considere este problema. Além disso, considere as permissões aplicadas a um banco de dados desanexado no SQL Server 2005. Para obter mais informações, consulte a seção "Desanexando e anexando um banco de dados" do tópico "Arquivos de dados e de log seguros" nos Manuais Online do SQL Server. Para exibir esse tópico, vá para o site do MSDN Segurança de dados e arquivos de log.

Movendo exemplos de bancos de dados

Para mover exemplos de banco de dados pubs e o exemplo de banco de dados Northwind no SQL Server 2000 ou SQL Server 7.0 ou mover o exemplo de banco de dados AdventureWorks e o exemplo de banco de dados AdventureWorksDW no SQL Server 2005, siga o mesmo procedimento para mover os bancos de dados do usuário.

Movendo o banco de dados modelo

SQL Server 2005 e SQL Server 2000
No SQL Server 2005 e SQL Server 2000, você não pode desanexar os bancos de dados do sistema usando o procedimento armazenado sp_detach_db. Ao tentar executar a instrução sp_detach_db 'model', a seguinte mensagem de erro será exibida:
Servidor: Msg 7940, Nível 16, Estado 1, Linha 1
Não é possível desanexar os bancos de dados de sistema mestre, modelo, msdb e tempdb.
Para mover o banco de dados modelo, você dever iniciar o SQL Server juntamente com a opção -c, a opção -m e o sinalizador de rastreamento 3608. O sinalizador de rastreamento 3608 impede que o SQL Server recupera qualquer banco de dados exceto o banco de dados mestre.

Observação Você não poderá acessar qualquer banco de dados do usuário depois de fazer isso. Você não deve realizar nenhuma operação ao usar o sinalizador de traço além das etapas a seguir. Para adicionar o sinalizador de rastreamento como um parâmetro de inicialização do SQL Server no SQL Server 2000, siga estas etapas:
  1. No SQL Server Enterprise Manager, clique com o botão direito no nome do servidor e clique em Propriedades.
  2. Na guia Geral, clique em Parâmetros de inicialização.
  3. Adicione o seguinte novo parâmetro:
    -c -m -T3608
Se estiver usando um SQL Server 2005, será possível usar o SQL Server Configuration Manager para alterar os parâmetros de inicialização do serviço SQL Server. Para obter mais informações sobre como alterar os parâmetros de inicialização, vá para o site do MSDN Opções de inicialização do serviço Mecanismo de Banco de Dados.

Após adicionar a opção -c, a opção -m e o sinalizador de rastreamento 3608, siga estas etapas:
  1. Interrompa e reinicie o SQL Server.
  2. Desanexar o banco de dado modelo usando os seguintes comandos:
    use master   go   sp_detach_db 'model'   go
  3. Mova os arquivos Model.mdf e Modellog.ldf da pasta D:\Mssql7\Data para a pasta E:\Sqldata.
  4. Anexar novamente o banco de dados modelo usando os seguintes comandos:
    use master   go   sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'   go
  5. Remova -c -m -T3608 dos parâmetros de inicialização no SQL Server Enterprise Manager ou no SQL Server Configuration Manager.
  6. Interrompa e reinicie o SQL Server. É possível verificar a alteração nos locais do arquivo usando o procedimento sp_helpfile armazenado. Por exemplo, use o seguinte comando:
    use model   go   sp_helpfile   go


SQL Server 7.0
  1. Certifique-se de que o SQL Server Agent no momento não está sendo executado.
  2. Execute o mesmo procedimento para mover bancos de dados de usuários.

Movendo o banco de dados do MSDB

SQL Server 2005 e SQL Server 2000
Para mover o banco de dados MSDB, você deve iniciar o SQL Server juntamente com a opção -c, a opção -m e o sinalizador de rastreamento 3608. O sinalizador de rastreamento 3608 impede que o SQL Server recupere qualquer banco de dados exceto o banco de dados mestre. Para adicionar a opção-c, a opção -m e o sinalizador de rastreamento 3608, execute as seguintes etapas na seção "Movendo o banco de dados modelo". Após adicionar a opção -c, a opção -m e o sinalizador de rastreamento 3608, execute as seguintes etapas:
  1. Pare e reinicie o SQL Server.
  2. Certifique-se de que o serviço do SQL Server Agent no momento não está sendo executado.
  3. Desanexar o banco de dados msdb da seguinte maneira:
    use mastergosp_detach_db 'msdb'go
  4. Mova os arquivos Msdbdata.mdf e Msdblog.ldf do local atual (D:\Mssql8\Data) para o novo local (E:\Mssql8\Data).
  5. Remova -c -m -T3608 da caixa de parâmetros de inicialização no Enterprise Manager.
  6. Interrompa e reinicie o SQL Server.

    Observação Se tentar anexar novamente o banco de dados msdb iniciando o SQL Server junto com a opção-c, a opção -m e o sinalizador de rastreamento 3608, talvez você a seguinte mensagem de erro seja exibida:
    Servidor: Msg 615, Nível 21, Estado 1, Linha 1
    Não foi possível encontrar a tabela de banco de dados ID 3, nome 'modelo'.
  7. Anexar novamente o banco de dados msdb da seguinte forma:
    use mastergo sp_attach_db 'msdb','E:\Mssql8\Data\msdbdata.mdf','E:\Mssql8\Data\msdblog.ldf' go
Observação Se usar este procedimento e mover o banco de dados modelo, estará tentando desanexar o banco de dados msdb enquanto desanexa o banco de dados modelo. Ao fazer isto, será necessário anexar novamente o banco de dados modelo primeiro e anexar novamente o banco de dados msdb. Se anexar novamente o banco de dados msdb primeiro, a seguinte mensagem de erro será exibida ao tentar anexar novamente o banco de dados modelo:
Msg 0, Nível 11, Estado 0, Linha 0
Erro grave no comando atual. Os resultados, se houver, devem ser descartados.
Neste caso, você deve desanexar o banco de dados msdb, anexar novamente o banco de dados modelo e anexar novamente o banco de dados msdb.

Após mover o banco de dados msdb, talvez a seguinte mensagem de erro seja exibida:
Erro 229: Permissão EXECUTAR negada para objeto. 'Nome do objeto', banco de dados 'mestre', proprietário 'dbo'.
Este problema ocorre porque a cadeia de propriedade é interrompida. Os proprietários do banco de dados para o banco de dados msdb e para o banco de dados mestre não são iguais. Neste caso, a propriedade do banco de dados msdb foi alterada. Como solução alternativa, execute as seguintes declarações Transact-SQL. Você pode fazer isto usando o utilitário de prompt de comando do Osql.exe (SQL Server 7.0 e SQL Server 2000) ou o utilitário de prompt de comando Sqlcmd.exe (SQL Server 2005):
USE MSDB Go EXEC sp_changedbowner 'sa' Go
Para obter mais informações, clique no número abaixo para ler o artigo da Base de Dados de Conhecimento Microsoft:
272424 A verificação da cadeia de propriedade do objeto entre os bancos de dados depende do login mapeado para os proprietários do objeto


SQL Server 7.0
Observação Se você estiver usando este procedimento enquanto estiver movendo para os bancos de dados msdb e modelo, você deve anexar novamente primeiro o banco de dados modelo e anexar novamente o banco de dados msdb. Execute as seguintes etapas:
  1. Certifique-se de que o SQL Server Agent no momento não está sendo executado.
  2. Siga o mesmo procedimento para mover os bancos de dados do usuário.
Observação Se o SQL Server Agent estiver sendo executado, o procedimento armazenado sp_detach_db falhará e você receberá a seguinte mensagem:
Servidor: Msg 3702, Nível 16, Estado 1, Linha 0
Não é possível cancelar o banco de dados 'msdb' porque está sendo usado no momento.
A execução do DBCC foi concluída. Se o DBCC imprimiu mensagens de erro, entre em contato com o administrador do sistema.

Como mover o banco de dados mestre

SQL Server 2005
Para obter mais informações sobre como mover o banco de dados mestre e o banco de dados do Recurso, vá para o site do MSDN Mover os bancos de dados do sistema.

Você pode enfrentar uma falha quando mover o banco de dados mestre e o banco de dados do Recurso.Para obter mais informações, clique no número abaixo para ler o artigo da Base de Dados de Conhecimento Microsoft:
918695 Você pode enfrentar uma falha ao instalar o SQL Server 2005 Service Pack 1 em uma instância do SQL Server 2005


SQL Server 2000 e SQL Server 7.0
  1. Altere o caminho para os arquivos de dados mestre e de log mestre no SQL Server Enterprise Manager.

    Observação Você também pode alterar o local do log de erros aqui.
  2. Clique com o botão direito do mouse no Gerenciador do Sistema do SQL Server e clique em Propriedades.
  3. Clique em Parâmetros de inicialização para visualizar as seguintes entradas:
    -dD:\MSSQL7\data\master.mdf -eD:\MSSQL7\log\ErrorLog -lD:\MSSQL7\data\mastlog.ldf
    -d é o caminho totalmente qualificado para o arquivo de dados do banco de dados mestre.

    -e é o caminho totalmente qualificado para o arquivo de log de erro.

    -l é o caminho totalmente qualificado para o arquivo de log do banco de dados mestre.
  4. Altere esses valores, conforme segue:
    1. Remova as entradas atuais para os arquivos Master.mdf e Mastlog.ldf.
    2. Adicione novas entradas especificando o novo local:
      -dE:\SQLDATA\master.mdf      -lE:\SQLDATA\mastlog.ldf
  5. Interrompa o SQL Server.
  6. Copie os arquivos Master.mdf e Mastlog.ldf para o novo local (E:\Sqldata).
  7. Reinicie o SQL Server.

Movendo o banco de dados tempdb

Você pode mover os arquivos tempdb usando a instrução ALTER DATABASE.
  1. Determine os nomes de arquivo lógico para o banco de dados tempdb usando o sp_helpfile da seguinte maneira:
    use tempdbgosp_helpfilego
    O nome lógico para cada arquivo está contido na coluna nome. Esse exemplo usa nomes de arquivos padrão do tempdev e templog.
  2. Use a instrução ALTER DATABASE, especificando o nome de arquivo lógico da seguinte maneira:
    use mastergoAlter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')goAlter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')go
    Você deverá receber as seguintes mensagens que confirmam a alteração:
    Mensagem 1
    Arquivo 'tempdev' modificado em sysaltfiles. Exclua o arquivo antigo após reiniciar o SQL Server.
    Mensagem 2
    Arquivo 'templog' modificado em sysaltfiles. Exclua o arquivo antigo após reiniciar o SQL Server.
  3. O uso do sp_helpfile em tempdb não confirmará essas alterações até que você reinicie o SQL Server.
  4. Interrompa e reinicie o SQL Server.
Referências
Para obter mais informações, clique nos números abaixo para ler os artigos na Base de Dados de Conhecimento Microsoft (alguns artigos podem estar em inglês):
912397 O serviço do SQL Server não pode iniciar quando você altera um parâmetro de inicialização para uma instância em cluster do SQL Server 2000 ou do SQL Server 2005 para um valor que não é válido
274188 O tópico "Solução de problemas de usuários órfãos" nos Manuais Online está incompleto
246133 Como transferir logons e senhas entre instâncias do SQL Server
168001 As permissões e logons de usuário em um banco de dados podem estar incorretos depois que o banco de dados for restaurado

Para obter mais informações, consulte os seguintes manuais:
Microsoft Corporation
Microsoft SQL Server 7.0 System Administration Training Kit
Microsoft Press, 2001
Microsoft Corporation (em inglês)
MCSE Training Kit: Microsoft SQL Server 2000 System Administration
Microsoft Press, 2001
Microsoft Corporation (em inglês)
Microsoft SQL Server 2000 Resource Kit
Microsoft Press, 2001
Moving database files new location move place
Propriedades

ID do Artigo: 224071 - Última Revisão: 09/19/2013 12:15:00 - Revisão: 17.0

  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • kbsqlsetup kbinfo KB224071
Comentários
js">