Mover tabelas para outro banco de dados SQL2008 (incluindo índices, gatilhos etc.)

Fazer o script das tabelas e usar o SSIS para transferir os dados seria a maneira mais confiável e eficaz de mover os dados para o novo banco de dados.

Na verdade, fizemos isso usando muitos scripts manuais em conjunto com o assistente de importação, mas esta manhã encontrei uma resposta melhor, cortesia do artigo do blog de Tibor Karaszi .

Parte de nossa frustração aqui foi que o SQL 2000 "DTS Import/Export Wizard" torna isso quase trivialmente fácil selecionando "Copy Objects and Data":

Esta terceira opção é a que contém a capacidade de incluir índices/gatilhos, etc:

Esta opção foi REMOVIDA do SQL 2005/2008 Import Wizard. Por quê? Nenhuma idéia:

Em 2005/2008, você aparentemente tem que criar manualmente um pacote SSIS no BIDS e usar a Tarefa Transferir Objetos do SQL Server , que contém todas as mesmas opções que estavam no assistente 2000:

Eu consideraria fazer um script da tabela ou usar uma ferramenta de comparação (por exemplo, Red Gate) para gerar as tabelas no banco de dados de destino. Sem índices ou restrições ainda.

Então eu consideraria restaurar o banco de dados com um nome diferente no mesmo servidor e fazer

 INSERT newdb.dbo.newtable SELECT * FROM olddb.dbo.oldtable

.. para cada tabela, com SET IDENTITY INSERT ON se necessário

Então eu adicionaria índices e restrições depois de carregar os dados.

Depende do seu nível de conforto com o SSIS (resposta do mrdenny) ou se você preferir SQL bruto.

Eu acrescentaria à resposta do Sr. Denny: Script do esquema de tabelas e use o BCP para mover os dados. Se você não estiver familiarizado com o SSIS, usar BCP e lotes deve ser fácil de fazer. Para milhões de linhas, nada supera o BCP (inserção em massa) :).

Eu sou o único que está completamente desconfortável com o SSIS.

Quando as tabelas de origem não têm colunas de identidade

1. crie um banco de dados vazio no servidor de destino
2. crie um servidor vinculado ao servidor de origem no servidor de destino
3. execute o script abaixo no banco de dados de origem para gerar as instruções select * into ...
4. execute o script gerado do banco de dados de destino
5. script de chaves primárias, índices, gatilhos, funções e procedimentos do banco de dados de origem
6. crie esses objetos pelo script gerado

Agora o T-SQL para gerar as instruções Select * into ...

SET NOCOUNT ON

declare @name sysname
declare @sql varchar(255)

declare db_cursor cursor for
select name from sys.tables order by 1
open db_cursor

fetch next from db_cursor into @name
while @@FETCH_STATUS = 0
begin
    Set @sql = 'select * into [' + @name + '] from [linked_server].[source_db].[dbo].[' + @name + '];'
    print @sql

    fetch next from db_cursor into @name
end

close db_cursor
deallocate db_cursor

Isso gera uma linha para cada tabela para copiar como

select * into [Table1] from [linked_server].[source_db].[dbo].[Table1];

Caso as tabelas contenham colunas de identidade, eu crio scripts nas tabelas incluindo propriedade de identidade e chaves primárias.

Eu não uso insert into ... select ... usando um servidor vinculado neste caso, pois essa não é uma técnica em massa. Estou trabalhando em alguns scripts do PowerShell semelhantes a [esta pergunta SO 1 , mas ainda estou trabalhando no tratamento de erros. Tabelas realmente grandes podem causar erros de falta de memória, pois uma tabela inteira é carregada na memória, antes de ser enviada via SQLBulkCopy para o banco de dados.

A recriação de índices etc. é semelhante ao caso acima. Desta vez posso pular a recriação das chaves primárias.

Você pode usar ferramentas de comparação que comparam esquemas e dados de banco de dados e primeiro sincronizar um esquema de banco de dados em branco com o banco de dados original para criar todas as tabelas.

Em seguida, sincronize os dados do banco de dados original com o novo (todas as tabelas estão lá, mas estão todas vazias) para inserir os registros nas tabelas

Eu uso ApexSQL Diff e ApexSQL Data Diff para isso, mas existem outras ferramentas semelhantes.

O bom desse processo é que você não precisa sincronizar os bancos de dados usando a ferramenta, pois isso pode ser bastante doloroso para milhões de linhas.

Você pode simplesmente criar um script INSERT INTO SQL (não se surpreenda se forem vários shows) e executá-lo.

Como scripts tão grandes não podem ser abertos no SQL Server Management Studio, eu uso sqlcmd ou osql

Como @mrdenny mencionou -

1. script de tabelas primeiro com todos os índices, FK's, etc e crie tabelas em branco no banco de dados de destino.

Em vez de usar o SSIS, use o BCP para inserir dados

1. bcp os dados usando o script abaixo. defina o SSMS no modo de texto e copie a saída gerada pelo script abaixo em um arquivo bat.

   -- save below output in a bat file by executing below in SSMS in TEXT mode
   
   -- clean up: create a bat file with this command --> del D:\BCP\*.dat 
   
   select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" ' /* path to BCP.exe */
       +  QUOTENAME(DB_NAME())+ '.' /* Current Database */
       +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'            
       +  QUOTENAME(name)  
       +  ' out D:\BCP\'  /* Path where BCP out files will be stored */
       +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' 
       +  REPLACE(name,' ','') 
       + '.dat -T -E -SServerName\Instance -n' /* ServerName, -E will take care of Identity, -n is for Native Format */
   from sys.tables
   where is_ms_shipped = 0 and name <> 'sysdiagrams'                       /* sysdiagrams is classified my MS as UserTable and we dont want it */
   /*and schema_name(schema_id) <> 'unwantedschema'    */                             /* Optional to exclude any schema  */
   order by schema_name(schema_id)
   

2. Execute o arquivo bat que irá gerar os arquivos .dat na pasta que você especificou.

3. Execute o script abaixo no

   --- Execute this on the destination server.database from SSMS.
   
   --- Make sure the change the @Destdbname and the bcp out path as per your environment.
   
   declare @Destdbname sysname
   set @Destdbname = 'destinationDB' /* Destination Database Name where you want to Bulk Insert in */
   select 'BULK INSERT ' 
   /*Remember Tables must be present on destination database */ 
   + QUOTENAME(@Destdbname) + '.' 
   + QUOTENAME(SCHEMA_NAME(SCHEMA_ID)) 
   + '.' + QUOTENAME(name) 
   + ' from ''D:\BCP\' /* Change here for bcp out path */ 
   + REPLACE(SCHEMA_NAME(schema_id), ' ', '') + '_' + REPLACE(name, ' ', '') 
   + '.dat'' with ( KEEPIDENTITY, DATAFILETYPE = ''native'', TABLOCK )' 
   + char(10) 
   + 'print ''Bulk insert for ' + REPLACE(SCHEMA_NAME(schema_id), ' ', '') + '_' + REPLACE(name, ' ', '') + ' is done... ''' 
   + char(10) + 'go'
      from sys.tables
      where is_ms_shipped = 0
   and name <> 'sysdiagrams' /* sysdiagrams is classified my MS as UserTable and we dont want it */
   and schema_name(schema_id) <> 'unwantedschema' /* Optional to exclude any schema */
       order by schema_name(schema_id) 
   

4. Execute a saída usando o SSMS para inserir dados de volta nas tabelas.

Este é um método bcp muito rápido, pois usa o modo nativo.