RESTORE DATABASE with dashes-in-its-name with sqlcmd

Question

Given:

Backup from Windows SQL Server 2022, containing a database with dashes in its name

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)

An attempt to restore it to the Linux installation of SQL Server 2022, via 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_ name is `my-awesome-database`, not `my-awesome-database.mdf`. Why does your command add the `.mdf` to the logical name? You only (arguably) need the file extension on the `TO` side of the command, since that's the only part that's about the _physical_ file. This doesn't have anything to do with the dashes. — Stuck at 1337, Aug 10 '23 at 16:01
Notice the error states *"Use RESTORE FILELISTONLY to list the **logical file names**."* (emphasis mine). The `LogicalName` is the first column in your data, and that's `my-awesome-database` and `my-awesome-database_log`. You'd get the same error if the database name's didn't have hyphens (`-`). — Thom A, Aug 10 '23 at 16:02
Just use `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'; GO`. It should work. — Mr. K, Aug 10 '23 at 16:04
@Stuckat1337, indeed. this helped, thanks. I remember using exactly this command with the .mdf/.ldf at the end successfully. Not sure though why this were happening. — snuk182, Aug 11 '23 at 08:12
Sure, that would work when someone put the extension into the logical names, e.g. `CREATE DATABASE dbname ON (name = 'dbname.mdf', …`. Which isn’t wrong, just not… uh, normal. The point is you have to use the exact logical names you get from FILELISTONLY, you can’t just guess or use what worked some other time. — Stuck at 1337, Aug 11 '23 at 11:07

score 1 · Accepted Answer · answered Aug 11 '23 at 11:10

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';

RESTORE DATABASE with dashes-in-its-name with sqlcmd

1 Answers1