CODEWITHSUNDEEP
sql-serverdockerssmssqlcmdazure-data-studio
Peter H
Peter H

Reputation: 109

Backup SQL Server 2019 in a Docker Linux container fails

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"

Upvotes: 1

Views: 2178

Answers (2)

Peter H
Peter H

Reputation: 109

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!

Upvotes: 2

Dan Guzman
Dan Guzman

Reputation: 46231

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;

Upvotes: 0

Related Questions