Passaggi per eseguire la migrazione di tutti i dati tra server in SQL Server

ambiente

SQL Server

• SQL Server 2019
• SQL Server 2012

Come viene migrata la migrazione dei dati e i vantaggi e gli svantaggi di ciascuno

Esistono diversi modi per eseguire la migrazione dei dati del database di SQL Server a SQL Server in altri server in blocco: Ognuno ha i suoi vantaggi e svantaggi durante la migrazione, quindi scegli il metodo più adatto al tuo ambiente.

precondizione

• Si supponga che SQL Server e SQL Server Management Studio siano installati in ogni server.
• Questo suggerimento osa rendere diverse le versioni di SQL Server.

Creare dati di esempio

Quando si discute della migrazione dei dati, questa operazione viene eseguita nel database con la configurazione della tabella seguente:

Origine migrazione dati

SQL Server 2012

• Tabella 1 (Colonna:Chiave, Valore)
• Tabella 2 (Colonna:Chiave, Valore, Osservazioni)
• Tabella 3 (Colonna:Chiave, Valore)

Di seguito è riportato uno script di creazione del database. Cambia il percorso in modo tempestivo.

USE [master]
GO

CREATE DATABASE [SampleDatabase]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'SampleDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SampleDatabase.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'SampleDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SampleDatabase_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Di seguito è riportato uno script per la creazione di tabelle.

USE [SampleDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[テーブル1](
	[キー] [int] NOT NULL,
	[値] [nvarchar](50) NULL,
 CONSTRAINT [PK_テーブル1] PRIMARY KEY CLUSTERED 
(
	[キー] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[テーブル2](
	[キー] [int] NOT NULL,
	[値] [nvarchar](50) NULL,
	[備考] [nvarchar](50) NULL,
 CONSTRAINT [PK_テーブル2] PRIMARY KEY CLUSTERED 
(
	[キー] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[テーブル3](
	[キー] [int] NOT NULL,
	[値] [nvarchar](50) NULL,
 CONSTRAINT [PK_テーブル3] PRIMARY KEY CLUSTERED 
(
	[キー] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Di seguito è riportato uno script di record aggiuntivo:

USE [SampleDatabase]
GO
INSERT [dbo].[テーブル1] ([キー], [値]) VALUES (1, N'1aaa')
GO
INSERT [dbo].[テーブル1] ([キー], [値]) VALUES (2, N'1bbb')
GO
INSERT [dbo].[テーブル2] ([キー], [値], [備考]) VALUES (1, N'2aaa', N'2remarks')
GO
INSERT [dbo].[テーブル2] ([キー], [値], [備考]) VALUES (2, N'2bbb', N'2remarks')
GO
INSERT [dbo].[テーブル3] ([キー], [値]) VALUES (1, N'3aaa')
GO
INSERT [dbo].[テーブル3] ([キー], [値]) VALUES (2, N'3bbb')
GO

Migrazione dei dati a

SQL Server 2019

• Tabella 1 (Colonna:Chiave, Valore)
• Tabella 2 (Colonna:Chiave, Valore)

I record in ogni tabella sono vuoti.

Di seguito è riportato uno script di creazione del database. Cambia il percorso in modo tempestivo.

USE [master]
GO

CREATE DATABASE [SampleDatabase]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'SampleDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\SampleDatabase.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'SampleDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\SampleDatabase_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO

Di seguito è riportato uno script per la creazione di tabelle.

USE [SampleDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[テーブル1](
	[キー] [int] NOT NULL,
	[値] [nvarchar](50) NULL,
 CONSTRAINT [PK_テーブル1] PRIMARY KEY CLUSTERED 
(
	[キー] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[テーブル2](
	[キー] [int] NOT NULL,
	[値] [nvarchar](50) NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
	[キー] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Modalità di backup e ripristino del database per il backup e il ripristino della migrazione

Questo metodo consente di eseguire il backup dell'intero database come file nel server di origine, copiarlo nel server di destinazione e inserirlo. Di conseguenza, tutte le configurazioni di tabella sono impostate sulla destinazione, tutte uguali all'origine.

Inoltre, questo metodo non consente di eseguire la migrazione da una versione più recente di SQL Server a una versione precedente. (Se la differenza di versione è piccola, potrebbe essere possibile))

procedimento

Avviare SQL Server Management Studio dall'origine. Scegliere una cartella corrispondente alla versione di SQL Server in uso.

Accedere al server di destinazione. I PC che utilizzano SQL Server Management Studio vanno bene su altri PC, ma questo suggerimento consente di operare tutti sui propri server.

In Esplora oggetti fare clic con il pulsante destro del mouse sul database e scegliere Backup per attività.

Eliminare il percorso di backup iniziale nell'elenco delle destinazioni di backup nell'angolo in basso a destra.

Quando lo elimini, fai clic sul pulsante Aggiungi.

Fare clic sul pulsante Cambia percorso.

Penso che la cartella "Backup" sia selezionata per impostazione predefinita, quindi inserisci il nome del file di cui eseguire il backup nel nome del file sottostante e fai clic sul pulsante OK. Il nome del file è facoltativo, quindi rendilo facile da capire.

Fare clic sul pulsante OK.

Verificare che sia stato aggiunto all'elenco e quindi fare clic sul pulsante OK.

Poiché il file di backup è stato creato nella seguente cartella, porterò questo file al server su cui voglio migrarlo. Il percorso della cartella dipende dalla versione di SQL Server e dal nome dell'istanza, quindi controllalo in modo tempestivo.

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup

Stato dopo la copia nel server di destinazione. Viene inserito nella cartella seguente perché è più facile da caricare se lo si inserisce nella cartella Backup di SQL Server. Questo dipende anche dalla versione di SQL Server e dal nome dell'istanza, quindi controllalo in modo tempestivo.

C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Backup

Avviare SQL Server Management Studio nel server a cui si sta eseguendo la migrazione. Hai effettuato l'accesso al tuo server qui.

In Esplora oggetti fare clic con il pulsante destro del mouse sul database di destinazione e quindi scegliere Attività, Ripristina e Database.

Assicurati che Generale sia selezionato nella selezione della pagina a sinistra, seleziona "Dispositivo" da "Sorgente" a destra e fai clic sul pulsante Sfoglia a destra.

Fare clic sul pulsante Aggiungi.

Seleziona il file che hai portato.

Seleziona "Opzioni" dalla selezione della pagina di sinistra e controlla le seguenti due. Se si selezionano questi due, è possibile forzare lo scambio di un altro sistema mentre si è connessi al database.

• Sovrascrivere un database esistente
• Chiudere una connessione esistente al database di destinazione

Tuttavia, se il ripristino non riesce con l'opzione "Chiudi connessione esistente al database di destinazione" selezionata, il database originale potrebbe non essere disponibile.

Quando si controlla, fare clic sul pulsante OK.

È possibile verificare che la configurazione della tabella e i record siano stati completamente migrati.

Per inciso, se si tenta di ripristinare una versione più recente di un database di SQL Server a una versione precedente di SQL Server, verrà visualizzato l'errore seguente: Questo metodo di migrazione dei dati si basa sulla stessa versione l'una con l'altra o su una versione più recente.

Procedura: generare dati come SQL (script) ed eseguirli nel database di destinazione

Ecco come eseguire l'output di tutti i record in una tabella come istruzioni insert. Poiché le istruzioni SQL sono quasi le stesse, in alcuni casi possono essere applicate ad altri tipi di database, indipendentemente dalla versione di SQL Server.

Tuttavia, poiché i record vengono scritti per inserire frasi una per una, la dimensione del file aumenta. Pertanto, all'aumentare del numero di record, è inutile, quindi è un metodo che può essere utilizzato quando il numero è piccolo.

Genera inoltre SQL per la tabella da cui è stata eseguita la migrazione, quindi non è disponibile se è diverso dalla configurazione della tabella in cui è stata eseguita la migrazione. È inoltre possibile includere la creazione di una tabella in SQL, ma è necessario eliminare la tabella una volta nella destinazione.

procedimento

Avviare SQL Server Management Studio dall'origine. Scegliere una cartella corrispondente alla versione di SQL Server in uso.

Accedere al server di destinazione. I PC che utilizzano SQL Server Management Studio vanno bene su altri PC, ma questo suggerimento consente di operare tutti sui propri server.

Fare clic con il pulsante destro del mouse sul database di destinazione e selezionare "Attività" e "Genera script".

Fare clic su Avanti.

È possibile scegliere per quali tabelle eseguire la migrazione dei dati. Seleziona tutto o seleziona una tabella specifica qui.

Poiché l'output è SQL, è possibile selezionare "File", "Appunti" o "Mostra in finestra". Una volta selezionato, fai clic sul pulsante "Avanzate" in alto a destra.

Selezionare Solo dati da "Tipi di dati per generare script" nel gruppo Generale.

Successivamente, seleziona "Avanti" per confermare. Puoi vedere che SQL è generato. Tutto ciò che resta è eseguire questo SQL sulla destinazione.

Poiché si tratta solo di un'istruzione insert, se si desidera che i dati siano gli stessi, è necessario eliminare tutti i record nella destinazione in anticipo.

Eseguire la migrazione anche delle configurazioni delle tabelle

Se si desidera eseguire anche la migrazione delle configurazioni delle tabelle, impostare la selezione precedente "Tipo di dati per generare script" su Schema e dati.

Viene inoltre generato uno script per la creazione di tabelle come segue: Se si desidera utilizzarlo, verrà eseguito dopo aver eliminato la tabella da migrare.

Genera anche uno script di creazione del database se è incluso.

Come eseguire la migrazione dei dati utilizzando l'importazione e l'esportazione dei dati

Durante l'installazione di SQL Server, si installa anche uno strumento denominato Importa ed esporta dati. È possibile utilizzarlo per eseguire la migrazione dei dati da una tabella a un altro server.

Il vantaggio di questa migrazione degli strumenti è che i dati possono essere migrati anche a versioni precedenti di SQL Server. È un po 'difficile da usare, ma può essere migrato a livello di diversi minuti anche con circa 1 milione di dati, quindi è adatto anche per la migrazione di dati di grandi dimensioni. Si noti, tuttavia, che i server di origine e di destinazione devono essere connessi su una rete privata e non possono essere utilizzati tra server con reti completamente diverse.

precondizione

Creare l'autenticazione di SQL Server o l'autenticazione di Windows in modo da poter accedere a ogni SQL Server da un PC che utilizza questo strumento.

Questo suggerimento utilizza questo strumento dal server da cui è stata eseguita la migrazione.

procedimento

Selezionare Importa ed esporta dati dal menu Start. È incluso in una cartella in SQL Server, ma il nome della cartella varia a seconda della versione di SQL Server, quindi cercalo in modo tempestivo.

Fare clic su Avanti.

Selezionare il database da cui si desidera eseguire la migrazione. Questa volta è in esecuzione sul server di origine, quindi è possibile passare attraverso l'autenticazione di Windows.

Quindi, impostare le impostazioni di connessione per il database di destinazione. L'autenticazione di SQL Server viene utilizzata perché si trova in un server diverso.

Seleziona "Copia dati da una o più tabelle o viste" e fai clic sul pulsante Successivo.

Selezionare la tabella di cui si desidera eseguire la migrazione. Questo elenco mostra anche la vista, quindi non controllarla.

Se i nomi delle tabelle di origine e di destinazione sono gli stessi, la tabella di cui eseguire la migrazione viene selezionata automaticamente. Imposta inoltre automaticamente il mapping dei nomi delle colonne se i nomi delle colonne sono gli stessi.

Ecco alcune cose da tenere a mente:

• Se la chiave primaria viene numerata automaticamente, la migrazione avrà esito negativo se non si seleziona "Consenti inserimento ID" da "Modifica mappatura"
• Se non ci sono tabelle a cui eseguire la migrazione, viene creata una tabella
• Se la tabella di destinazione non ha lo stesso nome di colonna, non verrà eseguita la migrazione. Non continuare a eseguire la migrazione o modificare la colonna di destinazione.
• I record vengono aggiunti per impostazione predefinita. Se si desidera sostituirlo, eliminare il record a destinazione in anticipo o selezionare "Elimina righe nella tabella di destinazione" da "Modifica mappatura"

In effetti, devi considerare quanto sopra, ma in questo Suggerimenti, non ci sono dati a destinazione e non c'è numerazione automatica, quindi puoi procedere senza impostare nulla.

Assicurati che "Esegui ora" sia selezionato e fai clic su "Avanti".

Fai clic su Fine.

Inizia la migrazione dei dati. Chiudi al termine di tutte le migrazioni.

Se si esamina il database di destinazione, è possibile verificare che sia stata creata una tabella che non esisteva. È inoltre possibile vedere che non ci sono più colonne per le tabelle esistenti.

È possibile verificare che i dati siano in fase di migrazione. Le colonne della nota nella tabella 2 non sono mappate e non sono state migrate.