Load Backup History from Backup File
This script creates a stored proc to verify all backups in a media set and load their history into MSDB. I use this frequently when I get backup files from clients and want to use Enterprise Manager's intelligent restore selection to quickly get the database restored on a test machine.
-- ================================================================================
-- 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'''