英文:

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

问题 {#heading}

给定：

1. 从 Windows SQL Server 2022 备份，其中包含一个带有破折号的数据库名称

   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)

2. 尝试将其还原到 SQL Server 2022 的 Linux 安装，通过 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

这只会导致错误：

> Msg 3234, Level 16, State 2, Server 1cff31b461c0, Line 1
> 逻辑文件 'my-awesome-database.mdf' 不是数据库 'my-awesome-database' 的一部分。请使用 RESTORE FILELISTONLY 来列出逻辑文件名。
>
> Msg 3013, Level 16, State 1, Server 1cff31b461c0, Line 1
> RESTORE DATABASE 异常终止。

我确定这种方法对于没有破折号的数据库名称有效。在这种情况下，我可以采取什么措施？ 英文:

Given:

1. 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)

2. 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?

答案1 {#1}

得分: 1

文件的逻辑和物理名称与正在恢复的文件不同。

一般情况下，它们几乎相同，除了扩展名.mdf和.ldf。

但这并不是保证的：使用 RESTORE FILELISTONLY 命令来检查备份中包含什么。如果你要恢复到另一个数据库上，还要使用 SELECT name, physical_name FROM sys.master_files 命令来查找目标数据库当前的文件信息。 英文:

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