英文:
RESTORE DATABASE with dashes-in-its-name with sqlcmd
问题
给定：
- 从 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)
- 尝试将其还原到 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:
- 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?
答案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';
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论