Migrating SQL Server from Windows to Linux

Source: Internet
Author: User
Tags microsoft sql server mssql

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.