The following script will generate output script with restore command to restore all log backup located at L:\Log_Backup\DB_Name\
exec sp_configure 'show advanced options',1
go
reconfigure with override
go
exec sp_configure 'xp_cmdshell',1
go
reconfigure with override
go
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
-- 2 - Initialize variables
SET @dbName = 'barneys_prod_md9_1'
SET @backupPath = 'L:\Log_Backup\DB_Name\'
-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd
-- 4 - Find latest full backup
--SELECT @lastFullBackup = MAX(backupFile)
--FROM @fileList
--WHERE backupFile LIKE '%.trn'
--AND backupFile LIKE @dbName + '%'
--SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
--+ @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE'
--PRINT @cmd
-- 4 - Find latest diff backup
--SELECT @lastDiffBackup = MAX(backupFile)
--FROM @fileList
--WHERE backupFile LIKE '%.DIF'
--AND backupFile LIKE @dbName + '%'
--AND backupFile > @lastFullBackup
-- check to make sure there is a diff backup
--IF @lastDiffBackup IS NOT NULL
--BEGIN
--SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
--+ @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'
-- PRINT @cmd
-- SET @lastFullBackup = @lastDiffBackup
--END
-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
SELECT backupFile
FROM @fileList
WHERE backupFile LIKE '%.TRN'
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''
+ @backupPath + @backupFile + ''' WITH NORECOVERY'
PRINT @cmd
FETCH NEXT FROM backupFiles INTO @backupFile
END
CLOSE backupFiles
DEALLOCATE backupFiles
-- 6 - put database in a useable state
--SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'
--PRINT @cmd
Comments