Действия по переносу всех данных между серверами в SQL Server

окружающая среда

SQL Server

• SQL Server 2019
• SQL Server 2012

Способ переноса данных и преимущества и недостатки каждого из них

Существует несколько способов массового переноса данных базы данных SQL Server в SQL Server на других серверах. Каждый из них имеет свои преимущества и недостатки во время миграции, поэтому выберите метод, который наилучшим образом подходит для вашей среды.

предварительное условие

• Предположим, что SQL Server и среда SQL Server Management Studio установлены на каждом сервере.
• Этот совет позволяет сделать версии SQL Server разными.

Создание образца данных

При обсуждении переноса данных это делается в базе данных со следующей конфигурацией таблицы:

Источник переноса данных

SQL Server 2012

• Таблица 1 (Столбец:Ключ, Значение)
• Таблица 2 (Столбец:Ключ, Значение, Примечания)
• Таблица 3 (Столбец:Ключ, Значение)

Ниже приведен сценарий создания базы данных. Своевременно меняйте путь.

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

Ниже приведен скрипт создания таблицы.

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

Ниже приведен дополнительный сценарий записи:

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

Перенос данных в

SQL Server 2019

• Таблица 1 (Столбец:Ключ, Значение)
• Таблица 2 (Столбец:Ключ, Значение)

Записи в каждой таблице пусты.

Ниже приведен сценарий создания базы данных. Своевременно меняйте путь.

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

Ниже приведен скрипт создания таблицы.

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

Резервное копирование и восстановление базы данных миграция

Этот метод заключается в резервном копировании всей базы данных в виде файла на исходном сервере, копировании ее на конечный сервер и приеме. Таким образом, все конфигурации таблиц устанавливаются в место назначения, все из которых совпадают с исходными.

Кроме того, этот метод не позволяет выполнить миграцию с более новой версии SQL Server на более старую версию. (Если разница версий невелика, это возможно))

процедура

Запустите среду SQL Server Management Studio из исходного кода. Выберите папку, соответствующую версии SQL Server.

Войдите на целевой сервер. Компьютеры, использующие среду SQL Server Management Studio, отлично подходят для других компьютеров, но эти советы позволяют работать на собственных серверах.

В обозревателе объектов щелкните правой кнопкой мыши базу данных и выберите Резервное копирование для задач.

Удалите исходный путь к резервному копированию в списке мест назначения резервных копий в правом нижнем углу.

При удалении нажмите кнопку Добавить.

Нажмите кнопку изменить путь.

Я думаю, что папка «Backup» выбрана по умолчанию, поэтому введите имя файла для резервного копирования в имя файла ниже и нажмите кнопку OK. Имя файла является необязательным, поэтому сделайте его легким для понимания.

Нажмите кнопку ОК.

Убедитесь, что он добавлен в список, и нажмите кнопку ОК.

Поскольку файл резервной копии был создан в следующей папке, я перенесу этот файл на сервер, на который я хочу его перенести. Путь к папке зависит от версии SQL Server и имени экземпляра, поэтому своевременно проверьте его.

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

Состояние после копирования на конечный сервер. Он помещается в следующую папку, так как его легче загрузить, если поместить его в папку Backup SQL Server. Это также зависит от версии SQL Server и имени экземпляра, поэтому своевременно проверьте его.

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

Запустите среду SQL Server Management Studio на сервере, на который выполняется миграция. Вы вошли на свой сервер здесь.

В обозревателе объектов щелкните правой кнопкой мыши конечную базу данных и выберите Задачи, Восстановление и База данных.

Убедитесь, что на левой странице выбора выбран пункт «Общие», выберите «Устройство» из «Источника» справа и нажмите кнопку обзора справа.

Нажмите кнопку Добавить.

Выберите файл, который вы принесли.

Выберите «Параметры» на левой странице и проверьте следующие два. Если вы проверите эти два, вы можете заставить какую-либо другую систему подкачиваться при подключении к базе данных.

• Перезапись существующей базы данных
• Закрытие существующего подключения к конечной базе данных

Однако обратите внимание, что если восстановление завершается ошибкой с флажком «Закрыть существующее соединение с целевой базой данных», исходная база данных может быть недоступна.

При проверке нажмите кнопку ОК.

Можно убедиться, что конфигурация таблицы и записи полностью перенесены.

Кстати, если вы попытаетесь восстановить более новую версию базы данных SQL Server до более старой версии SQL Server, вы увидите следующую ошибку: Этот метод переноса данных основан на одной и той же версии друг к другу или на более новую версию.

Как вывести данные в виде SQL (скрипта) и запустить их в целевой базе данных

Это способ вывода всех записей в таблице в виде инструкций insert. Поскольку инструкции SQL почти одинаковы, в некоторых случаях их можно применять к другим типам баз данных, независимо от версии SQL Server.

Однако, поскольку записи записываются для вставки предложений одно за другим, размер файла увеличивается. Поэтому по мере увеличения количества записей он бесполезен, поэтому это метод, который можно использовать, когда число небольшое.

