USE master
GO
IF OBJECT_ID('sp_BackupHeaderList') IS NULL EXEC ('CREATE PROCEDURE sp_BackupHeaderList AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_BackupHeaderList
@BackupPath VARCHAR(256) = NULL,
@LiteSpeed BIT = 1
AS
SET NOCOUNT ON
IF @BackupPath IS NULL
BEGIN
PRINT '
/* -- SILENT OPERATION
IF OBJECT_ID(''TEMPDB..#BackupHeaderList'') IS NOT NULL DROP TABLE #BackupHeaderList
CREATE TABLE #BackupHeaderList (LiteSpeed BIT NOT NULL,
BackupDescription VARCHAR(255) NULL,
BackupFinishDate DATETIME NULL,
BackupName VARCHAR(256) NULL,
BackupSize NUMERIC(20, 0) NULL,
BackupStartDate DATETIME NULL,
BackupType VARCHAR(128) NULL,
BindingID UNIQUEIDENTIFIER NULL,
CheckpointLsn NUMERIC(25, 0) NULL,
DatabaseBackupLSN NUMERIC(25, 0),
[Codepage] SMALLINT NULL,
CompatibilityLevel TINYINT NULL,
Compressed TINYINT NULL,
DatabaseCreationDate DATETIME NULL,
DatabaseName VARCHAR(128) NULL,
DatabaseVersion INT NULL,
DeviceType TINYINT NULL,
DifferentialBaseLSN NUMERIC(25, 0) NULL,
ExpirationDate DATETIME NULL,
FirstLSN NUMERIC(25, 0) NULL,
IsCopyOnly VARCHAR(128) NULL,
LastLSN NUMERIC(25, 0) NULL,
MachineName VARCHAR(128) NULL,
[Position] SMALLINT NULL,
RecoveryForkID UNIQUEIDENTIFIER NULL,
[ServerName] VARCHAR(128) NULL,
SoftwareVendorId INT NULL,
SoftwareVersionBuild INT NULL,
SoftwareVersionMajor INT NULL,
SoftwareVersionMinor INT NULL,
SortOrder SMALLINT NULL,
UserName VARCHAR(128) NULL)
*/
'
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_BackupHeaderList',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN
END
IF NOT EXISTS ( SELECT *
FROM Util.FS.GetFileInfo(@BackupPath) )
BEGIN
RAISERROR ('File %s does not exists!', 16, 1, @BackupPath)
RETURN
END
--#region Backup Header Definitions
DECLARE @MSBACKUP TABLE (BackupName VARCHAR(256),
BackupDescription VARCHAR(255),
BackupType SMALLINT,
ExpirationDate DATETIME,
Compressed TINYINT,
[Position] SMALLINT,
DeviceType TINYINT,
UserName VARCHAR(128),
[ServerName] VARCHAR(128),
DatabaseName VARCHAR(128),
DatabaseVersion INT,
DatabaseCreationDate DATETIME,
BackupSize NUMERIC(20, 0),
FirstLSN NUMERIC(25, 0),
LastLSN NUMERIC(25, 0),
CheckpointLSN NUMERIC(25, 0),
DatabaseBackupLSN NUMERIC(25, 0),
BackupStartDate DATETIME,
BackupFinishDate DATETIME,
SortOrder SMALLINT,
[Codepage] SMALLINT,
UnicodeLocaleId INT,
UnicodeComparisonStyle INT,
CompatibilityLevel TINYINT,
SoftwareVendorId INT,
SoftwareVersionMajor INT,
SoftwareVersionMinor INT,
SoftwareVersionBuild INT,
MachineName VARCHAR(128),
Flags INT,
BindingID UNIQUEIDENTIFIER,
RecoveryForkID UNIQUEIDENTIFIER,
[COLLATION] VARCHAR(128),
FamilyGUID UNIQUEIDENTIFIER,
HasBulkLoggedData BIT,
IsSnapshot BIT,
IsReadOnly BIT,
IsSingleUser BIT,
HasBackupChecksums BIT,
IsDamaged BIT,
BeginsLogChain BIT,
HasIncompleteMetaData BIT,
IsForceOffline BIT,
IsCopyOnly BIT,
FirstRecoveryForkID UNIQUEIDENTIFIER,
ForkPointLSN NUMERIC(25, 0),
RecoveryModel VARCHAR(60),
DifferentialBaseLSN NUMERIC(25, 0),
DifferentialBaseGUID UNIQUEIDENTIFIER,
BackupTypeDescription VARCHAR(60),
BackupSetGUID UNIQUEIDENTIFIER,
CompressedBackupSize BIGINT)
DECLARE @LITESPEEDBACKUP TABLE (FileNumber INT,
BackupFormat VARCHAR(128),
Guid UNIQUEIDENTIFIER,
BackupName VARCHAR(256),
BackupDescription VARCHAR(128),
BackupType VARCHAR(128),
ExpirationDate DATETIME,
Compressed TINYINT,
[Position] SMALLINT,
DeviceType TINYINT,
UserName VARCHAR(128),
[ServerName] VARCHAR(128),
DatabaseName VARCHAR(128),
DatabaseVersion INT,
DatabaseCreationDate DATETIME,
BackupSize NUMERIC(20, 0),
FirstLsn NUMERIC(25, 0),
LastLsn NUMERIC(25, 0),
CheckpointLsn NUMERIC(25, 0),
DifferentialBaseLsn NUMERIC(25, 0),
BackupStartDate DATETIME,
BackupFinishDate DATETIME,
SortOrder SMALLINT,
[Codepage] SMALLINT,
CompatibilityLevel TINYINT,
SoftwareVendorId INT,
SoftwareVersionMajor INT,
SoftwareVersionMinor INT,
SoftwareVersionBuild INT,
MachineName VARCHAR(128),
BindingId UNIQUEIDENTIFIER,
RecoveryForkId UNIQUEIDENTIFIER,
ENCRYPTION INT,
IsCopyOnly VARCHAR(128))
--#endregion
--#region Populate Backup Header
IF @LiteSpeed = 1
BEGIN
BEGIN TRY
INSERT @LITESPEEDBACKUP
EXEC master.dbo.xp_restore_headeronly
@filename = @BackupPath
END TRY
BEGIN CATCH
BEGIN TRY
INSERT @MSBACKUP
EXEC ('RESTORE HEADERONLY FROM DISK = ''' + @BackupPath + '''')
SET @LiteSpeed = 0
END TRY
BEGIN CATCH
END CATCH
END CATCH
END
ELSE
IF @LiteSpeed = 0
BEGIN
BEGIN TRY
INSERT @MSBACKUP
EXEC ('RESTORE HEADERONLY FROM DISK = ''' + @BackupPath + '''')
END TRY
BEGIN CATCH
BEGIN TRY
INSERT @LITESPEEDBACKUP
EXEC master.dbo.xp_restore_headeronly
@filename = @BackupPath
SET @LiteSpeed = 1
END TRY
BEGIN CATCH
END CATCH
END CATCH
END
--#endregion
IF @LiteSpeed = 1
IF OBJECT_ID('TEMPDB..#BackupHeaderList') IS NOT NULL
INSERT #BackupHeaderList
(LiteSpeed,
BackupDescription,
BackupFinishDate,
BackupName,
BackupSize,
BackupStartDate,
BackupType,
BindingID,
CheckpointLsn,
[Codepage],
CompatibilityLevel,
Compressed,
DatabaseCreationDate,
DatabaseName,
DatabaseVersion,
DeviceType,
DifferentialBaseLSN,
ExpirationDate,
FirstLSN,
IsCopyOnly,
LastLSN,
MachineName,
[Position],
RecoveryForkID,
[ServerName],
SoftwareVendorId,
SoftwareVersionBuild,
SoftwareVersionMajor,
SoftwareVersionMinor,
SortOrder,
UserName)
SELECT 1 AS LiteSpeed,
BackupDescription,
BackupFinishDate,
BackupName,
BackupSize,
BackupStartDate,
BackupType,
BindingID,
CheckpointLsn,
[Codepage],
CompatibilityLevel,
Compressed,
DatabaseCreationDate,
DatabaseName,
DatabaseVersion,
DeviceType,
DifferentialBaseLSN,
ExpirationDate,
FirstLSN,
IsCopyOnly,
LastLSN,
MachineName,
[Position],
RecoveryForkID,
[ServerName],
SoftwareVendorId,
SoftwareVersionBuild,
SoftwareVersionMajor,
SoftwareVersionMinor,
SortOrder,
UserName
FROM @LITESPEEDBACKUP
ELSE
SELECT 1 AS LiteSpeed,
*
FROM @LITESPEEDBACKUP
ELSE
IF OBJECT_ID('TEMPDB..#BackupHeaderList') IS NOT NULL
INSERT #BackupHeaderList
(LiteSpeed,
BackupDescription,
BackupFinishDate,
BackupName,
BackupSize,
BackupStartDate,
BackupType,
BindingID,
CheckpointLsn,
DatabaseBackupLSN,
[Codepage],
CompatibilityLevel,
Compressed,
DatabaseCreationDate,
DatabaseName,
DatabaseVersion,
DeviceType,
DifferentialBaseLSN,
ExpirationDate,
FirstLSN,
IsCopyOnly,
LastLSN,
MachineName,
[Position],
RecoveryForkID,
[ServerName],
SoftwareVendorId,
SoftwareVersionBuild,
SoftwareVersionMajor,
SoftwareVersionMinor,
SortOrder,
UserName)
SELECT 0 AS LiteSpeed,
BackupDescription,
BackupFinishDate,
BackupName,
BackupSize,
BackupStartDate,
BackupType,
BindingID,
CheckpointLsn,
DatabaseBackupLSN,
[Codepage],
CompatibilityLevel,
Compressed,
DatabaseCreationDate,
DatabaseName,
DatabaseVersion,
DeviceType,
DifferentialBaseLSN,
ExpirationDate,
FirstLSN,
IsCopyOnly,
LastLSN,
MachineName,
[Position],
RecoveryForkID,
[ServerName],
SoftwareVendorId,
SoftwareVersionBuild,
SoftwareVersionMajor,
SoftwareVersionMinor,
SortOrder,
UserName
FROM @MSBACKUP
ELSE
SELECT 0 AS LiteSpeed,
*
FROM @MSBACKUP
GO
EXEC sys.sp_ms_marksystemobject sp_BackupHeaderList
GO