Migrating SQL Server from Windows to Linux

Previous blog about SQL Server on Linux installation, address: http://www.cnblogs.com/fishparadise/p/8057650.html, Now the test migrates a database under the Windows platform to the Linux platform.

1. Environment

Windows:microsoft SQL Server R2 (SP3)-10.50.6000.34 (X64)
Linux:centos 7.4,sql Server (RC2)-14.0.900.75 (X64)

2. Create a backup under Windows

Full backup database using SSMS under Windows

BACKUP DATABASE  to DISK = ' D:\sql_bak\db1\db1.bak '  with  ='full_backup'=1GO

Code-1: Backing Up the database

3. Copy the backup file to Liunx

Copy the backup files to Linux, such as under/tmp/. can use FTP, or RZ and other tools.

4. Restore the database under Linux

Use a terminal (such as Xshell) to connect to Linux. Default data file directory under/var/opt/mssql/data/, if you want to specify a data directory, you can create a custom directory and give the appropriate permissions.

mkdir -p/data/mssql_datachown -R mssql:mssql/data/mssql_data

Code-2: Creating a directory and modifying permissions

Connect to a database using sqlcmd

Sqlcmd-s localhost-u SA

Code-3: Connecting to a Database

RESTORE DATABASEDb1 from DISK = '/tmp/db1.bak' withMOVE'DB1'  to '/data/mssql_data/db1.mdf', MOVE'Db1_log'  to '/data/mssql_data/db1_log.ldf', STATS= 1,REPLACE, RECOVERYGO

Code-4: Restoring a Database

Note: You cannot copy and paste the entire multiline command at once, and you can only copy each line after you press the ENTER key. Finally, go to the end, enter after the execution.

Of course, you can also write the command that restores the database above to a file, and then use SQLCMD to execute the script to restore the database.

' /data/mssql_data/restore_db.txt '

Code-5: Execute script file to restore database

5. Query validation

sqlcmd query under Linux:

Figure-1: Query under sqlcmd

Figure-2: Properties of the DB1 database under SSMs (Files)

In the Windows SSMS connection to the database under Linux, it was found that the path to the database file was not a forward slash of Linux but a backslash for Windows, and compatibility level could not be modified.

Figure-2: Properties of DB1 Database under SSMs (Options)

Figure-2: Query under SSMs

6. Reference

Https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-migrate-restore-database

Migrating SQL Server from Windows to Linux