SSMS restore database error 31 in docker

[DrRee]

### SSMS restore database error 31

I have issue with restore backup from database inside SSMS. In SSMS, I select Database -> restore database -> Device -> Select backup devices -> Add -> bb.bak after that I click on added file into list and I want to see "Contents" error bellow "Error" and if I go next I'm not able to restore my database. SSMS version: 17.9

### Error:

TITLE: Microsoft.SqlServer.Smo

System.Data.SqlClient.SqlError: Read on "/var/opt/mssql/backup/back.bak" failed: 31(A device attached to the system is not functioning.)

For help, click: http://go.microsoft.com/fwlinkProdName=Microsoft+SQL+Server&ProdVer=14.0.17285.0((SSMS_Rel_17_4).180821-0238)&LinkId=20476

I have tried to solve this error with full rights on whole path to backup dir inside the docker but it didn't help. After ls -la /var/opt/mssql/backup everything was setup to 777 (see output below). My docker-compose.yml see below:

version: "3.1"
services:
  db:
    image: "microsoft/mssql-server-linux"
    container_name: mssql-server
    environment:
      SA_PASSWORD: "123456Aa+"
      ACCEPT_EULA: "Y"
      MSSQL_PID: "Developer"
    volumes:
     - ./database/backup:/var/opt/mssql/backup
    command: >
      bash -c "chmod -R 777 /var/opt/mssql/backup/* && /opt/mssql/bin/sqlservr" 
    ports:
     - "192.168.99.100:6000:1433"

### ls -la /var/opt/mssql/backup

mssql-server | total 6900
mssql-server | drwxrwxrwx 1 1000 staff       0 Nov  8 12:06 .
mssql-server | drwxr-xr-x 3 root root     4096 Nov  8 15:25 ..
mssql-server | -rwxrwxrwx 1 1000 staff    2413 Nov  8 12:06 back.sql
mssql-server | -rwxrwxrwx 1 1000 staff 7056896 Nov  8 12:06 back.bak

[Pirlo-Zhang]

@DrRee

Sorry, I can not reproduce this issue on my environment. (copy AdventureWorks database from host to container path:/temp)

ls -la /temp

-rwxr-xr-x 1 root root 48749568

Please make sure the sql server account connected to SQL database instance has enough permission to read the file.

Also have a check:

Restore a SQL Server database in a Linux Docker container

[AdamAnSubtractM]

I also have this issue, were you able to solve it?

[nocentino]

Perhaps this post could help. I’ve seen this issue when the database being restored is larger than the available space in Docker.

https://danielssilva.dev/2019-09-09-MODIFY-FILE-encountered-operating-system-error-31(A-device-attached-to-the-system-is-not-functioning.)-while-attempting-to-expand-the-physical-file/

[mausch]

Link above is dead now. It's on archive.org though: https://web.archive.org/web/20201212000307/https://www.danielssilva.dev/2019-09-09-MODIFY-FILE-encountered-operating-system-error-31(A-device-attached-to-the-system-is-not-functioning.)-while-attempting-to-expand-the-physical-file/

Dear mssql team: please improve your error messages. I'm constantly having to guess what's going on because error messages are too vague.

[tracker1]

Double-check the CASE of your file itself and that it matches... I noticed these types of issues when going from a case-insensitive environment to a case-sensitive one. It seems to match in your description.

Beyond this, double-check the volume mount for SQL Server instance...

I can say that I have run backup/restore commands in/out of docker without issue in the past.... I did it a lot for testing scenarios.

[JoshMart]

For me, I was getting this error when Docker was running out of space. My environment: WSL2 Ubuntu on Windows 11 with Docker Desktop 4.15.0 and no extra configuration files.

I could restore a 16 and 67 GB on-disk backup; but when I tried to restore a 77 GB on-disk backup I would get 31(A device attached to the system is not functioning.).

I tried to follow Microsoft's Expand the size of your WSL 2 Virtual Hard Disk guide, but this guide doesn't expand the correct VHD for our Docker problems. Using this SO answer to correctly find the Docker VHD %LOCALAPPDATA%\Docker\wsl\data\<disk name>.vhdx I was able to follow the Microsoft guide, increase the correct VHD, and restore my largest backup.

Hopefully this helps someone since we're not getting Docker specific errors back and have to dig a little bit into what's going on.

[Broderick890]

same issue to me

[lechediaz]

Intenta eliminando archivos con extensión .bak dejados dentro del contenedor:

docker exec -it "$CONTAINER_NAME" rm "/var/opt/mssql/backup/$BACKUP_FILE"

Donde $CONTAINER_NAME es una variable de ambiente cuyo valor es el nombre del contenedor con SQL Server y $BACKUP_FILE contiene el nombre de tu archivo backup.

En tu caso sería:

docker exec -it "mssql-server" rm "/var/opt/mssql/backup/back.bak"

--- English

Try deleting files with .bak extension let inside container:

docker exec -it "$CONTAINER_NAME" rm "/var/opt/mssql/backup/$BACKUP_FILE"

Where $CONTAINER_NAME is an environment variable with the container name with SQL Server as value and $BACKUP_FILE has the backup file name.

In your case:

docker exec -it "mssql-server" rm "/var/opt/mssql/backup/back.bak"

[birgersp]

Just wanted to chime in that I had the exact same problem (error message) when trying to restore the db.

I solved it by allowing docker to use more disk space. I configured this in the docker desktop dashboard

Another thing I did while trying to solve the error message (idk if it is relevant but it might help others) was to ensure that the .bak files I were trying to restore were actually readable by the mssql user that is the default user in the docker image.