Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL SERVER: A stored procedure that export the latest record from RESTORE HEADERONLY
/*
Original script from here: https://karaszi.com/restore-all-databases-from-a-number-of-backup-files
Tim C: made a modification so the script adjusts the columns per the SQL Server version
*/
IF OBJECT_ID (N'dbo.GetBackupRestoreHeaders') IS NULL BEGIN
EXEC('CREATE PROCEDURE dbo.GetBackupRestoreHeaders AS SET NOCOUNT ON;');
END
GO
ALTER PROCEDURE dbo.GetBackupRestoreHeaders (
@backup_file_path VARCHAR(8000),
@backup_type_filter NVARCHAR(128) = 'Database',
@BackupName NVARCHAR(128) OUTPUT,
@BackupDescription NVARCHAR(255) OUTPUT,
@BackupType SMALLINT OUTPUT,
@BackupTypeDescription NVARCHAR(128) OUTPUT,
@Compressed TINYINT OUTPUT,
@Position SMALLINT OUTPUT,
@DatabaseName NVARCHAR(128) OUTPUT,
@BackupStartDate DATETIME OUTPUT,
@BackupFinishDate DATETIME OUTPUT,
@CompatibilityLevel TINYINT OUTPUT,
@Collation NVARCHAR(128) OUTPUT,
@HasBackupChecksums BIGINT OUTPUT,
@IsCopyOnly BIGINT OUTPUT,
@BackupSize NUMERIC(20, 0) OUTPUT,
@CompressedBackupSize BIGINT OUTPUT
)
AS
BEGIN
IF OBJECT_ID('tempdb..#restore_headeronly') IS NOT NULL BEGIN
DROP TABLE #restore_headeronly
END
CREATE TABLE #restore_headeronly (
BackupName NVARCHAR(128),
BackupDescription NVARCHAR(255),
BackupType SMALLINT,
ExpirationDate DATETIME,
Compressed TINYINT,
Position SMALLINT,
DeviceType TINYINT,
UserName NVARCHAR(128),
ServerName NVARCHAR(128),
DatabaseName NVARCHAR(128),
DatabaseVersion BIGINT,
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 BIGINT,
UnicodeComparisonStyle BIGINT,
CompatibilityLevel TINYINT,
SoftwareVendorId BIGINT,
SoftwareVersionMajor BIGINT,
SoftwareVersionMinor BIGINT,
SoftwareVersionBuild BIGINT,
MachineName NVARCHAR(128),
Flags BIGINT,
BindingID UNIQUEIDENTIFIER,
RecoveryForkID UNIQUEIDENTIFIER,
Collation NVARCHAR(128),
FamilyGUID UNIQUEIDENTIFIER,
HasBulkLoggedData BIGINT,
IsSnapshot BIGINT,
IsReadOnly BIGINT,
IsSingleUser BIGINT,
HasBackupChecksums BIGINT,
IsDamaged BIGINT,
BeginsLogChain BIGINT,
HasIncompleteMetaData BIGINT,
IsForceOffline BIGINT,
IsCopyOnly BIGINT,
FirstRecoveryForkID UNIQUEIDENTIFIER,
ForkPointLSN NUMERIC(25, 0),
RecoveryModel NVARCHAR(128),
DifferentialBaseLSN NUMERIC(25, 0),
DifferentialBaseGUID UNIQUEIDENTIFIER,
BackupTypeDescription NVARCHAR(128),
BackupSetGUID UNIQUEIDENTIFIER,
CompressedBackupSize BIGINT
);
DECLARE @major_version INT = CAST(SERVERPROPERTY('ProductMajorVersion') AS INT)
-- this column was added in 2012
IF @major_version >= 11 BEGIN
ALTER TABLE #restore_headeronly ADD
Containment BIGINT
END
-- these columns were added in 2014
IF @major_version >= 12 BEGIN
ALTER TABLE #restore_headeronly ADD
KeyAlgorithm NVARCHAR(32),
EncryptorThumbprint VARBINARY(20),
EncryptorType NVARCHAR(23)
END
-- cant use explicit columns here because of the version differences
INSERT INTO #restore_headeronly
EXEC('RESTORE HEADERONLY FROM DISK = ''' + @backup_file_path + '''')
-- SELECT * FROM #restore_headeronly
-- select top 1 ordered by position desc in case they are using the backup-append.
SELECT TOP (1)
@BackupName = b.BackupName,
@BackupDescription = b.BackupDescription,
@BackupType = b.BackupType,
@BackupTypeDescription = b.BackupTypeDescription,
@Compressed = b.Compressed,
@Position = b.Position,
@DatabaseName = b.DatabaseName,
@BackupStartDate = b.BackupStartDate,
@BackupFinishDate = b.BackupFinishDate,
@CompatibilityLevel = b.CompatibilityLevel,
@Collation = b.Collation,
@HasBackupChecksums = b.HasBackupChecksums,
@IsCopyOnly = b.IsCopyOnly,
@BackupSize = b.BackupSize,
@CompressedBackupSize = b.CompressedBackupSize
FROM #restore_headeronly b
WHERE b.BackupTypeDescription = @backup_type_filter
ORDER BY b.Position DESC
END
GO
/***********************************************************************************************************/
/***********************************************************************************************************/
/**************EXAMPLE CALL*********************************************************************************/
/***********************************************************************************************************/
/***********************************************************************************************************/
DECLARE @backup_file_path varchar(8000) = 'path to backup.bak'
, @backup_type_filter NVARCHAR(128) = 'Database'
, @BackupName nvarchar(128)
, @BackupDescription nvarchar(255)
, @BackupType smallint
, @BackupTypeDescription nvarchar(128)
, @Compressed tinyint
, @Position smallint
, @DatabaseName nvarchar(128)
, @BackupStartDate datetime
, @BackupFinishDate datetime
, @CompatibilityLevel tinyint
, @Collation nvarchar(128)
, @HasBackupChecksums bigint
, @IsCopyOnly bigint
, @BackupSize numeric(20,0)
, @CompressedBackupSize bigint
EXECUTE [Admin].[dbo].[GetBackupRestoreHeaders]
@backup_file_path
, @backup_type_filter
, @BackupName OUTPUT
, @BackupDescription OUTPUT
, @BackupType OUTPUT
, @BackupTypeDescription OUTPUT
, @Compressed OUTPUT
, @Position OUTPUT
, @DatabaseName OUTPUT
, @BackupStartDate OUTPUT
, @BackupFinishDate OUTPUT
, @CompatibilityLevel OUTPUT
, @Collation OUTPUT
, @HasBackupChecksums OUTPUT
, @IsCopyOnly OUTPUT
, @BackupSize OUTPUT
, @CompressedBackupSize OUTPUT
SELECT
[backup_file_path] = @backup_file_path
, [BackupName] = @BackupName
, [BackupDescription] = @BackupDescription
, [BackupType] = @BackupType
, [BackupTypeDescription] = @BackupTypeDescription
, [Compressed] = @Compressed
, [Position] = @Position
, [DatabaseName] = @DatabaseName
, [BackupStartDate] = @BackupStartDate
, [BackupFinishDate] = @BackupFinishDate
, [CompatibilityLevel] = @CompatibilityLevel
, [Collation] = @Collation
, [HasBackupChecksums] = @HasBackupChecksums
, [IsCopyOnly] = @IsCopyOnly
, [BackupSize] = @BackupSize
, [CompressedBackupSize] = @CompressedBackupSize
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment