T-SQL Script to Roll Forward Transaction Log Backups

Performing a transaction log backup every 15 minutes has its advantages. But it also has disadvantages. Ever tried to restore hundreds of transaction log backups one by one in order to bring your database back from disaster? Not a bit of fun that is. Check out this useful script that I prepared to help sort this problem out.

This script is especially useful for those “Maintenance Plan” backups where each backup is in a separate file, all located in a single folder, and has uniform file names that represent their date and time of backup.

The script uses XP_CMDSHELL in order to get a listing of all the files in the folder. Take serious note of this, because having the XP_CMDSHELL procedure enabled is a huge security risk! It’s highly recommended to disable XP_CMDSHELL again once you’re done with the script.

EDIT 2022-02-23: I finally replaced the usage of xp_cmdshell with xp_dirtree instead, which is a safe, built-in stored procedure for listing files in a folder.

 Parameters

The script uses 4 parameters:

DECLARE 
   @TransactionLogBackupFolder VARCHAR(4000) = 'C:\SqlDBBackupsMyDB'
 , @FileNameQualifier VARCHAR(4000) = 'MyDB_%.trn'
 , @DatabaseName SYSNAME = 'MyDB'
 , @PerformRecovery BIT = 0
  • @TransactionLogBackupFolder: The folder path where all the transaction log backups are located. You must end this path with a backslash ( \ ).
  • @FileNameQualifier: This is a file name qualifier which will be used in a LIKE clause in order to filter the files which you want to restore. This is useful if there are backup files of more than one database in the same folder, or if you want to make SQL’s job easier by filtering which files to restore (for example: ‘MyDB_backup_2015_08_%.trn’ will only try to restore from the backups made in August 2015). If you don’t want to use any filter, please specify ‘%’.
  • @DatabaseName: This is the database name which should be restored. Needless to say, since we’re restoring transaction log backups here, the database must be in NORECOVERY or STANDBY mode before you begin.
  • @PerformRecovery: If you want the script to finalize the restore process by bringing the database back online WITH RECOVERY, then specify “1” for this parameter. It’s recommended to leave this parameter as “0” at least at first, to make sure all the backups you need were properly restored.
  • @StandByFilePath: If you want the script to finalize the restore process by leaving the database in STANDBY mode, then you can specify a full file path for this parameter to determine the location of the standby/undo file. If you use this option, you must also set the parameter @PerformRecovery to “0”.

 The Script

/*
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Date: August, 2015
Description:
Use this script to easily roll forward multiple transaction log backups from a given folder.
More info:
https://eitanblumin.com/2018/10/28/t-sql-script-to-roll-forward-transaction-log-backups
*/
DECLARE
@TransactionLogBackupFolder VARCHAR(4000) = 'C:\SqlDBBackupsMyDB'
, @FileNameQualifier VARCHAR(4000) = 'MyDB_%.trn'
, @DatabaseName SYSNAME = 'MyDB'
, @PerformRecovery BIT = 0
, @StandByFilePath varchar(4000) = NULL 'C:\MSSQL\DATA\MyDB_StandbyFile.undo'
SET NOCOUNT ON;
DECLARE @CMD VARCHAR(4000)
Add backslash at end of path if doesn't exist already
IF RIGHT(@TransactionLogBackupFolder, 1) <> '\'
SET @TransactionLogBackupFolder = @TransactionLogBackupFolder + '\'
DECLARE @FileList TABLE
(FileName nvarchar(500)
,depth int
,isFile int)
INSERT INTO @FileList
EXEC xp_dirtree @TransactionLogBackupFolder,1,1
Loop through all files that comply with the specified qualifier
DECLARE @CurrPath NVARCHAR(MAX)
DECLARE CM CURSOR
LOCAL FAST_FORWARD
FOR
SELECT FileName
FROM @FileList
WHERE isFile=1
AND FileName LIKE @FileNameQualifier
ORDER BY FileName
OPEN CM
WHILE 1=1
BEGIN
FETCH NEXT FROM CM INTO @CurrPath
IF @@FETCH_STATUS <> 0 BREAK;
Prepare and execute RESTORE LOG command
SET @CMD = N'RESTORE LOG ' + QUOTENAME(@DatabaseName) + N' FROM
DISK = N''' + @TransactionLogBackupFolder + @CurrPath + N''' WITH
FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10'
RAISERROR(@CMD,0,1) WITH NOWAIT;
EXEC(@CMD);
END
CLOSE CM
DEALLOCATE CM
Perform final recovery if needed
IF @PerformRecovery = 1
BEGIN
SET @CMD = N'RESTORE LOG ' + QUOTENAME(@DatabaseName) + N' WITH RECOVERY'
RAISERROR(@CMD,0,1) WITH NOWAIT;
EXEC(@CMD);
END
Attempt to restore with standby if needed
ELSE IF @StandByFilePath IS NOT NULL
BEGIN
SET @CMD = N'RESTORE LOG ' + QUOTENAME(@DatabaseName) + N' WITH STANDBY=''' + @StandByFilePath + N''''
RAISERROR(@CMD,0,1) WITH NOWAIT;
EXEC(@CMD);
END
RAISERROR(N'Done.',0,1) WITH NOWAIT;
GO
The script is available in my GitHub Gists

Or from the Madeira Toolbox on GitHub.

Or from my TechNet Gallery.

This article was originally published by Eitan Blumin in August 2015, at www.madeiradata.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.