Przenoszenie tabel do innej bazy danych SQL2008 (w tym indeksów, wyzwalaczy itp.)

Muszę przenieść całą grupę (ponad 100) dużych (miliony wierszy) tabel z jednej bazy danych SQL2008 do drugiej.

Pierwotnie użyłem Kreatora importu / eksportu, ale we wszystkich tabelach docelowych brakowało kluczy głównych i obcych, indeksów, ograniczeń, wyzwalaczy itp. (Kolumny tożsamości zostały również przekonwertowane na zwykłe INT, ale myślę, że właśnie przegapiłem pole wyboru w polu czarodziej.)

Jak to zrobić?

Gdyby to było tylko kilka tabel, wróciłbym do źródła, wypisałbym definicję tabeli (ze wszystkimi indeksami itp.), A następnie uruchomiłem część tworzenia indeksu skryptu w miejscu docelowym. Ale przy tak wielu stołach wydaje się to niepraktyczne.

Gdyby nie było tak dużo danych, mógłbym użyć kreatora „Utwórz skrypty ...”, aby wyskrybować źródło, w tym dane, ale skrypt o długości 72 metrów nie wydaje się dobrym pomysłem!

Skryptowanie tabel, a następnie używanie SSIS do przesyłania danych byłoby najbardziej niezawodnym i skutecznym sposobem przenoszenia danych do nowej bazy danych.

Zrobiliśmy to za pomocą wielu ręcznych skryptów w połączeniu z kreatorem importu, ale dziś rano znalazłem lepszą odpowiedź dzięki uprzejmości blogu Tibora Karasziego .

Częścią naszej frustracji było to, że SQL 2000 „Kreator importu / eksportu DTS” sprawia, że ​​jest to prawie banalnie proste, wybierając „Kopiuj obiekty i dane”:

Ta trzecia opcja zawiera opcję włączenia indeksów / wyzwalaczy itp .:

Ta opcja została usunięta z Kreatora importu SQL 2005/2008 . Dlaczego? Brak pomysłu:

W roku 2005/2008 najwyraźniej musisz ręcznie utworzyć pakiet SSIS w BIDS i użyć zadania Transfer SQL Server Objects , które zawiera wszystkie te same opcje, które były dostępne w kreatorze 2000:

Zastanowiłbym się, czy nie utworzyć skryptów w tabeli, lub użyć narzędzi porównawczych (np. Red Gate), aby wygenerować tabele w docelowej bazie danych. Bez indeksów i ograniczeń jeszcze.

Następnie rozważę przywrócenie bazy danych pod inną nazwą na tym samym serwerze i zrobienie tego

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

.. dla każdej tabeli, w razie potrzeby ustaw WŁĄCZANIE WKŁADKI TOŻSAMOŚCI

Następnie dodawałbym indeksy i ograniczenia po wczytaniu danych.

Zależy to od Twojego poziomu komfortu w SSIS (odpowiedź mrdenny) lub od tego, czy wolisz surowy SQL.

Dodałbym do odpowiedzi pana Denny'ego: wypisz schemat tabel, a następnie użyj BCP do przeniesienia danych. Jeśli nie znasz SSIS, korzystanie z BCP i partii powinno być łatwe do zrobienia. Dla milionów wierszy nic nie przebije BCP (wkładka zbiorcza) :).

To ja czuję się całkowicie niekomfortowo z SSIS.

Gdy tabele źródłowe nie mają kolumn tożsamości

1. utwórz pustą bazę danych na serwerze docelowym
2. utwórz serwer połączony z serwerem źródłowym na serwerze docelowym
3. uruchom poniższy skrypt w źródłowej bazie danych, aby wygenerować instrukcje select * do ...
4. uruchom wygenerowany skrypt z docelowej bazy danych
5. skrypty podstawowe klucze, indeksy, wyzwalacze, funkcje i procedury ze źródłowej bazy danych
6. utwórz te obiekty za pomocą wygenerowanego skryptu

Teraz T-SQL do generowania instrukcji 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

To generuje wiersz dla każdej tabeli do skopiowania

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

W przypadku, gdy tabele zawierają kolumny tożsamości, tworzę skrypty tabel, w tym właściwość tożsamości i klucze podstawowe.

Nie używam wstawiania w ... select ... w tym przypadku za pomocą połączonego serwera, ponieważ nie jest to technika zbiorcza. Pracuję nad niektórymi skryptami PowerShell podobnymi do [tego SO pytania 1 , ale nadal pracuję nad obsługą błędów. Naprawdę duże tabele mogą powodować błędy braku pamięci, ponieważ cała tabela jest ładowana do pamięci, zanim zostanie wysłana przez SQLBulkCopy do bazy danych.

Odtwarzanie indeksów itp. Jest podobne do powyższego przypadku. Tym razem mogę pominąć odtwarzanie kluczy podstawowych.

Możesz użyć narzędzi porównawczych, które porównują schematy i dane bazy danych i najpierw zsynchronizują puste miejsce schemat bazy danych z oryginalną bazą danych, aby utworzyć wszystkie tabele.

Następnie zsynchronizuj dane z oryginalnej bazy danych z nową (wszystkie tabele są tam, ale wszystkie są puste), aby wstawić rekordy do tabel

Używam ApexSQL Diff i ApexSQL Data Diff , ale istnieją inne podobne narzędzia.

Zaletą tego procesu jest fakt, że nie trzeba synchronizować baz danych za pomocą tego narzędzia, ponieważ może to być bardzo bolesne dla milionów wierszy.

Możesz po prostu utworzyć skrypt INSERT INTO SQL (nie zdziw się, jeśli jest kilka koncertów) i go uruchom.

Ponieważ tak dużych skryptów nie można nawet otworzyć w SQL Server Management Studio, używam sqlcmd lub osql

Jak wspomniano @mrdenny -

1. najpierw wypisz skrypt ze wszystkimi indeksami, FK itp. i utwórz puste tabele w docelowej bazie danych.

Zamiast korzystać z SSIS, użyj BCP do wstawienia danych

1. bcp odczyt danych za pomocą poniższego skryptu. ustaw SSMS w trybie tekstowym i skopiuj dane wyjściowe wygenerowane przez poniższy skrypt w pliku nietoperza.

   -- 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. Uruchom plik bat, który wygeneruje pliki .dat w określonym folderze.

3. Uruchom poniżej skryptu na

   --- 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. Uruchom dane wyjściowe za pomocą SSMS, aby wstawić dane z powrotem do tabel.

Jest to bardzo szybka metoda BCP, ponieważ wykorzystuje tryb macierzysty.