You can try something like the code below (this is assuming you've already performed any full/diff backups):
/* LOADS ALL TRN FILES IN A GIVEN DIRECTORY AND THEN RESTORES THEM TO THE APPROPRIATE DATABASE */
SET NOCOUNT ON
-- 1 - Variable declarations
DECLARE @CMD1 varchar(5000)
DECLARE @CMD2 varchar(5000)
DECLARE @FilePath varchar(500)
DECLARE @SQLCmd nvarchar(2500)
DECLARE @DBToRunOn nvarchar(15)
SET @DBToRunOn = 'DBName'
DECLARE @DBAbbr nvarchar(2)
SET @DBAbbr = ''
-- 2 - Create the #OriginalFileList temporary table to support the un-cleansed file list
CREATE TABLE #OriginalFileList (
Col1 varchar(1000) NULL
)
-- 3 - Create the #ParsedFileList temporary table to suppor the cleansed file list
CREATE TABLE #ParsedFileList (
PFLID INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
DateTimeStamp datetime NOT NULL,
LSN int,
FileSize varchar(150) NOT NULL,
FileName1 varchar (255) NOT NULL
)
-- 4 - Initialize the variables
SELECT @CMD1 = ''
SELECT @CMD2 = ''
SELECT @FilePath = '\\backup location'
-- 5 - Build the string to capture the file names in the restore location
SELECT @CMD1 = 'master.dbo.xp_cmdshell ' + char(39) + 'dir ' + @FilePath + '\*.trn' + char(39)
-- 6 - Build the string to populate the #OriginalFileList temporary table
SELECT @CMD2 = 'INSERT INTO #OriginalFileList(Col1)' + char(13) + 'EXEC ' + @CMD1
-- 7 - Execute the string to populate the #OriginalFileList table
EXEC (@CMD2)
-- 8 - Delete unneeded data from the #OriginalFileList
DELETE FROM #OriginalFileList WHERE COL1 IS NULL
DELETE FROM #OriginalFileList WHERE COL1 LIKE '%Volume%'
DELETE FROM #OriginalFileList WHERE COL1 LIKE '%Directory%'
DELETE FROM #OriginalFileList WHERE COL1 LIKE '%<DIR>%'
DELETE FROM #OriginalFileList WHERE COL1 LIKE '%bytes%'
-- 9 - Populate the #ParsedFileList table with the final data
INSERT INTO #ParsedFileList (DateTimeStamp, LSN, FileSize, FileName1)
SELECT LTRIM(SUBSTRING (Col1, 1, 20)) AS 'DateTimeStamp',
LTRIM(SUBSTRING(Col1, 71, 6)) AS 'LSN',
LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',
LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'
FROM #OriginalFileList
ORDER BY LSN
-- ********************************************************************************
-- INSERT code here to process the data from the #ParsedFileList table
DECLARE @Count int, @TotalRecs int, @File varchar(75)
SET @TotalRecs = (SELECT COUNT(1) FROM #ParsedFileList)
SET @Count = 1
WHILE @Count <= @TotalRecs
BEGIN
SET @File = (SELECT FileName1 FROM #ParsedFileList WHERE PFLID = @Count)
PRINT('Processing File ' + CAST(@Count as varchar(2)) + '/' + CAST(@TotalRecs as varchar(2)))
IF @Count = @TotalRecs
BEGIN
SET @SQLCmd = '
RESTORE LOG ['+ RTRIM(@DBToRunOn) + '] FROM DISK = N''\\backup location\' + RTRIM(@File) + '''
WITH RECOVERY, NOUNLOAD, STATS = 10
'
END
ELSE
BEGIN
SET @SQLCmd = '
RESTORE LOG ['+ RTRIM(@DBToRunOn) + '] FROM DISK = N''\\backup location\' + RTRIM(@File) + '''
WITH NORECOVERY, NOUNLOAD, STATS = 10
'
END
EXEC sp_executesql @SQLCmd
SET @count = (@count + 1)
END
-- ********************************************************************************
-- 10 - Drop the temporary tables
DROP TABLE #OriginalFileList
DROP TABLE #ParsedFileList
SET NOCOUNT OFF
GO
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience