Sql-server – How to restore a batch of transaction logs rather than one by one

sql serversql-server-2008ssms

I have a SQL Server database that backs up the transaction logs every 10 minutes, with an overnight full backup.

Using SQL 2008 Management studio we seem to have to select each transaction log one by one. Is there any way to point it at a directory?

I am considering running a differential backup several times a day, which may offset some of this, but going one by one through dozens/hundreds of files seems to be pretty time consuming. Writing code to try to script it seems far too off topic from our core competencies.

If SQL Server Management Studio does not have a faster way, perhaps there is a third party tool available?

Best Answer

There is no ways to specify bunch of transaction log backups (oк folder) to restore in SQL Server Management studio.

But you can find all information about SQL Server backup operations in database MSDB (table backupset and related).

Here is script to generate SQL Server commands for restore database from backup and applying all transaction logs backups performed from last full database backup. I think it should help you.

DECLARE @databaseName sysname
DECLARE @backupStartDate datetime
DECLARE @backup_set_id_start INT
DECLARE @backup_set_id_end INT

-- set database to be used
SET @databaseName = '<your_database_name_here>' 

SELECT @backup_set_id_start = MAX(backup_set_id) 
FROM  msdb.dbo.backupset 
WHERE database_name = @databaseName AND type = 'D'

SELECT @backup_set_id_end = MIN(backup_set_id) 
FROM  msdb.dbo.backupset 
WHERE database_name = @databaseName AND type = 'D'
AND backup_set_id > @backup_set_id_start

IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999

SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = ''' 
               + mf.physical_device_name + ''' WITH NORECOVERY'
FROM    msdb.dbo.backupset b,
           msdb.dbo.backupmediafamily mf
WHERE    b.media_set_id = mf.media_set_id
           AND b.database_name = @databaseName
          AND b.backup_set_id = @backup_set_id_start
UNION
SELECT backup_set_id, 'RESTORE LOG ' + @databaseName + ' FROM DISK = ''' 
               + mf.physical_device_name + ''' WITH NORECOVERY'
FROM    msdb.dbo.backupset b,
           msdb.dbo.backupmediafamily mf
WHERE    b.media_set_id = mf.media_set_id
           AND b.database_name = @databaseName
          AND b.backup_set_id >= @backup_set_id_start AND b.backup_set_id < @backup_set_id_end
          AND b.type = 'L'
UNION
SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY'
ORDER BY backup_set_id
Related Topic