bbtsoftware/docker-mssql-backup

Operating system error 5(Access is denied.)

ShrinkingUniverse opened this issue · 9 comments

ShrinkingUniverse commented

Hello everyone, could you please help me with the error 5, which I'm getting when backup is trying to create?

Backup started at 2022-08-19 01:00:01
Backup database $$$ to /backup/202208190100/202208190100.$$$.bak on sqlserver...
Msg 3201, Level 16, State 1, Server 5e56e03d94c6, Line 1
Cannot open backup device '/backup/202208190100/202208190100.$$$.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Server 5e56e03d94c6, Line 1
BACKUP DATABASE is terminating abnormally.
Error creating database backup
Backup of log skipped.
Copy backup files to target directory
find: ‘/backup/202208190100’: No such file or directory
find: ‘/backup/202208190100’: No such file or directory
Cleanup intermediate directory
find: ‘/backup/202208190100’: No such file or directory
Backup files cleanup is disabled

I tried to bring full rights to all users of my local machine, but it doesn't help. Does anyone faced with this issue? Thank you in advance

christianbumann commented

Hi @ShrinkingUniverse

With the information you provided I unfortunately cannot give you any help.
There are many questions

  1. Is the container running standalone, in a Docker swarm or in a Kubernetes cluster. I assume standalone as it runs on your local machine...
  2. How you start the container, with a simple command or with a docker-compose file? Do you have the command or the file?
  3. Where is the folder you want to save the backup. Is this a local folder, a remote folder, SMB Share, NFS share?
  4. On which OS you are running the container? Linux, Windows (yes this should be possible)?
  5. Under which user is the docker services / container running?

We are running this container in a Docker swarm using a docker-compose file and with a NFS share without any problem.

Hamsterman commented

I have the same issue as well. - Using SQL Server 2022

  1. Standalone docker
    2/3/5) I have tried different things this is the latest docker-compose
    mssql:
        container_name: MSSQL
        environment:
            - PUID=1000
            - PGID=1000
            - ACCEPT_EULA=Y
            - MSSQL_SA_PASSWORD=yourStrong(!)Password
        ports:
            - '1433:1433'
        volumes:
            - '//MYLOCALFOLDER/mssql/backup:/var/opt/mssql/backup'
            - '//MYLOCALFOLDER/mssql/data:/var/opt/mssql/data'
            - '//MYLOCALFOLDER/mssql/log:/var/opt/mssql/log'
        restart: unless-stopped
        image: mcr.microsoft.com/mssql/server:2022-latest
    mssqlbackup:
        container_name: MSSQL-backup
        image: bbtsoftwareag/mssql-backup
        volumes:
            - '//MYLOCALFOLDER/mssql/backup:/backup'
        environment:
            - PUID=1000
            - PGID=1000
            - TZ=Europe/Copenhagen
            - DB_SERVER=mssql
            - DB_USER=SA
            - DB_PASSWORD=yourStrong(!)Password
            - "DB_NAMES=
               Test"
            - CRON_SCHEDULE=0 1 * * *
  1. Ubuntu 22.04
christianbumann commented

@Hamsterman I’ll try to find some time next days to reproduce your issue.

In your docker-compose example you are mounting in both containers the backup folder to the same local folder. Maybee one container locks the directory… can you use another one for each container?
Do you need the backup path in the database container?

We are writing the files on a nfs share (mounted in the compose file) and we don‘t have any problem. The reason we do this is that we are running the containers in a Docker Swarm (not Kubernetes) and each worker should have access to the same path…

Hamsterman commented

In your docker-compose example you are mounting in both containers the backup folder to the same local folder. Maybee one container locks the directory… can you use another one for each container? Do you need the backup path in the database container?

From the readme

NOTE: The backup is written to a directory /backup inside the Microsoft SQL Server container, not to a volume in the backup container. For using the cleanup feature attach the same /backup volume in the bbtsoftwareag/mssql-backup container.

Hamsterman commented

There I made it work

Firstly the docker compose that will both create the msql and the backup container.

    mssql:
        container_name: MSSQL
        environment:
            - ACCEPT_EULA=Y
            - MSSQL_SA_PASSWORD=yourStrong(!)Password
        ports:
            - '1433:1433'
        volumes:
            - '//LOCALSTORAGE/mssql:/var/opt/mssql'
            - '//LOCALSTORAGE/mssql/backup:/backup'
        restart: unless-stopped
        image: mcr.microsoft.com/mssql/server:2022-latest
    mssqlbackup:
        container_name: MSSQL-backup
        image: bbtsoftwareag/mssql-backup
        volumes:
            - '//LOCALSTORAGE/mssql/backup:/backup'
        environment:
            - TZ=Europe/Copenhagen
            - DB_SERVER=mssql
            - DB_USER=SA
            - DB_PASSWORD=yourStrong(!)Password
            - "DB_NAMES=
               Test"
            - CRON_SCHEDULE=0 1 * * *

Then to fix a "bug" in mssql you have to fix the permissions.

chown -R 10001:10001 /LOCALSTORAGE/mssql/

It's important that you do that after the container is running.

pompushko commented

Hello

I have same situation :)
Unfortunately, chown -R 10001:10001 /var/opt/service/backup/ not helps me...

pupa@panel-1:~/service$ ls -la /var/opt/service/
total 24
drwxr-xr-x 6 pupa  pupa  4096 Mar  8 20:03 .
drwxr-xr-x 3 root  root  4096 Mar  4 17:49 ..
drwxrwxr-x 2 10001 10001 4096 May  6 14:20 backup
drwxr-xr-x 6 10001 10001 4096 Mar  7 16:06 mssql

pupa@panel-1:/var/opt/service$ docker logs --since=24h 0fb5c56b4390
Backup started at 2024-05-06 01:00:01
Backup database MyDB to /backup/202405060100.MyDB .bak on sql...
Msg 3201, Level 16, State 1, Server 4ea7f81e7583, Line 1
Cannot open backup device '/backup/202405060100.MyDB.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Server 4ea7f81e7583, Line 1
BACKUP DATABASE is terminating abnormally.
Error creating database backup
Backup log of MyDB to /backup/202405060100.MyDB.trn on sql...
Msg 4214, Level 16, State 1, Server 4ea7f81e7583, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Server 4ea7f81e7583, Line 1
BACKUP LOG is terminating abnormally.
Error creating log backup
Backup files cleanup is disabled
Backup process finished at 2024-05-06 01:00:03

  backup:
    image: bbtsoftwareag/mssql-backup
    volumes:
      - /var/opt/service/backup:/backup
    environment:
      - TZ=Europe/Zurich
      - DB_SERVER=sql
      - DB_USER=sa
      - DB_PASSWORD=bloablaobababba
      - "DB_NAMES=
          MyDB"
      - CRON_SCHEDULE=0 1 * * *
    networks:
      network-name:
        aliases:
          - backup

Also, if I will login into the container, I can create any files in that directory :)

root@0fb5c56b4390:/backup# touch test
root@0fb5c56b4390:/backup# ls -la
total 8
drwxrwxr-x 2 10001 10001 4096 May  6 14:26 .
drwxr-xr-x 1 root  root  4096 Mar 22 22:51 ..
-rw-r--r-- 1 root  root     0 May  6 14:26 test

pupa@panel-1:/var/opt/service/backup$ ls -la
total 8
drwxrwxr-x 2 10001 10001 4096 May  6 14:26 .
drwxr-xr-x 6 pupa  pupa  4096 Mar  8 20:03 ..
-rw-r--r-- 1 root  root     0 May  6 14:26 test