From the 'Restore Wizard' console UI, we are selecting to restore the most recent full backup, the most recent diff backup, and all the t-log backups listed under that diff backup. The t-logs are configured to all append into one physical file that keeps growing with each t-log backup. The restore of our t-log backups is not working. Only first t-log is actually restored.
Looks like the LiteSpeed 'Restore Wizard' is constructing the restore script incorrectly. The @filenumber parameter's value always stays at "1" instead of getting incremented for each different appended logical t-log backup stored within that one physical T-log backup file.
We know that when referencing an appended tlog backups located within one file, we have to reference the correct t-log by denoting which "filenumber" to use. Each different time appending increments the “filenumber”. Our restore script isn't doing that, it was just always referencing filenumber = 1 over and over again.
Did we do something incorrect in our restore steps in the Restore Wizard UI? We selected the full backup, most recent diff backup, and all t-logs under that diff. Maybe we ought to be creating a separate backup file for each t-log backup rather than appending them all into one single file?
Or maybe the problem is with the backup script? Below is the backup script we are using.
BEGIN
declare @path as nvarchar(512)
set @path = N'\\our_company\SHARES\SQLBackups\LiteSpeed\%INSTANCE%\%DATABASENAME%\%TYPE%\'
set @path = @path + N'%INSTANCE%_%TYPE%_%T_%Z.LITE'
exec master.dbo.xp_backup_log
@MultiDatabaseType = N'SELECTED',
@database = @dbname,
@backupname = N'%D - %T Backup',
@desc = N'%T Backup of %D on %Y-%m-%d %I:%M:%S %p',
@compressionlevel = 4,
@filename = @path,
@init = 1,
@with = N'STATS = 10'
FETCH NEXT FROM select_cursor
INTO @dbname;
END