It may be a very easy task for SQL DBAs to restore a database backup on a SQL Instance which is running on Windows server. With the launch of SQL Server on Linux, It may not be as easy as it is with Windows, If you don’t have knowledge of Linux operating system.
In this blog post, we are going to learn how to restore a database backup on SQL Linux Instance using T-SQL script. We have already downloaded the new sample database backup file ‘WideWorldImporters-Full.bak’ in my previous post. Now, I shall be using the same file to restore.
Steps to restore a SQL database backup on SQL Linux instance
- Connect to your SQL Server Linux Instance
- Verify the current location of data and log files on the SQL Linux Instance
Syntax: SELECT * FROM SYS.sysaltfiles
- Go to the location where you have downloaded the new sample database backup file
Syntax: pwd (This command tells your present working directory)
Syntax: ls -l (This command tells how many files are in the current directory)
It means the backup file location is ‘/home/sachit/WideWorldImporters-Full.bak’
- To get the logical name of the data and log files, kindly check the file list detail of the backup file
Syntax: RESTORE FILELISTONLY FROM DISK = ‘/home/sachit/WideWorldImporters-Full.bak’
- Now, restore the sample database backup on SQL Linux Instance
Syntax:
USE [master]
RESTORE DATABASE [WideWorldImporters] FROM DISK = N’/home/sachit/WideWorldImporters-Full.bak’
WITH FILE = 1, MOVE N’WWI_Primary’
TO N’/var/opt/mssql/data/WideWorldImporters.mdf’,
MOVE N’WWI_UserData’ TO N’/var/opt/mssql/data/WideWorldImporters_UserData.ndf’,
MOVE N’WWI_Log’ TO N’/var/opt/mssql/data/WideWorldImporters.ldf’,
MOVE N’WWI_InMemory_Data_1′ TO N’c:\var\opt\mssql\data\WideWorldImporters_InMemory_Data_1′,
–In Public Preview vNext CTP1, In-Memory OLTP database need to have the “C:\” notation when referred
NOUNLOAD, STATS = 5
GO
You have restored a SQL Server database backup on the SQL Linux Instance successfully.
If you follow the database restoration script, I have highlighted one of the limitations of the public preview. So, when you are restoring a database backup on SQL Linux Instance and the backup contains In-memory OLTP information, you need to follow the provided workaround to restore the backup.
Please leave a comment, if you are facing any issue with restoration.
One thought on “Restore SQL Server Database Backup on SQL Linux Instance”
On Red Hat Enterprise Linux and MSSQL Server 2017:
[user@dev-mssql02 opt]$ sqlcmd -S localhost -U SA
Password:
1> RESTORE DATABASE db
2> FROM DISK = ‘/var/opt/mssql/db_backup.bak’
3> WITH MOVE ‘DB’ TO ‘/var/opt/mssql/db.mdf’,
4> MOVE ‘DOCUMENTS’ TO ‘/var/opt/mssql/documents.mdf’,
5> MOVE ‘DB_LOG’ TO ‘/var/opt/mssql/db_log.lfd’
6> GO
Msg 5149, Level 16, State 3, Server dev-mssql0, Line 1
MODIFY FILE encountered operating system error 31(A device attached to the system is not functioning.) while attempting to expand the physical file ‘/var/opt/mssql/dev.mdf’.
Msg 3013, Level 16, State 1, Server dev-mssql0, Line 1
RESTORE DATABASE is terminating abnormally.