Load Backup History from Backup File

-- ================================================================================
-- Net Connex SQL Server Stored Procedure Creation Script
-- ================================================================================
-- Function Name  : usp_LoadBackupHistory
-- See object comments below for detailed information
-- ================================================================================
IF EXISTS (SELECT name FROM sysobjects where name='usp_LoadBackupHistory' AND xtype='P')
  BEGIN
    PRINT 'Dropping Stored Procedure usp_LoadBackupHistory...'
    DROP PROCEDURE usp_LoadBackupHistory
  END
GO
PRINT 'Creating Stored Procedure usp_LoadBackupHistory...'
GO
-- =============================================================================
-- Procedure Name : usp_LoadBackupHistory
-- Author         : Bret Stateham (bret@netconnex.com)
-- Date           : 03/11/03
-- Description    : Verifies and Loads History for all backups in a media set.
--
-- Parameters:
-- =============================================================================
--    Parameter           Data Type         Default   Description
--    ------------------  ----------------  --------  --------------------------
--    @Path               varchar(1000)     NULL      The path the backup file
--
-- Return Values (int):
-- =============================================================================
--    Value  Meaining                     
--    -----  --------
--    0      Success
--    1      Failure - Incorrect parameters or failed load of header info
-- =============================================================================
CREATE PROC usp_LoadBackupHistory
  @Path as varchar(1000)=NULL
AS

--Check for something in the @Path parm....
IF @Path IS NULL
  BEGIN
    RAISERROR('You must supply the system path to the backup file to investigate and load.',16,1)
    RETURN 1
  END

SET NOCOUNT ON

--Create a temp table to load the backup information into
CREATE TABLE #BackupHeader
(
  BackupName nvarchar(129), 
  BackupDescription nvarchar(255), 
  BackupType tinyint, 
  ExpirationDate datetime,
  Compressed tinyint, 
  Position smallint, 
  DeviceType tinyint, 
  UserName nvarchar(129), 
  ServerName nvarchar(129),
  DatabaseName nvarchar(129), 
  DatabaseVersion int, 
  DatabaseCreationDate datetime, 
  BackupSize decimal(20,0),
  FirstLsn decimal(25,0), 
  LastLsn decimal(25,0), 
  CheckpointLsn decimal(25,0), 
  DifferentialLsn decimal(25,0),
  BackupStartDate datetime, 
  BackupFinishDate datetime, 
  SortOrder smallint,
  CodePage smallint,
  UnicodeLocaleID int,
  UnicodeComparisonStyle int,
  CompatabilityLevel tinyint,
  SoftwareVendorID int,
  SoftwareVersionMajor int,
  SoftwareVersionMinor int,
  SoftwareVersionBuild int,
  MachineName nvarchar(129),
  Flags int,
  BindingID nvarchar(38),
  RecoveryForkID nvarchar(38),
  Collation nvarchar(129)
)

--Load the table up with the results from the RESTORE HEADERONLY statement
INSERT INTO #BackupHeader
EXEC('RESTORE HEADERONLY FROM DISK=''' + @Path + '''')

--Check for a successful load
IF @@ERROR <> 0 
  BEGIN
    RAISERROR ('There was a problem loading the backup information.  Aborting!',16,1)
    SET NOCOUNT OFF    
    RETURN 1
  END 

--Return the backup info to the caller
SELECT * FROM #BackupHeader

--Use a server side cursor to loop through the backups in the media set,
--verify them, and load their history info into MSDB
DECLARE curBackups CURSOR
READ_ONLY
FOR SELECT CAST(Position AS varchar(2)) FROM #BackupHeader
DECLARE @Pos varchar(2)
OPEN curBackups
FETCH NEXT FROM curBackups INTO @Pos
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
    BEGIN
      PRINT 'Validating and loading history for backup as postion #' + @Pos
      EXEC('RESTORE VERIFYONLY FROM DISK=''' + @Path +''' WITH FILE=' + @Pos + ', LOADHISTORY')
    END
  FETCH NEXT FROM curBackups INTO @Pos
END
CLOSE curBackups
DEALLOCATE curBackups

--DROP the temp table
DROP TABLE #BackupHeader

SET NOCOUNT OFF

RETURN 0
GO
PRINT 'Done!'
PRINT 'Sample Execution:'
PRINT 'EXEC usp_LoadBackupHistory ''C:\Backups\MyDBBakups.Bak'''