Given:
1> RESTORE filelistonly FROM DISK = '/tmp/my-awesome-database_FULL_20230810_000209.bak';
2> go
LogicalName PhysicalName Type FileGroupName Size MaxSize FileId CreateLSN DropLSN UniqueId ReadOnlyLSN ReadWriteLSN BackupSizeInBytes SourceBlockSize FileGroupId LogGroupGUID DifferentialBaseLSN DifferentialBaseGUID IsReadOnly IsPresent TDEThumbprint SnapshotUrl
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- --------------------------- --------------------------- ------------------------------------ --------------------------- --------------------------- -------------------- --------------- ----------- ------------------------------------ --------------------------- ------------------------------------ ---------- --------- ------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
my-awesome-database E:\SQLAWESOME\MDF\my-awesome-database.mdf D PRIMARY 61547216896 35184372080640 1 0 0 XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX 0 0 61498261504 512 1 NULL 1234567890123 YYYYYYYY-YYYY-YYYY-YYYY-YYYYYYYYYYYY 0 1 NULL NULL
my-awesome-database_log F:\SQLAWESOME\LDF\my-awesome-database_log.ldf L NULL 11282677760 2199023255552 2 0 0 ZZZZZZZZ-ZZZZ-ZZZZ-ZZZZ-ZZZZZZZZZZZZ 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1 NULL NULL
(2 rows affected)
sqlcmd:1> RESTORE DATABASE [my-awesome-database] FROM DISK = '/tmp/my-awesome-database_FULL_20230810_000209.bak' WITH REPLACE, MOVE 'my-awesome-database.mdf' TO '/var/opt/mssql/data/my-awesome-database.mdf', MOVE 'my-awesome-database_log.ldf' TO '/var/opt/mssql/data/my-awesome-database_log.ldf';
2> go
This only results in errors:
Msg 3234, Level 16, State 2, Server 1cff31b461c0, Line 1
Logical file 'my-awesome-database.mdf' is not part of database 'my-awesome-database'. Use RESTORE FILELISTONLY to list the logical file names.Msg 3013, Level 16, State 1, Server 1cff31b461c0, Line 1
RESTORE DATABASE is terminating abnormally.
I know for sure that this approach works for database names with no dashes. Is there anything I can do with this case?
The logical and physical file names of the files you are restoring are not the same.
At a guess, normally they are almost the same except for the extension .mdf and .ldf.
But that is not guaranteed: use RESTORE FILELISTONLY to check what's on the backup. If you are restoring over another database then also use use SELECT name, physical_name FROM sys.master_files to find out what the database has currently.
RESTORE DATABASE [my-awesome-database]
FROM DISK = '/tmp/my-awesome-database_FULL_20230810_000209.bak'
WITH REPLACE,
MOVE 'my-awesome-database' TO '/var/opt/mssql/data/my-awesome-database.mdf',
MOVE 'my-awesome-database_log' TO '/var/opt/mssql/data/my-awesome-database_log.ldf';