[Others]Migrate database to SQL on Linux Docker

Introduction
In the previous article, Rookie found out how to keep the database file in the SQL on Linux Docker container after the container is destroyed, and Laruence was quite happy. So today, Laruence raised a new question: "Rookie, what should I do if I want to move my database from SQL on Windows to SQL on Linux Docker?"
Upon hearing the question, Rookie quickly offered two methods for database migration: backup and restoration, and detach and attach. Let's give it a try.

Build an SQL on Windows test database
To test the two methods for migrating databases from SQL on Windows to SQL on Linux Docker: restoration from backup, and database attach, we created two test databases in the SQL on Windows database instance: TestAttach and TestBackRestore. Then we created a test table under each of the two databases, inserted two pieces of NEWID test data, and finally viewed the two pieces of data.
IF DB_ID('TestAttach') IS NULL
    CREATE DATABASE TestAttach;
GO

USE TestAttach
GO
IF OBJECT_ID('dbo.tb_Test', 'U') IS NOT NULL
    DROP TABLE dbo.tb_Test
GO

CREATE TABLE dbo.tb_Test(
RowID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
,Name VARCHAR(36) NOT NULL
);

INSERT INTO dbo.tb_Test
SELECT NEWID() UNION ALL SELECT NEWID();

SELECT * FROM dbo.tb_Test;

IF DB_ID('TestBackRestore') IS NULL
    CREATE DATABASE TestBackRestore;
GO

USE TestBackRestore
GO
IF OBJECT_ID('dbo.tb_Test', 'U') IS NOT NULL
    DROP TABLE dbo.tb_Test
GO

CREATE TABLE dbo.tb_Test(
RowID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
,Name VARCHAR(36) NOT NULL
);

INSERT INTO dbo.tb_Test
SELECT NEWID() UNION ALL SELECT NEWID();

SELECT * FROM dbo.tb_Test;

SELECT physical_name,* FROM sys.master_files
WHERE database_id = DB_ID('TestAttach');
The execution result is shown in the screenshot below:

In order to copy mdf and ldf files directly, we need to move the TestAttach database offline, and then back up the TestBackRestore database.
USE master
GO
ALTER DATABASE TestAttach SET OFFLINE;

USE master
GO
EXEC xp_create_subdir 'C:\temp\'
GO
BACKUP DATABASE [TestBackRestore]
    TO DISK = 'C:\temp\TestBackRestore_full_20170210.bak' WITH STATS = 2 ;

Transmit database files
Since I carried out the test in a Windows virtual machine on a Mac system, first I need to copy the files in the Windows virtual machine to the Mac. For example: I copied the following three files to the Mac's ~/Downloads directory:
C:\SQL2K14_DATA\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestAttach.mdf
C:\SQL2K14_DATA\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestAttach_log.ldf
C:\temp\TestBackRestore_full_20170210.bak
Next, I need to copy these three files to the SQL on Linux Docker container in Mac, as follows:
docker cp ~/Downloads/TestAttach.mdf linuxsql41433:/var/opt/mssql/data/TestAttach.mdf
docker cp ~/Downloads/TestAttach_log.ldf linuxsql41433:/var/opt/mssql/data/TestAttach_log.ldf
docker cp ~/Downloads/TestBackRestore_full_20170210.bak linuxsql41433:/var/opt/mssql/data/TestBackRestore_full_20170210.bak
After the backup file, the database data file, and the log file are ready, it is time to restore one database and attach the other database.

Restore the database to SQL on Linux Docker
This operation is the same as the restoration operation for SQL on Windows. First check the integrity of the backup file, and then use the Restore statement to recover the database. Then connect to SQL on Linux Docker using SSMS and execute the following script:
USE master
GO
--Verify backup file
RESTORE FILELISTONLY
    FROM DISK = N'C:\var\opt\mssql\data\TestBackRestore_full_20170210.bak';
RESTORE VERIFYONLY
    FROM DISK = N'C:\var\opt\mssql\data\TestBackRestore_full_20170210.bak';
-- Restore Database
USE master
GO
RESTORE DATABASE [TestBackRestore]
FROM DISK = N'C:\var\opt\mssql\data\TestBackRestore_full_20170210.bak'
WITH MOVE N'TestBackRestore' TO N'C:\var\opt\mssql\data\TestBackRestore.mdf'
    ,MOVE N'TestBackRestore_log' TO N'C:\var\opt\mssql\data\TestBackRestore_log.ldf'
    ,STATS=2
GO
The last step is the operation to attach the database to SQL on Linux Docker.

Attach the database to SQL on Linux Docker
The method for attaching the database is also the same as that for SQL on Windows - using the Create Database for Attach method. Similarly, connect to SQL on Linux Docker using SSMS and execute the following database attaching script:
USE master
GO
-- Create database via attach
CREATE DATABASE [TestAttach]
    ON ( FILENAME = N'C:\var\opt\mssql\data\TestAttach.mdf'),
    ( FILENAME = N'C:\var\opt\mssql\data\TestAttach_log.ldf')
    FOR ATTACH;
GO

Final confirmation
In the final confirmation, you only need to check whether the data in the test tables in the two databases is consistent with that before the migration.
SELECT * FROM TestAttach.dbo.tb_Test;
SELECT * FROM TestBackRestore.dbo.tb_Test;
Compare the data in the database test tables before and after the migration for consistency:

We can see from the final confirmation results that the database test data before and after the migration is exactly the same, so both methods used for this migration were successful and reliable.

Closing
The SQL on Windows database can be smoothly migrated to SQL on Linux Docker, so feel free to choose between Windows and Linux databases without having to worry about migration. We can use the database backup-restore and detach-attach methods to migrate databases.

@ysera, Helpful post...

@ysera, Very informative.