How to find physical path for SQL Server backup on Container

Question: I'm migrating some SQL Server databases to Openshift Containers. The SQL Server is set up with persistent disk , with a dedicated persistent disk partition for the SQL Server defaultbackup directory. I don't have access to the underlying files via command line and can only use command line. How can I get the physical disk device , which will then allow me to create a RESTORE DATABASE statement pointing to the device?

Answer: Assuming you've taken the BACKUP DATABASE with at at least one backup device  , such as this example , which backups to the default backup directory - e.g 

BACKUP DATABASE [test123] TO DISK = N'./mssql-test/test/FULL/mssql-test_test123_FULL_20201117_082318.bak' WITH CHECKSUM, NO_COMPRESSION;

and the file has not been moved - then you should be able to restore . A quick method to find out the physical device name is to query the backupmediafamily table.

Whenever a BACKUP DATABASE command is executed , SQL Server creates audit records with various meta information . 

This query  returns various details about the backup , including the physical device name.

SELECT bus.server_name as 'server',
bus.database_name as 'database',
bume.physical_device_name,
CAST(bus.backup_size /1024/1024/1024 AS DECIMAL(10,2)) as 'buSize_GB',
CAST(DATEDIFF(ss, bus.backup_start_date,bus.backup_finish_date) AS VARCHAR(4)) as 'buDuration_Sec',
bus.backup_start_date as 'buDateStart',
CAST(bus.first_lsn AS VARCHAR(50)) as LSN_First,
CAST(bus.last_lsn AS VARCHAR(50)) as LSN_Last,
CASE bus.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
WHEN 'F' THEN 'File\FileGroup'
WHEN 'G' THEN 'Differential File'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential Partial'
END AS buType,
bus.begins_log_chain ,
bus.differential_base_lsn,
bus.recovery_model,
is_snapshot
FROM msdb.dbo.backupset bus
INNER JOIN msdb.dbo.backupmediafamily bume ON bus.media_set_id = bume.media_set_id
WHERE bus.database_name =  
ORDER BY bus.backup_start_date DESC, bus.backup_finish_date DESC
GO

Use the physical device name to construct the RESTORE DATABASE statement e.g 

USE [master]
RESTORE DATABASE [test] FROM  
DISK = N'/var/opt/mssql/mpbkp/mssql-test/test123/FULL/mssql-test_test123_FULL_20201110_133801.bak' 
WITH  FILE = 1,  MOVE N'test' TO N'/var/opt/mssql/mpdata/test.mdf',  
MOVE N'test_log' TO N'/var/opt/mssql/mplog/test_log.ldf',  
NOUNLOAD,  STATS = 5

The obvious problem is if a user or process has removed the file. Typically the error message is like:

Msg 3201, Level 16, State 2, Line 3
Cannot open backup device '/var/opt/mssql/mpbkp/mssql-test/test123/FULL/mssql-test_test123_FULL_20201110_133801.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

In that case you'll need to review the processes managing files, access to files, retention policies and any other factors which could impact these files

For Docker container commands read SQL Server Docker and Container Cheat Sheet