How to Restore Multiple Transaction Log backup Files to Database in SQL Server - SQL Server DBA Tutorial

Scenario: 

You are working as SQL Server DBA, you run your full backup jobs and transaction log jobs on different schedules.
Now you need to restore database, you have restored the full backup in NoRecovery mode and need to restore set of log backups. It is really annoying to choose one log backup file at a time and keep restoring.

The below script can be used to restore multiple log backup files to database. You can change the select query to include the log backup files you like to restore.


/*--------------------------------------------------
Restore Multiple transaction log backup files
----------------------------------------------------*/
DECLARE @DBName VARCHAR(500)
DECLARE @LogFilePath VARCHAR(500)

SET @DBName='TestDB'

DECLARE LogR_cursor CURSOR FOR
  --Prepare your list of Log File those needs to be restored.
  -- Uncomment the columns while prepare your List of Log files.
  SELECT
  --BS.database_name,
  BMF.physical_device_name
  --,BS.backup_start_date
  -- ,BS.backup_finish_date
  FROM   msdb.dbo.backupset BS
         INNER JOIN msdb.dbo.backupmediafamily BMF
                 ON BS.media_set_id = BMF.media_set_id
  WHERE  BS.database_name = @DBName
         AND BS.Type = 'L'
  --AND BS.backup_start_date>=''   Provide the StartDate and EndDate To Refine your Selection for Log Files
  --AND BS.backup_finish_date<=''    those you want to restore.
  ORDER  BY backup_start_date,
            backup_finish_date

OPEN LogR_cursor

FETCH NEXT FROM LogR_cursor INTO @LogFilePath

WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SQL NVARCHAR(2000)

      SET @SQL='Restore Log ' + @DBName + ' from Disk='''
               + @LogFilePath + ''' With NORECOVERY'

      PRINT @SQL
      EXEC(@SQL)

      FETCH NEXT FROM LogR_cursor INTO @LogFilePath
  END

--SET DATABASE Mode to Recovery
DECLARE @SQL_SET_RECOVERY NVARCHAR(2000)

SET @SQL_SET_RECOVERY='Restore Database ' + @DBName
                      + ' WITH RECOVERY'

PRINT @SQL_SET_RECOVERY
EXEC(@SQL_SET_RECOVERY)

CLOSE LogR_cursor

DEALLOCATE LogR_cursor

3 comments: