Backup SQL Server 2019 in a Docker Linux container fails

Question

Attempted multiple ways to do the backup.

Tried adding SA as a root user in the container

Azure Data studio

BACKUP DATABASE [PrestigeCars] TO  DISK = N'/var/opt/mssql/backup//PrestigeCars-202044-8-4-52.bak' WITH NOFORMAT, NOINIT,  NAME = N'PrestigeCars--2020-04-04T12:04:52', NOSKIP, REWIND, NOUNLOAD,  STATS = 10

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '/var/opt/mssql/backup//PrestigeCars-202044-8-4-52.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Total execution time: 00:00:00.217

SSMS

backup database [PrestigeCars]
to  disk = N'/var/opt/mssql/backup//PrestigeCars-202044-6-42-9.bak'
with noformat
    , noinit
    , name = N'PrestigeCars--20200404'
    , noskip
    , rewind
    , nounload
    , compression
    , stats = 10;

SQLCMD

sqlcmd -S localhost,12001 -U SA -Q "BACKUP DATABASE [PrestigeCars] TO DISK = N'/var/opt/mssql/backup/CSCI331-Backup/PrestigeCars-202044-6-42-9.bak' WITH NOFORMAT, NOINIT, NAME = 'PrestigeCars-20200404', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

The error is telling you the problem. Look at your file path: `backup//PrestigeCars` Notice the **two** `/` characters. — Thom A, Apr 04 '20 at 12:33

score 2 · Answer 1 · answered Apr 05 '20 at 17:40

Thanks to all of those of you that have helped. The issue was not missing folders but a permissions issue.

The folders were initially create when copying backups to the container:

docker cp /Users/YourUsername/CSCI331-Backup/TSQLV4.bak linux-sql2k19:/var/opt/mssql/backup/

*The permissions were 4 drwxr-xr-**x 3 root root 4096 Jan 31 19:06 backup***

I tried various Ubuntu create sudo adduser but none of the commands sudo or apt-get worked. (https://help.ubuntu.com/community/FilePermissions)

I found this command to connect as the root user

docker exec -it -u root 874 bash

cd /var/opt/mssql/backup/

cd ..

chmod 777 backup

cd ..

chmod 777 mssql

cd ..

chmod 777 opt

cd ..

chmod 777 var

Close the container. I backed up databases in SSMS and Azure Data Studio. Double Yeah!

Dan Guzman · Answer 2 · 2020-04-04T12:57:31.203

0

Directory /var/opt/mssql/backup does not exist in standard SQL Server Linux images. You'll need to first create the directory by running the following command in the container:

mkdir /var/opt/mssql/backup

Also, as @Larnu pointed out, you have an extra backslash in the path. The backup command should be:

BACKUP DATABASE [PrestigeCars] TO  DISK = N'/var/opt/mssql/backup/PrestigeCars-202044-8-4-52.bak' WITH NOFORMAT, NOINIT,  NAME = N'PrestigeCars--2020-04-04T12:04:52', NOSKIP, REWIND, NOUNLOAD,  STATS = 10;

edited Apr 04 '20 at 12:57

answered Apr 04 '20 at 12:45

Dan Guzman

43,250
3
46
71

@Larnu, the default user is `mssql` with `docker exec -it sql-container-name bash`. So the `mkdir` command creates the directory with `drwxr-xr-x`, same as the other sql directories. – Dan Guzman Apr 04 '20 at 13:12
Fair enough, I've personally not used `docker` for SQL Server, just actual Linux installs and LXD, so wasn't sure and why I made the comment. – Thom A Apr 04 '20 at 13:25
1

First, the directory does exist. It was created using a docker copy – Peter H Apr 05 '20 at 16:13
@PeterH, so did the backup work once you removed the extraneous slash? – Dan Guzman Apr 05 '20 at 16:52

Backup SQL Server 2019 in a Docker Linux container fails

2 Answers2