Restore all trn files in a folder

Question

Restore all trn files in a folder

krypto69

SSChampion

Points: 13591
More actions
September 14, 2016 at 12:10 pm

#321470

HI,
I used to have a script that would do this but can't fine it anywhere.
Using A\G groups and need to manually replay several transaction files (all files in a directory).
Anyone know of a script that will restore (with no recovery) every trn file in a folder?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27038 More actions · Answer 1

How about this?

http://www.sqlservercentral.com/articles/Restore+Genie/145380/"> http://www.sqlservercentral.com/articles/Restore+Genie/145380/

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

krypto69 SSChampion Points: 13591 More actions · Answer 2

Thanks, but I'm looking for somthing similar to this..(but I can't this working - nothing prints)

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 = 'pcm_epic'

SET @backupPath = 'H:\tlogs\'

-- 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 '%.BAK'

-- 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'

AND backupFile LIKE @dbName + '%'

AND backupFile > @lastFullBackup

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

Sue_H SSC Guru Points: 90860 More actions · Answer 3

The script looks like it works - I was just messing with it.

Try changing your PRINTs to SELECTs - i.e. SELECT @cmd

Sue

Michael L John One Orange Chip Points: 27038 More actions · Answer 4

Concat null yields null.

One of the variables you are concatenating is null. So, you get nothing.

Add some print statements after you initialize each variable, and see what is in them.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Sue_H SSC Guru Points: 90860 More actions · Answer 5

Michael L John (9/14/2016)
Concat null yields null.
One of the variables you are concatenating is null. So, you get nothing.
Add some print statements after you initialize each variable, and see what is in them.

Looked like the buffering issue with PRINTs to me. Remove the commented out sections and nothing is returned.

Change the Prints to Selects and all of the original Print statements that are now selects showed the commands. Nothing was null and all of the correct commands were displayed.

Sue

krypto69 SSChampion Points: 13591 More actions · Answer 6

krypto69

SSChampion

Points: 13591

September 15, 2016 at 8:38 am

#1901124

Thanks everyone..that worked!