Steps to migrate all data between servers in SQL Server

environment

SQL Server

• SQL Server 2019
• SQL Server 2012

How data migration is migrated and the advantages and disadvantages of each

There are several ways to migrate SQL Server database data to SQL Server on other servers in bulk: Each has its advantages and disadvantages during migration, so choose the method that best suits your environment.

precondition

• Assume that sql server and SQL Server Management Studio are installed on each server.
• This Tips dares to make sql server versions different.

Create sample data

When discussing data migration, you do so in the database with the following table configuration:

Data migration source

SQL Server 2012

• Table 1 (Column:Key, Value)
• Table 2 (Column:Key, Value, Remarks)
• Table 3 (Column:Key, Value)

The following is a database creation script. Change the path in a timely manner.

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

Below is a table creation script.

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

The following is an additional record script:

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

Data migration to

SQL Server 2019

• Table 1 (Column:Key, Value)
• Table 2 (Column:Key, Value)

The records in each table are empty.

The following is a database creation script. Change the path in a timely manner.

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

Below is a table creation script.

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

How database backup and restore migration

This method is to back up the entire database as a file on the source server, copy it to the destination server, and ingest it. Therefore, all table configurations are set to the destination, all of which are the same as the source.

Also, this method does not allow you to migrate from a newer version of SQL Server to an older version. (If the version difference is small, it may be possible))

procedure

Start SQL Server Management Studio from the source. Choose a folder that matches your SQL Server version.

Log in to the target server. PCs using SQL Server Management Studio are fine on other PCs, but this Tips lets you all operate on your own servers.

From Object Explorer, right-click the database and select Backup for Tasks.

Delete the initial backup path in the list of backup destinations in the lower right corner.

When you delete it, click the Add button.

Click the change path button.

I think that the "Backup" folder is selected by default, so enter the file name to back up in the file name below and click the OK button. The file name is optional, so make it easy to understand.

Click the OK button.

Verify that it has been added to the list, and then click the OK button.

Since the backup file has been created in the following folder, I will take this file to the server to which I want to migrate it. The folder path depends on the version of SQL Server and the name of the instance, so check it in a timely manner.

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

Status after copying to the destination server. It is placed in the following folder because it is easier to load if you put it in the Backup folder of SQL Server. This also depends on the version of SQL Server and the instance name, so please check it in a timely manner.

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

Start SQL Server Management Studio on the server you are migrating to. You are logged in to your server here.

From Object Explorer, right-click the destination database, and then select Tasks, Restore, and Database.

Make sure General is selected in the left page selection, select "Device" from the "Source" on the right, and click the browse button on the right.

Click the Add button.

Select the file you brought.

Select "Options" from the selection of the left page and check the following two. If you check these two, you can force some other system to swap while connected to the database.

• Overwrite an existing database
• Close an existing connection to the destination database

However, please note that if the restore fails with "Close existing connection to the destination database" is checked, the original database may not be available.

When you check, click the OK button.

You can verify that the table configuration and records have been fully migrated.

Incidentally, if you try to restore a newer version of a SQL Server database to an older version of SQL Server, you will see the following error: This method of data migration is based on the same version to each other or to a newer version.

How to: Output Data as SQL (Script) and Run it on the Destination Database

This is how to output all records in a table as insert statements. Because SQL statements are almost the same, they can be applied to other types of databases in some cases, regardless of the version of SQL Server.

However, since records are written to insert sentences one by one, the file size increases. Therefore, as the number of records increases, it is useless, so it is a method that can be used when the number is small.

It also generates SQL for the table from which it was migrated, so it is not available if it is different from the table configuration to which it is migrated. You can also include the creation of a table in SQL, but you must delete the table once at the destination.

procedure

Start SQL Server Management Studio from the source. Choose a folder that matches your SQL Server version.

Log in to the target server. PCs using SQL Server Management Studio are fine on other PCs, but this Tips lets you all operate on your own servers.

Right-click on the target database and select "Task" and "Generate Script".

Click Next.

You can choose which tables to migrate data for. Select all or select a specific table here.

Since the output is SQL, you can select "File", "Clipboard", or "Show in Window". Once selected, click the "Advanced" button at the top right.

Select Data only from "Types of data to generate scripts" in the General group.

After that, select "Next" to confirm. You can see that SQL is generated. All that's left is to run this SQL on the destination.

Since it is just an insert statement, if you want the data to be the same, you need to delete all records at the destination in advance.

Migrate table configurations as well

If you also want to migrate table configurations, set the previous "Type of data to generate scripts" selection to Schema and Data.

A table creation script is also generated as follows: If you want to use this, it will be executed after deleting the table to be migrated.

It also generates a database creation script if it is also included.

How to migrate data by using Import and Export Data

As you install SQL Server, you also install a tool called Import and Export Data. You can use it to migrate data from a table to another server.

The advantage of this tool migration is that data can also be migrated to older versions of SQL Server. It is a little difficult to use, but it can be migrated at the level of several minutes even with about 1 million data, so it is also suitable for large data migration. Note, however, that the source and destination servers must be connected on a private network and cannot be used between servers with completely different networks.

precondition

Build SQL Server authentication or Windows authentication so that you can access each SQL Server from a PC that uses this tool.

This Tips uses this tool from the server from which it was migrated.

procedure

Select Import and Export Data from the Start menu. It is included in a folder in SQL Server, but the folder name varies depending on the version of SQL Server, so look for it in a timely manner.

Click Next.

Select the database from which you want to migrate. This time it is running on the source server, so you can pass through Windows authentication.

Next, set the connection settings for the destination database. Sql Server authentication is being used because it is on a different server.

Check "Copy data from one or more tables or views" and click the next button.

Select the table that you want to migrate. This list also shows the view, so don't check it.

If the source and destination table names are the same, the table to be migrated is automatically selected. It also automatically sets the mapping of column names if the column names are the same.

Here are some things to keep in mind:

• If the primary key is autonumered, the migration will fail if you do not check "Allow ID insertion" from "Edit mapping"
• If there are no tables to migrate to, a table is created
• If the destination table does not have the same column name, it will not be migrated. Do not continue to migrate or change the destination column.
• Records are added by default. If you want to replace it, delete the record at the destination in advance, or check "Delete rows in the destination table" from "Edit mapping"

In fact, you have to consider the above, but in this Tips, there is no data at the destination and there is no automatic numbering, so you can proceed without setting anything.

Make sure "Run Now" is checked and click "Next".

Click Done.

Data migration begins. Close when all migrations are complete.

If you look at the target database, you can verify that a table that did not exist has been created. You can also see that there are no more columns for existing tables.

You can verify that the data is being migrated. Note columns in table 2 are not mapped and have not been migrated.