如何从备份文件中检索数据库的逻辑文件名 [英] How can I retrieve the logical file name of the database from backup file

问题描述

我一直在研究如何

I was looking into the steps of how to Restore Database Backup using SQL Script (T-SQL). Here are the steps:

数据库YourDB具有完整备份YourBackUpFile.bak.可以使用以下两个步骤将其还原:

Database YourDB has full backup YourBackUpFile.bak. It can be restored using following two steps:

步骤1:从备份中检索数据库的逻辑文件名.

Step 1: Retrieve the logical file name of the database from the backup.

RESTORE FILELISTONLY
FROM DISK = 'D:BackUpYourBackUpFile.bak'
GO

步骤2:在接下来的步骤中使用LogicalName列中的值.

Step 2: Use the values in the LogicalName column in the following step.

----Make Database to single user Mode
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore Database
RESTORE DATABASE YourDB
FROM DISK = 'D:BackUpYourBackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'

我只是对如何获取YourMDFLogicalName和YourLDFLogicalName有疑问. 有人可以帮我吗?

I am just having problem on how to get the YourMDFLogicalName and YourLDFLogicalName. Can any one help me with that?

推荐答案

DECLARE @Table TABLE (LogicalName varchar(128),[PhysicalName] varchar(128), [Type] varchar, [FileGroupName] varchar(128), [Size] varchar(128), 
            [MaxSize] varchar(128), [FileId]varchar(128), [CreateLSN]varchar(128), [DropLSN]varchar(128), [UniqueId]varchar(128), [ReadOnlyLSN]varchar(128), [ReadWriteLSN]varchar(128), 
            [BackupSizeInBytes]varchar(128), [SourceBlockSize]varchar(128), [FileGroupId]varchar(128), [LogGroupGUID]varchar(128), [DifferentialBaseLSN]varchar(128), [DifferentialBaseGUID]varchar(128), [IsReadOnly]varchar(128), [IsPresent]varchar(128), [TDEThumbprint]varchar(128)
)
DECLARE @Path varchar(1000)='C:\SomePath\Base.bak'
DECLARE @LogicalNameData varchar(128),@LogicalNameLog varchar(128)
INSERT INTO @table
EXEC('
RESTORE FILELISTONLY 
   FROM DISK=''' +@Path+ '''
   ')

   SET @LogicalNameData=(SELECT LogicalName FROM @Table WHERE Type='D')
   SET @LogicalNameLog=(SELECT LogicalName FROM @Table WHERE Type='L')

SELECT @LogicalNameData,@LogicalNameLog

更新

根据 Microsoft网站:

SQL Server文件有两个名称:

SQL Server files have two names:

逻辑文件名

logical_file_name是用于引用物理文件的名称 在所有Transact-SQL语句中.逻辑文件名必须符合 SQL Server标识符的规则，并且在逻辑之间必须唯一 数据库中的文件名.

The logical_file_name is the name used to refer to the physical file in all Transact-SQL statements. The logical file name must comply with the rules for SQL Server identifiers and must be unique among logical file names in the database.

os_file_name

os_file_name

os_file_name是物理文件的名称，包括 目录路径.它必须遵循操作系统文件的规则 名称.

The os_file_name is the name of the physical file including the directory path. It must follow the rules for the operating system file names.

这篇关于如何从备份文件中检索数据库的逻辑文件名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！