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 have 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.
Parameters
The script uses 4 parameters:
@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.
The Script
You can download the full script from here:
Or copy and paste it from here:
Transact-SQL
DECLARE @TransactionLogBackupFolder VARCHAR(4000) = 'C:SqlDBBackupsMyDB' , @FileNameQualifier VARCHAR(4000) = 'MyDB_%.trn' , @DatabaseName SYSNAME = 'MyDB' , @PerformRecovery BIT = 0 SET NOCOUNT ON; DECLARE @Output AS TABLE (Msg NVARCHAR(MAX)); DECLARE @CMD VARCHAR(4000) -- Add backslash at end of path if doesn't exist already IF RIGHT(@TransactionLogBackupFolder, 1) <> '' SET @TransactionLogBackupFolder = @TransactionLogBackupFolder + '' -- Prepare and execute dir command SET @CMD = 'dir /b "' + @TransactionLogBackupFolder + N'"' INSERT INTO @Output EXEC xp_cmdshell @CMD -- Loop through all files that comply with the specified qualifier DECLARE @CurrPath NVARCHAR(MAX) DECLARE CM CURSOR FOR SELECT * FROM @Output WHERE Msg LIKE @FileNameQualifier ORDER BY Msg OPEN CM FETCH NEXT FROM CM INTO @CurrPath WHILE @@FETCH_STATUS = 0 BEGIN -- 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); FETCH NEXT FROM CM INTO @CurrPath 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 RAISERROR(N'Done.',0,1) WITH NOWAIT; GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
DECLARE
@TransactionLogBackupFolder VARCHAR(4000) = 'C:SqlDBBackupsMyDB'
, @FileNameQualifier VARCHAR(4000) = 'MyDB_%.trn'
, @DatabaseName SYSNAME = 'MyDB'
, @PerformRecovery BIT = 0
SET NOCOUNT ON;
DECLARE @Output AS TABLE (Msg NVARCHAR(MAX));
DECLARE @CMD VARCHAR(4000)
-- Add backslash at end of path if doesn't exist already
IF RIGHT(@TransactionLogBackupFolder, 1) <> ''
SET @TransactionLogBackupFolder = @TransactionLogBackupFolder + ''
-- Prepare and execute dir command
SET @CMD = 'dir /b "' + @TransactionLogBackupFolder + N'"'
INSERT INTO @Output
EXEC xp_cmdshell @CMD
-- Loop through all files that comply with the specified qualifier
DECLARE @CurrPath NVARCHAR(MAX)
DECLARE CM CURSOR FOR
SELECT *
FROM @Output
WHERE Msg LIKE @FileNameQualifier
ORDER BY Msg
OPEN CM
FETCH NEXT FROM CM INTO @CurrPath
WHILE @@FETCH_STATUS = 0
BEGIN
-- 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);
FETCH NEXT FROM CM INTO @CurrPath
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
RAISERROR(N'Done.',0,1) WITH NOWAIT;
GO
Script Highlights
Let’s go over the important parts of the script and see what they do:
Transact-SQL
DECLARE @Output AS TABLE (Msg NVARCHAR(MAX)); DECLARE @CMD VARCHAR(4000) -- Prepare and execute dir command SET @CMD = 'dir /b "' + @TransactionLogBackupFolder + N'"' INSERT INTO @Output EXEC xp_cmdshell @CMD
1
2
3
4
5
6
7
8
DECLARE @Output AS TABLE (Msg NVARCHAR(MAX));
DECLARE @CMD VARCHAR(4000)
-- Prepare and execute dir command
SET @CMD = 'dir /b "' + @TransactionLogBackupFolder + N'"'
INSERT INTO @Output
EXEC xp_cmdshell @CMD
These commands will execute “dir /b” using XP_CMDSHELL and save the output into the variable table called @Output. The “/b” flag tells the “dir” command to return only the file and folder names without all the summary info and other details.
Next up we use a CURSOR to loop through all the relevant file names:
Transact-SQL
DECLARE CM CURSOR FOR SELECT * FROM @Output WHERE Msg LIKE @FileNameQualifier ORDER BY Msg
1
2
3
4
5
DECLARE CM CURSOR FOR
SELECT *
FROM @Output
WHERE Msg LIKE @FileNameQualifier
ORDER BY Msg
Note the use of the @FileNameQualifier parameter.
Then for each of the files we do the following:
Transact-SQL
-- 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' EXEC(@CMD);
1
2
3
4
5
6
-- 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'
EXEC(@CMD);
This part creates the actual RESTORE LOG command using the path of the current file (@CurrPath) and executes it. It uses some default parameters and the NORECOVERY option so we’d be able to continue restoring / rolling forward transaction log backups.
And finally:
Transact-SQL
-- Perform final recovery if needed IF @PerformRecovery = 1 BEGIN SET @CMD = N'RESTORE LOG ' + QUOTENAME(@DatabaseName) + N' WITH RECOVERY' EXEC(@CMD); END
1
2
3
4
5
6
-- Perform final recovery if needed
IF @PerformRecovery = 1
BEGIN
SET @CMD = N'RESTORE LOG ' + QUOTENAME(@DatabaseName) + N' WITH RECOVERY'
EXEC(@CMD);
END
If the boolean @PerformRecovery parameter equals to 1 then this part executes a simple RESTORE LOG… WITH RECOVERY command which will try to bring the database online.
Conclusion
This script is another example of how you can “let SQL Server write code for you” instead of doing all the hard and repetitive work by yourself (in this case, restoring a bunch of transaction log files).
Check out my blog post about this subject here: Let SQL Server Write Code for You.
Post in the comments below if you have questions, comments, or things to add.