USE master
GO
IF OBJECT_ID('sp_BackupFileList') IS NULL EXEC ('CREATE PROCEDURE sp_BackupFileList AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_BackupFileList
@BackupPath VARCHAR(1000) = NULL,
@Position SMALLINT = 1,
@LiteSpeed BIT = 1 OUTPUT,
@DoNotCheckFileExists BIT = 0
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(MAX)
IF @BackupPath IS NULL
BEGIN
PRINT '
IF OBJECT_ID(''tempdb..#RestoreFileList'') IS NOT NULL DROP TABLE #RestoreFileList
CREATE TABLE #RestoreFileList (LogicalName VARCHAR(128),
PhysicalName VARCHAR(128),
[Type] CHAR(1),
FileGroupName VARCHAR(128),
SizeGB NUMERIC(20, 4),
BackupSizeGB NUMERIC(20, 4),
MaxSizeGB NUMERIC(20, 4),
FileGroupId INT,
FileId INT,
DifferentialBaseLSN NUMERIC(25, 0) NULL,
Litespeed BIT)
EXEC dbo.sp_BackupFileList
@BackupPath = ''BackupPath'',
@Position = 1,
@LiteSpeed = 1,
@DoNotCheckFileExists = 0
SELECT LogicalName, PhysicalName, [Type], FileGroupName, SizeGB, BackupSizeGB, MaxSizeGB, FileGroupId, FileId, DifferentialBaseLSN, Litespeed
FROM #RestoreFileList
'
RETURN
END
IF ISNULL(@DoNotCheckFileExists, 0) = 0
AND NOT EXISTS ( SELECT *
FROM Util.FS.GetFileInfo(@BackupPath) )
BEGIN
RAISERROR ('File %s does not exists!', 16, 1, @BackupPath)
RETURN
END
DECLARE @MSHeader TABLE (LogicalName VARCHAR(128),
PhysicalName VARCHAR(260),
TYPE CHAR(1),
FileGroupName VARCHAR(128),
SIZE NUMERIC(20, 0),
MaxSize NUMERIC(20, 0),
FileID BIGINT,
CreateLSN NUMERIC(25, 0),
DropLSN NUMERIC(25, 0) NULL,
UniqueID UNIQUEIDENTIFIER,
ReadOnlyLSN NUMERIC(25, 0) NULL,
ReadWriteLSN NUMERIC(25, 0) NULL,
BackupSizeInBytes BIGINT,
SourceBlockSize INT,
FileGroupID INT,
LogGroupGUID UNIQUEIDENTIFIER,
DifferentialBaseLSN NUMERIC(25, 0) NULL,
DifferentialBaseGUID UNIQUEIDENTIFIER,
IsReadOnly BIT,
IsPresent BIT,
TDEThumbprint VARCHAR(128))
DECLARE @LSHeader TABLE (LogicalName VARCHAR(128),
PhysicalName VARCHAR(128),
TYPE CHAR(1),
FileGroupName VARCHAR(128),
SIZE VARCHAR(30),
MaxSize VARCHAR(30),
FileId VARCHAR(30),
BackupSizeInBytes VARCHAR(30),
FileGroupId VARCHAR(30))
DECLARE @TableCreated BIT = 0
IF OBJECT_ID('tempdb..#RestoreFileList') IS NULL
BEGIN
SET @TableCreated = 1
CREATE TABLE #RestoreFileList (LogicalName VARCHAR(128),
PhysicalName VARCHAR(128),
[TYPE] CHAR(1),
FileGroupName VARCHAR(128),
SizeGB NUMERIC(20, 4),
BackupSizeGB NUMERIC(20, 4),
MaxSizeGB NUMERIC(20, 4),
FileGroupId INT,
FileId INT,
DifferentialBaseLSN NUMERIC(25, 0) NULL,
Litespeed BIT)
END
IF @LiteSpeed = 1
BEGIN
BEGIN TRY
INSERT @LSHeader
(LogicalName,
PhysicalName,
TYPE,
FileGroupName,
SIZE,
MaxSize,
FileId,
BackupSizeInBytes,
FileGroupId)
EXEC master.dbo.xp_restore_filelistonly
@filename = @BackupPath,
@filenumber = @Position
INSERT #RestoreFileList
(LogicalName,
PhysicalName,
TYPE,
FileGroupName,
SizeGB,
MaxSizeGB,
FileId,
BackupSizeGB,
FileGroupId,
Litespeed)
SELECT LogicalName,
PhysicalName,
TYPE,
FileGroupName,
CAST(SIZE AS BIGINT) * 1.0 / 1024 / 1024 / 1024,
CAST(MaxSize AS BIGINT) * 1.0 / 1024 / 1024 / 1024,
CAST(FileId AS INT),
CAST(BackupSizeInBytes AS BIGINT) * 1.0 / 1024 / 1024 / 1024,
CAST(FileGroupId AS INT),
@LiteSpeed AS Litespeed
FROM @LSHeader
END TRY
BEGIN CATCH
SET @LiteSpeed = 0
SET @SQL = 'RESTORE FILELISTONLY FROM DISK = ''' + @BackupPath + '''' + ISNULL(' WITH FILE = ' + CAST(@Position AS VARCHAR), '') + ';'
INSERT @MSHeader
EXEC (@SQL)
INSERT #RestoreFileList
(LogicalName,
PhysicalName,
TYPE,
FileGroupName,
SizeGB,
MaxSizeGB,
FileId,
BackupSizeGB,
FileGroupId,
DifferentialBaseLSN,
Litespeed)
SELECT LogicalName,
PhysicalName,
TYPE,
FileGroupName,
CAST(SIZE AS BIGINT) * 1.0 / 1024 / 1024 / 1024,
CAST(MaxSize AS BIGINT) * 1.0 / 1024 / 1024 / 1024,
CAST(FileId AS INT),
CAST(BackupSizeInBytes AS BIGINT) * 1.0 / 1024 / 1024 / 1024,
CAST(FileGroupId AS INT),
DifferentialBaseLSN,
@LiteSpeed AS Litespeed
FROM @MSHeader
END CATCH
END
ELSE
BEGIN
BEGIN TRY
SET @SQL = 'RESTORE FILELISTONLY FROM DISK = ''' + @BackupPath + '''' + ISNULL(' WITH FILE = ' + CAST(@Position AS VARCHAR), '') + ';'
INSERT @MSHeader
EXEC (@SQL)
INSERT #RestoreFileList
(LogicalName,
PhysicalName,
TYPE,
FileGroupName,
SizeGB,
MaxSizeGB,
FileId,
BackupSizeGB,
FileGroupId,
DifferentialBaseLSN,
Litespeed)
SELECT LogicalName,
PhysicalName,
TYPE,
FileGroupName,
CAST(SIZE AS BIGINT) * 1.0 / 1024 / 1024 / 1024,
CAST(MaxSize AS BIGINT) * 1.0 / 1024 / 1024 / 1024,
CAST(FileId AS INT),
CAST(BackupSizeInBytes AS BIGINT) * 1.0 / 1024 / 1024 / 1024,
CAST(FileGroupId AS INT),
DifferentialBaseLSN,
@LiteSpeed AS Litespeed
FROM @MSHeader
END TRY
BEGIN CATCH
SET @LiteSpeed = 1
INSERT @LSHeader
(LogicalName,
PhysicalName,
TYPE,
FileGroupName,
SIZE,
MaxSize,
FileId,
BackupSizeInBytes,
FileGroupId)
EXEC master.dbo.xp_restore_filelistonly
@filename = @BackupPath,
@filenumber = @Position
INSERT #RestoreFileList
(LogicalName,
PhysicalName,
TYPE,
FileGroupName,
SizeGB,
MaxSizeGB,
FileId,
BackupSizeGB,
FileGroupId,
Litespeed)
SELECT LogicalName,
PhysicalName,
TYPE,
FileGroupName,
CAST(SIZE AS BIGINT) * 1.0 / 1024 / 1024 / 1024,
CAST(MaxSize AS BIGINT) * 1.0 / 1024 / 1024 / 1024,
CAST(FileId AS INT),
CAST(BackupSizeInBytes AS BIGINT) * 1.0 / 1024 / 1024 / 1024,
CAST(FileGroupId AS INT),
@LiteSpeed AS Litespeed
FROM @LSHeader
END CATCH
END
IF @TableCreated = 1
BEGIN
SELECT *
FROM #RestoreFileList
DROP TABLE #RestoreFileList
END
GO
EXEC sys.sp_ms_marksystemobject sp_BackupFileList
GO