Он также создает SQL для таблицы, из которой он был перенесен, поэтому он недоступен, если он отличается от конфигурации таблицы, в которую он переносится. Можно также включить создание таблицы в SQL, но необходимо удалить таблицу один раз в месте назначения.

процедура

Запустите среду SQL Server Management Studio из исходного кода. Выберите папку, соответствующую версии SQL Server.

Войдите на целевой сервер. Компьютеры, использующие среду SQL Server Management Studio, отлично подходят для других компьютеров, но эти советы позволяют работать на собственных серверах.

Щелкните правой кнопкой мыши на целевой базе данных и выберите «Задача» и «Создать скрипт».

Нажмите кнопку Далее.

Вы можете выбрать, для каких таблиц перенести данные. Выберите все или выберите определенную таблицу здесь.

Поскольку выходными данными являются SQL, вы можете выбрать «Файл», «Буфер обмена» или «Показать в окне». После выбора нажмите кнопку «Дополнительно» в правом верхнем углу.

Выберите Только данные из раздела "Типы данных для создания скриптов" в группе Общие.

После этого нажмите «Далее» для подтверждения. Вы можете видеть, что SQL сгенерирован. Все, что остается, это запустить этот SQL в месте назначения.

Поскольку это просто инструкция insert, если вы хотите, чтобы данные были одинаковыми, вам нужно заранее удалить все записи в месте назначения.

Перенос конфигураций таблиц

Если вы также хотите перенести конфигурации таблиц, установите для предыдущего выбора «Тип данных для создания сценариев» значение Схема и данные.

Сценарий создания таблицы также создается следующим образом: Если вы хотите использовать это, оно будет выполнено после удаления переносимой таблицы.

Он также генерирует сценарий создания базы данных, если он также включен.

Перенос данных с помощью импорта и экспорта данных

При установке SQL Server также устанавливается средство импорта и экспорта данных. Его можно использовать для переноса данных из таблицы на другой сервер.

Преимущество этой миграции средств заключается в том, что данные также могут быть перенесены в более старые версии SQL Server. Он немного сложен в использовании, но его можно перенести на уровне нескольких минут даже с примерно 1 миллионом данных, поэтому он также подходит для миграции больших данных. Обратите внимание, однако, что исходный и конечный серверы должны быть подключены к частной сети и не могут использоваться между серверами с совершенно разными сетями.

предварительное условие

Создайте проверку подлинности SQL Server или проверку подлинности Windows, чтобы можно было получить доступ к каждому SQL Server с компьютера, использующего это средство.

В этом разделе Советы используется это средство с сервера, с которого оно было перенесено.

процедура

Выберите Импорт и экспорт данных в меню Пуск. Он включен в папку в SQL Server, но имя папки зависит от версии SQL Server, поэтому своевременно ищите его.

Нажмите кнопку Далее.

Выберите базу данных, из которой требуется выполнить миграцию. На этот раз он запущен на исходном сервере, поэтому вы можете пройти проверку подлинности Windows.

Затем задайте параметры подключения для целевой базы данных. Проверка подлинности SQL Server используется, поскольку она находится на другом сервере.

Установите флажок «Копировать данные из одной или нескольких таблиц или представлений» и нажмите следующую кнопку.

Выберите таблицу, которую требуется перенести. В этом списке также отображается представление, поэтому не проверяйте его.

Если имена исходной и конечной таблиц совпадают, переносимая таблица выбирается автоматически. Он также автоматически устанавливает сопоставление имен столбцов, если имена столбцов совпадают.

Вот некоторые вещи, которые следует иметь в виду:

• Если первичный ключ автоматически нумерован, миграция завершится ошибкой, если вы не установите флажок «Разрешить вставку идентификатора» в разделе «Изменить сопоставление»
• Если нет таблиц для миграции, создается таблица
• Если конечная таблица не имеет одинакового имени столбца, она не будет перенесена. Не продолжайте миграцию или изменение целевого столбца.
• Записи добавляются по умолчанию. Если вы хотите заменить его, удалите запись в месте назначения заранее или установите флажок «Удалить строки в целевой таблице» в разделе «Изменить сопоставление»

На самом деле, вы должны учитывать вышесказанное, но в этих советах нет данных в пункте назначения и нет автоматической нумерации, поэтому вы можете продолжить, ничего не устанавливая.

Убедитесь, что установлен флажок «Запустить сейчас» и нажмите «Далее».

Нажмите кнопку Готово.

Начнется перенос данных. Закрыть, когда все миграции будут завершены.

Если вы посмотрите на целевую базу данных, вы можете убедиться, что таблица, которая не существовала, была создана. Вы также можете видеть, что для существующих таблиц больше нет столбцов.

Вы можете убедиться, что данные переносятся. Обратите внимание, что столбцы в таблице 2 не сопоставлены и не были перенесены.