Monday, November 28, 2016

SSMS & Linux - Use the Wizard to RESTORE Database

You can use SQL Server Management Studio (SSMS) to RESTORE a backup file to your Linux based SQL Server installation.  While the most control comes from T-SQL commands, we want to know just how far we can use the SSMS GUI and its related wizards.

Once connected, our Locate Backup File pop-up takes us to the C:\var\opt\mssql\data folder. Interestingly, on our Linux machine, the folder is simply addressed as:  /var/opt/mssql/data. No leading C:\, and the slashes are reversed.  The wizard pop-up looks like this:

For this preview release, the location wizard limits our access to various files and folders. It's not good form, but we'll move the file to the /data/ folder.

Copy / Move

Here we move our backup file to the folder where SSMS is expecting it to be on our Linux machine. For production, you really will not do this, but just so we can see our backup file display in our wizard, we'll place a copy of the backup file in the default data folder used by SSMS.

Using a terminal, use the copy command cp, to move the file:

But my Backup file is still not visible in the wizard!

Permissions.  If you drill down into the folders in Linux, we found that the files already present in the /data/ folder are owned by the user mssql.  Our recently copied backup file is NOT owned by mssql, and it not accessible to other users. So, our wizard cannot see the file. Options:

  1. Permissions.  Once we changed access permissions on our backup file, our wizard could access the backup file.
  2. Don't use the folder: /var/opt/mssql/data. This folder holds the live database files (.mdf, .ldf, etc) so it's best to create a new folder to hold your backup files. The location wizard will let you type in a different folder directory.

Change Permissions on Test.bak

If you need to change permissions on the file, you most likely will have to start the file viewer "Nautilus" as a root user.  In a terminal, 

  1. sudo su ( and enter root password)
  2. nautilus

This brings up the file viewer as root.  Navigate to /var/opt/mssql/data, and then adjust the file permissions:

Once done, you should be able to see your backup file in the Location wizard.

No comments: