The main disadvantage of this method is the large file size, and a long time required to create, and especially to import, data. However, as in the case with BACPAC, when exporting, it is desirable to create a snapshot to export data in a consistent state. sql file because before importing it, you can view it and change something if necessary.
#Adventureworks2012 read only windows
sqlcmd -U SA -I AdventureWorks.sqlĬreating a script on a Windows server and then executing it on a Linux server is a good solution.
sql file, you can use sqlcmd utility by submitting a file with a script using the -i parameter. mdf files with those that are supported in Linux: Here, in addition to specifying the Output file, you will need to specify both Schema and Data as types of data to submit. This is similar to what mysqldump or pg_dump does. Problems can arise if the source and target SQL server have different versions.Īnother way to move a database from a Windows server to a Linux server is to create a T-SQL script. Executing BACKUP DATABASE and RESTORE DATABASE commands are a good way to move a database from Windows to Linux. You can execute a T-SQL query on a Linux server using sqlcmd utility or SSMS if remote connections are allowed. MOVE 'AdventureWorks2017_log' TO '/var/opt/mssql/log/AdventureWorks.ldf' WITH MOVE 'AdventureWorks2017' TO '/var/opt/mssql/data/AdventureWorks.mdf', RESTORE DATABASE įROM DISK = '/home/username/AdventureWorks.bak' We learned the names of these files during the previous step. You will need to specify the path in RESTORE DATABASE command not only to the location of the copied backup but also to the files in which the database will be physically stored. First, you need to copy the backup file to the Linux server, and then you can restore the database. d ON d.database_id = f.database_idĪfter you have made a backup and found out the database file names, it’s time to restore it on Linux. You can also get the database file names through the following T-SQL query: SELECTįROM. In the window that appears, select “Files,” and the Logical Name column will contain a list of database file names. These can be viewed in SSMS by right-clicking on the database and selecting “Properties.” We also need to know the logical names of database files to restore a database on a Linux server. Using T-SQL’s BACKUP DATABASE and RESTORE DATABASE commandsįor this first method, you’ll create a native backup and facilitate its restoration to an SQL Server located on the Linux server.įirst, run BACKUP DATABASE command on the Windows server: BACKUP DATABASE TO DISK = 'c:\Temp\AdventureWorks.bak'
This can come in handy, for example, if you wanted to provide developers with a database for testing without buying a Windows Server license. Now that Microsoft has released SQL Server for Linux, even if your main tech stack is tied to a Windows Server you may want to copy the database to another SQL Server located on a Linux computer.