提问人:snuk182 提问时间:8/10/2023 最后编辑:marc_ssnuk182 更新时间:8/11/2023 访问量:66
使用 sqlcmd 的破折号在 its-name 中还原数据库
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
这只会导致错误:
消息 3234,级别 16,状态 2,服务器 1cff31b461c0,第 1
行逻辑文件“my-awesome-database.mdf”不是数据库“my-awesome-database”的一部分。使用 RESTORE FILELISTONLY 列出逻辑文件名。消息 3013,级别 16,状态 1,服务器 1cff31b461c0,第 1
行 RESTORE DATABASE 异常终止。
我确信这种方法适用于没有破折号的数据库名称。对于这个案子,我能做些什么吗?
答:
1赞
Charlieface
8/11/2023
#1
要还原的文件的逻辑文件名和物理文件名不同。
据猜测,通常它们几乎相同,除了扩展名和 ..mdf
.ldf
但这并不能保证:用于检查备份中的内容。如果要在另一个数据库上还原,则还要使用 use 来查找数据库当前具有的内容。RESTORE FILELISTONLY
SELECT name, physical_name FROM sys.master_files
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';
评论
my-awesome-database
my-awesome-database.mdf
.mdf
TO
LogicalName
my-awesome-database
my-awesome-database_log
-
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
CREATE DATABASE dbname ON (name = 'dbname.mdf', …