SQL: Save Result From RESTORE FILELISTONLY

RESTORE FILELISTONLY

is a Transact Statement in MS-SQL that returns a result set containing a list of the database and log files contained in the backup set in SQL Server.

Basically it contains meta-data pertaining to the database’s:

  • LogicalName
  • PhysicalName
  • Type
  • Size
  • MaxSize
  • etc.

This information is quite useful when performing Restore operation. For example, the physical name can be used to restore an existing database to a new database using the backup of the existing database by identifying and changing it’s physical name.

Here’s a quick script to create a table and store result from RESTORE FILELISTONLY operation. This table can then be used to select desired columns only like PhysicalName and LogicalName.


IF OBJECT_ID('tempdb..#Restore') IS NOT NULL DROP TABLE #Restore

CREATE TABLE #Restore (
LogicalName NVARCHAR(128),
PhysicalName NVARCHAR(260),
[Type] CHAR(1),
FileGroupName NVARCHAR(128),
Size NUMERIC(20,0),
MaxSize NUMERIC(20,0),
FileID BIGINT,
CreateLSN NUMERIC(25,0),
DropLSN NUMERIC(25,0),
UniqueID UNIQUEIDENTIFIER,
ReadOnlyLSN NUMERIC(25,0),
ReadWriteLSN NUMERIC(25,0),
BackupSizeInBytes BIGINT,
SourceBlockSize INT,
FileGroupID INT,
LogGroupGUID UNIQUEIDENTIFIER,
DifferentialBaseLSN NUMERIC(25,0),
DifferentialBaseGUID UNIQUEIDENTIFIER,
IsReadOnly BIT,
IsPresent BIT,
TDEThumbprint VARBINARY(32)
)

INSERT INTO #Restore
RESTORE FILELISTONLY FROM DISK='' -- database backup path here

 

 

 

Tags: