USE master
go
IF OBJECT_ID('dbo.sp_restore_db_logs') IS NOT NULL And
OBJECTPROPERTY(OBJECT_ID('dbo.sp_restore_db_logs'), 'IsProcedure') = 1
BEGIN
DROP PROCEDURE dbo.sp_restore_db_logs
IF OBJECT_ID('dbo.sp_restore_db_logs') IS NOT NULL And
OBJECTPROPERTY(OBJECT_ID('dbo.sp_restore_db_logs'), 'IsProcedure') = 1
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_restore_db_logs >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_restore_db_logs >>>'
END
go
CREATE PROCEDURE sp_restore_db_logs (
@SourceDir nvarchar( 512 ) = NULL ,
@DBBackup nvarchar( 512 ) = NULL ,
@SourceDB nvarchar( 128 ) = NULL ,
@DestDB nvarchar( 128 ) = NULL ,
@StartAt datetime = NULL ,
@StopAt datetime = NULL ,
@RestoreMode varchar( 10 ) = 'NORECOVERY' ,
@MoveDataTo varchar( 255 ) = NULL ,
@MoveLogTo varchar( 255 ) = NULL ,
@RecoverAtEnd bit = 0 ,
@Debug bit = 0 ,
@JustCheck bit = 0
)
WITH ENCRYPTION
AS
/*********************************************************************************/
/* Created By : Umachandar Jayachandran (UC) */
/* Created On : 06 September 2000 */
/* Description: This stored procedure can be used to load a database backup and */
/* several log backups using a programmable mechanism. This SP also */
/* accepts several parameters that can be used to control the log */
/* loads, recovery mode & so on. This also serves to demonstrate */
/* several powerful T-SQL programming techniques. */
/*********************************************************************************/
/* Resources : https://umachandar.com/resources.htm */
/*********************************************************************************/
CREATE TABLE #Files(
[FileName] varchar( 255 ) NULL ,
FileTimeStamp AS ( CAST( STUFF( STUFF( SUBSTRING( [FileName] ,
PATINDEX('%_' + REPLICATE( '[0-9]' , 12 ) + '%' , [FileName] ) + 1 ,
COALESCE( NULLIF( PATINDEX( '%.TRN', [FileName] ) , 0 ) ,
NULLIF( PATINDEX( '%.BAK', [FileName] ) , 0 ) ) -
PATINDEX('%_' + REPLICATE( '[0-9]' + '%' , 12 ),
[FileName] ) - 1 ) ,
9 , 0 , SPACE( 1 ) ) ,
12 , 0 , ':' ) AS datetime ) )
);
CREATE TABLE #DBFiles (
LogicalName nvarchar( 128 ) ,
PhysicalName nvarchar( 512 ) ,
Type char( 1 ) ,
FileGroupName nvarchar( 128 ) ,
[Size] varchar( 30 ) ,
[MaxSize] varchar( 30 ) ,
RevPhysicalName AS ( REVERSE( PhysicalName ) )
);
DECLARE @CmdStr varchar( 8000 ) , @LogExt varchar( 10 ) , @DBExt varchar( 10 ) ,
@crlf char( 2 ) , @headerline varchar( 255 ) , @DBBackupDir nvarchar( 512 ) ,
@FileName varchar( 255 ) , @LoadFile varchar( 255 ) , @StandbyFile varchar( 255 ) ,
@Source varchar( 10 ) , @IsTapeBackup bit , @DoDBRestore bit , @MoveDBFiles bit;
SELECT @DBExt = '.BAK' , @LogExt = '.TRN' , @crlf = CHAR( 13 ) + CHAR( 10 ) ,
@headerline = @crlf + REPLICATE( '-' , 80 ) + @crlf ,
@IsTapeBackup = CASE WHEN @DBBackup LIKE '\\.\Tape%' THEN 1 ELSE 0 END ,
@Source = CASE @IsTapeBackup WHEN 1 THEN 'TAPE' WHEN 0 THEN 'DISK' END ,
@DoDBRestore = CASE WHEN @DBBackup IS NULL THEN 0 ELSE 1 END ,
@MoveDBFiles = CASE WHEN COALESCE( @MoveDataTo , @MoveLogTo ) IS NULL THEN 0 ELSE 1 END ,
@SourceDir = @SourceDir + CASE RIGHT( @SourceDir , 1 ) WHEN '\' THEN '' ELSE '\' END ,
@MoveDataTo = @MoveDataTo + CASE RIGHT( @MoveDataTo , 1 ) WHEN '\' THEN '' ELSE '\' END ,
@MoveLogTo = @MoveLogTo + CASE RIGHT( @MoveLogTo , 1 ) WHEN '\' THEN '' ELSE '\' END ,
@StopAt = COALESCE( @StopAt , '9999-12-31 00:00' );
IF COALESCE( UPPER( @RestoreMode ) , 'NORECOVERY' ) NOT IN ( 'NORECOVERY' , 'STANDBY' )
BEGIN
RAISERROR( 'Invalid restore option was specified!', -1 , 12 );
RETURN( -1 );
END;
IF COALESCE( NULLIF( @SourceDir , '' ) , NULLIF( @SourceDB , '' ) ,
NULLIF( @DestDB , '' ) ) IS NULL
BEGIN
PRINT 'Usage: sp_restore_db_logs , , ' +
' , ,' + @crlf +
' , , ' +
' , ' + @crlf +
' , , ' +
' , ' + @headerline +
'Defaults: NULL value for means a database backup with a timestamp' + @crlf +
' earlier than the first log dump / start at value will be used.' + @crlf +
' NULL values for & means '+
'all dumps will be loaded.'+ @crlf +
' default is NORECOVERY.' + @headerline +
'Parameters: complete file path for a database backup to load before ' + @crlf +
' restoring logs. The file path can be relative also. ' + @crlf +
' & can be datetime values. This will be used' + @crlf +
' to determine the starting & end point of the log dumps.' + @crlf +
' can be NORECOVERY | STANDBY' + @crlf +
' & can be new paths for the restored data' + @crlf +
' log files. The logical files are determined from the backup.' + @crlf +
' is a flag to perform recovery at the end of restore.' + @crlf +
' will print progress messages & other details.' + @crlf +
' will not perform the actual restore but instead display' + @crlf +
' files to be loaded etc. This can be used to verify' + @crlf +
' the parameters before performing the actual restore.' +
@headerline
RETURN( -1 );
END;
IF @Debug = 1 PRINT 'Destination Database : ' + @DestDB + @crlf;
SET @CmdStr = 'DIR /B ' + @SourceDir + @SourceDB + '*' + @LogExt;
IF @Debug = 1 PRINT 'Directory Command String (Logs): ' + @CmdStr + @crlf;
INSERT INTO #Files ( [FileName] ) EXEC master..xp_cmdshell @CmdStr;
IF @StartAt IS NOT NULL
DELETE #Files WHERE FileTimeStamp NOT BETWEEN @StartAt And @StopAt;
SELECT @StandbyFile = @SourceDir + @DestDB + '.REDO'
WHERE UPPER( @RestoreMode ) = 'STANDBY';
IF @DoDBRestore = 1
BEGIN
IF @IsTapeBackup = 1
SET @FileName = @DBBackup;
ELSE IF @IsTapeBackup = 0
BEGIN
-- If filename was not specified , then use source directory + dbbackup path for search
IF CHARINDEX( @DBExt , @DBBackup ) > 0
SET @FileName = @DBBackup;
ELSE
BEGIN
IF @DBBackup LIKE '\\%' or @DBBackup LIKE '[a-zA-Z]%'
SET @DBBackupDir = @DBBackup +
CASE RIGHT( @DBBackup , 1 ) WHEN '\' THEN '' ELSE '\' END;
ELSE
SET @DBBackupDir = @SourceDir + @DBBackup +
CASE RIGHT( @DBBackup , 1 ) WHEN '\' THEN '' ELSE '\' END;
SET @CmdStr = 'DIR /B ' + @DBBackupDir + @SourceDB + '*' + @DBExt;
IF @Debug = 1 PRINT 'Directory Command String (DB) : ' + @CmdStr + @crlf;
INSERT INTO #Files ( [FileName] ) EXEC master..xp_cmdshell @CmdStr;
SELECT TOP 1 @FileName = @DBBackupDir + [FileName]
FROM #Files
WHERE FileTimeStamp < ( SELECT MIN( FileTimeStamp ) FROM #Files
WHERE CHARINDEX( @LogExt , [FileName] ) > 0 )
ORDER BY FileTimeStamp DESC;
END
END;
IF @MoveDBFiles = 1
BEGIN
SET @CmdStr = 'RESTORE FILELISTONLY FROM ' + @Source + ' = ' + QUOTENAME( @FileName , '''' );
IF @Debug = 1 PRINT @CmdStr
INSERT INTO #DBFiles (
LogicalName , PhysicalName , Type , FileGroupName ,
[Size] , [MaxSize]
) EXEC( @CmdStr );
END;
IF @Debug = 1 PRINT @headerline + 'Database Backup to be loaded :' + @headerline;
PRINT @FileName + COALESCE( ' | ' + @StandbyFile , '' );
SET @CmdStr = NULL;
UPDATE #DBFiles
SET @CmdStr = COALESCE( @CmdStr , '' ) + 'MOVE ' + QUOTENAME( LogicalName , '''' ) + ' TO ' +
QUOTENAME( CASE Type WHEN 'D' THEN @MoveDataTo WHEN 'L' THEN @MoveLogTo END +
REVERSE( LEFT( RevPhysicalName ,
CHARINDEX( '\' , RevPhysicalName ) - 1 ) ) , '''' ) +
' , ' + @crlf
WHERE Type IN ( CASE WHEN @MoveDataTo IS NULL THEN '' ELSE 'D' END ,
CASE WHEN @MoveLogTo IS NULL THEN '' ELSE 'L' END );
SET @CmdStr = 'RESTORE DATABASE ' + QUOTENAME( @DestDB ) +
' FROM ' + @Source + ' = ' + QUOTENAME( @FileName , '''' ) + @crlf +
'WITH ' + UPPER( @RestoreMode ) +
CASE UPPER( @RestoreMode ) WHEN 'STANDBY'
THEN ' = ' + QUOTENAME( @StandbyFile , '''' ) ELSE '' END +
CASE WHEN DB_ID( @DestDB ) IS NULL THEN '' ELSE ' , REPLACE ' + @crlf END +
COALESCE( ', ' + LEFT( @CmdStr , LEN( @CmdStr ) - 5 ) , '' );
IF @Debug = 1 PRINT @CmdStr;
IF @JustCheck = 0 EXEC( @CmdStr );
END;
IF @Debug = 1 PRINT @headerline + 'Log Backups to be loaded :' + @headerline;
-- Files to be loaded:
DECLARE loadfiles CURSOR FAST_FORWARD FOR
SELECT [FileName] FROM #Files
WHERE CHARINDEX( @LogExt , [FileName] ) > 0 And
( ( @StartAt IS NOT NULL And FileTimeStamp BETWEEN @StartAt And @StopAt ) Or
( @StartAt IS NULL ) )
ORDER BY [FileName];
OPEN loadfiles;
WHILE( 'FETCH IS OK' = 'FETCH IS OK' )
BEGIN
FETCH loadfiles INTO @FileName;
IF @@FETCH_STATUS < 0 BREAK;
SET @LoadFile = @SourceDir + @FileName ;
PRINT @LoadFile + COALESCE( ' | ' + @StandbyFile , '' );
IF UPPER( @RestoreMode ) = 'NORECOVERY' And @JustCheck = 0
RESTORE LOG @DestDB
FROM DISK = @LoadFile
WITH NORECOVERY;
ELSE IF UPPER( @RestoreMode ) = 'STANDBY' And @JustCheck = 0
RESTORE LOG @DestDB
FROM DISK = @LoadFile
WITH STANDBY = @StandbyFile;
END;
DEALLOCATE loadfiles;
IF @Debug = 1 PRINT @headerline;
IF @RecoverAtEnd = 1 RESTORE DATABASE @DestDB WITH RECOVERY;
RETURN( 0 );
GO
IF OBJECT_ID('dbo.sp_restore_db_logs') IS NOT NULL And
OBJECTPROPERTY(OBJECT_ID('dbo.sp_restore_db_logs'), 'IsProcedure') = 1
BEGIN
GRANT EXECUTE ON dbo.sp_restore_db_logs To Public
PRINT '<<< CREATED PROCEDURE dbo.sp_restore_db_logs >>>'
END
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_restore_db_logs >>>'
go
This page was last updated on May 01, 2006 04:28 PM.