Restore all trn files in a folder

  • krypto69

    SSChampion

    Points: 13494

    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?

  • Michael L John

    One Orange Chip

    Points: 25626

    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: 13494

    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: 89718

    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: 25626

    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: 89718

    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: 13494

    Thanks everyone..that worked!

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

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