5

I'm using SQL Server 2008 R2, and I need to restore 36 .trn logs to a database. I have restored the first 4 using the restore transaction log functionality in SSMS (right-click database > tasks > restore > transaction log).

Is there a way to restore them all in order using this tool or with a script? The .trn files are in a folder on a remote server. I do not not need to restore all the files in the folder, only ones between specific dates.

I'm looking for a way to automate this so I don't have to manually restore each individual log.

I access the files using an address like the one below.

\\remote server IP\SQL_Backup\LMSQL02\TLog\file.trn

  • 1
    Yes, use RESTORE LOG ..., not the GUI. If you give more information about how you identify the 36 file names, we can give you some ideas about how to generate the scripts. – Aaron Bertrand Jul 17 '15 at 18:35
  • You can add them all in the GUI, you dont need to click restore for each log. Or script out the restore and add the files remembering to end each restore statement with 'with no recovery' – Spörri Jul 18 '15 at 9:07
5

I would suggest to use Restore Gene : Automating SQL Server Database Restores - written by Paul Brewer

The Restore Gene stored procedure generates the required restore scripts, including the DBCC CHECKDB command, if required. It is a useful tool, by itself; in disaster recovery situations, it can construct a restore script, in seconds. It optionally includes override options for name of the target database and WITH MOVE overrides for data, log file locations, which might help recover lost data by restoring a temporary version of a database to the same instance.

It has both TSQL and PowerShell versions for automation.

1

This uses xp_cmdshell to look at the file system for the log files. If you're OK with that....

DECLARE @databaseName AS VARCHAR(100)
DECLARE @restoreName AS VARCHAR(100)
SET @databaseName = 'OriginalDBName' 
SET @restoreName = 'RestoredDBName' 

CREATE TABLE #dirList (
  id int identity(1,1),
  line nvarchar(1000)
  )

DECLARE @cmdShellStatement VARCHAR(2000)
--Modify this file path if the backup files are not located in the standard backup directory.
SET @cmdShellStatement = 'DIR "C:\Program'+' '+'Files\Microsoft'+' '+'SQL'+'     '+'Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\' + @databaseName + '\"'
INSERT INTO #dirList (line) EXEC master..xp_cmdshell @cmdShellStatement
SELECT SUBSTRING(line,0,21) AS 'DateTime'
--Modify this file path if the backup files are not located in the standard backup directory.
,'RESTORE DATABASE ' + @restoreName + ' FROM DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\' + @databaseName     + '\' +   SUBSTRING(line,40,100) + ''' WITH REPLACE, NORECOVERY' as 'Script'
FROM #dirList
WHERE line like '%.bak'
ORDER BY CAST(SUBSTRING(line,0,21) AS DateTime)

SELECT SUBSTRING(line,0,21) AS 'DateTime'
--Modify this file path if the backup files are not located in the standard backup directory.
,'RESTORE LOG ' + @restoreName + ' FROM DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\' + @databaseName + '\' +   SUBSTRING(line,40,100) + ''' WITH NORECOVERY' as 'Script'
FROM #dirList
WHERE line like '%.trn'
ORDER BY CAST(SUBSTRING(line,0,21) AS DateTime)

DROP TABLE #dirList
  • The does not have a restore ...with move option. If the db directory path is different, then the script will fail. – Kin Shah Jul 17 '15 at 19:17
  • @Kin why would you need with move? You're restoring a transaction log, not a log file. – Aaron Bertrand Jul 17 '15 at 19:19
  • @AaronBertrand I meant when restoring the database with norecovery - RESTORE DATABASE ' + @restoreName. – Kin Shah Jul 17 '15 at 19:32
  • @Kin ah, ok, the OP doesn't say they need to restore a database, just apply logs. – Aaron Bertrand Jul 17 '15 at 19:45
  • 1
    @tpet I just mentioned it for completeness as people will come across your script and try to use it and then post a question here only saying it is not working :-) – Kin Shah Jul 17 '15 at 21:46
0

We have the below script handy when have a scenario to restore multiple tran logs in sequence during an issue from here

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

Also, would suggest you to read TRANSACTION LOG BACKUP and RESTORE SEQUENCE: Myths & Truths

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.