T-SQL script to automatically restore the transaction logs?

  • Hello Everyone,

    Is there a T-SQL script to automatically restore the transaction logs just like what the log shipping is doing?

    Thank you.

  • You could use powershell and do this in a couple of lines with relative ease

    This is the "guts" of one of the scripts I'm using. (EDIT: There may be some missing parameters as this isnt the full script)

    $Instance =;

    $targetfolder=; ##Source of logfiles

    $result = invoke-sqlcmd -ServerInstance $Instance -Database "MSDB" -Query "select top 1 B.backup_finish_date from msdb.dbo.backupset B join msdb.dbo.restorehistory RH on RH.backup_set_id = B.backup_set_id where b.type = 'L' and rh.destination_database_name = '$Database' order by B.backup_set_id desc"

    $RestoreFrom = ($result.backup_finish_date).AddMinutes(1).Datetime <#Restore files after previous#>

    Get-ChildItem -path $TargetFolder -filter *.trn -recurse | Where-Object {$_.LastWriteTime -ge $lastbackuptime -and !$_.PsIsContainer} | Sort-Object lastwritetime | ForEach-Object {

    $sqlquery = "RESTORE LOG [$Database] from DISK = N'" + $_.fullname + "' WITH FILE = 1, NOUNLOAD, STATS = 10, NORECOVERY";

    #Write-Output $sqlquery

    invoke-sqlcmd -ServerInstance $Instance -Database "MSDB" -Query $sqlquery -QueryTimeout 900;

    }

  • Thank you!

    I am not good in powershell scripting, so most probably I'll do it in T-SQL. The queries in your scripts will be my basis.

  • You can try something like the code below (this is assuming you've already performed any full/diff backups):

    /* LOADS ALL TRN FILES IN A GIVEN DIRECTORY AND THEN RESTORES THEM TO THE APPROPRIATE DATABASE */

    SET NOCOUNT ON

    -- 1 - Variable declarations

    DECLARE @CMD1 varchar(5000)

    DECLARE @CMD2 varchar(5000)

    DECLARE @FilePath varchar(500)

    DECLARE @SQLCmd nvarchar(2500)

    DECLARE @DBToRunOn nvarchar(15)

    SET @DBToRunOn = 'DBName'

    DECLARE @DBAbbr nvarchar(2)

    SET @DBAbbr = ''

    -- 2 - Create the #OriginalFileList temporary table to support the un-cleansed file list

    CREATE TABLE #OriginalFileList (

    Col1 varchar(1000) NULL

    )

    -- 3 - Create the #ParsedFileList temporary table to suppor the cleansed file list

    CREATE TABLE #ParsedFileList (

    PFLID INT PRIMARY KEY IDENTITY (1,1) NOT NULL,

    DateTimeStamp datetime NOT NULL,

    LSN int,

    FileSize varchar(150) NOT NULL,

    FileName1 varchar (255) NOT NULL

    )

    -- 4 - Initialize the variables

    SELECT @CMD1 = ''

    SELECT @CMD2 = ''

    SELECT @FilePath = '\\backup location'

    -- 5 - Build the string to capture the file names in the restore location

    SELECT @CMD1 = 'master.dbo.xp_cmdshell ' + char(39) + 'dir ' + @FilePath + '\*.trn' + char(39)

    -- 6 - Build the string to populate the #OriginalFileList temporary table

    SELECT @CMD2 = 'INSERT INTO #OriginalFileList(Col1)' + char(13) + 'EXEC ' + @CMD1

    -- 7 - Execute the string to populate the #OriginalFileList table

    EXEC (@CMD2)

    -- 8 - Delete unneeded data from the #OriginalFileList

    DELETE FROM #OriginalFileList WHERE COL1 IS NULL

    DELETE FROM #OriginalFileList WHERE COL1 LIKE '%Volume%'

    DELETE FROM #OriginalFileList WHERE COL1 LIKE '%Directory%'

    DELETE FROM #OriginalFileList WHERE COL1 LIKE '%<DIR>%'

    DELETE FROM #OriginalFileList WHERE COL1 LIKE '%bytes%'

    -- 9 - Populate the #ParsedFileList table with the final data

    INSERT INTO #ParsedFileList (DateTimeStamp, LSN, FileSize, FileName1)

    SELECT LTRIM(SUBSTRING (Col1, 1, 20)) AS 'DateTimeStamp',

    LTRIM(SUBSTRING(Col1, 71, 6)) AS 'LSN',

    LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',

    LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'

    FROM #OriginalFileList

    ORDER BY LSN

    -- ********************************************************************************

    -- INSERT code here to process the data from the #ParsedFileList table

    DECLARE @Count int, @TotalRecs int, @File varchar(75)

    SET @TotalRecs = (SELECT COUNT(1) FROM #ParsedFileList)

    SET @Count = 1

    WHILE @Count <= @TotalRecs

    BEGIN

    SET @File = (SELECT FileName1 FROM #ParsedFileList WHERE PFLID = @Count)

    PRINT('Processing File ' + CAST(@Count as varchar(2)) + '/' + CAST(@TotalRecs as varchar(2)))

    IF @Count = @TotalRecs

    BEGIN

    SET @SQLCmd = '

    RESTORE LOG ['+ RTRIM(@DBToRunOn) + '] FROM DISK = N''\\backup location\' + RTRIM(@File) + '''

    WITH RECOVERY, NOUNLOAD, STATS = 10

    '

    END

    ELSE

    BEGIN

    SET @SQLCmd = '

    RESTORE LOG ['+ RTRIM(@DBToRunOn) + '] FROM DISK = N''\\backup location\' + RTRIM(@File) + '''

    WITH NORECOVERY, NOUNLOAD, STATS = 10

    '

    END

    EXEC sp_executesql @SQLCmd

    SET @count = (@count + 1)

    END

    -- ********************************************************************************

    -- 10 - Drop the temporary tables

    DROP TABLE #OriginalFileList

    DROP TABLE #ParsedFileList

    SET NOCOUNT OFF

    GO

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • I've used that TSQL type solution in the past. My problem with it is it uses a substring on the file name and those formats can and do change between versions etc.

    The powershell I put together uses the file timestamp which is much more reliable.

  • Thanks both for your replies.

  • No problem! I sincerely hope the tsql code helps.

    Like yourself, I haven't dove in to the powershell much yet...

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • MyDoggieJessie (9/1/2011)


    No problem! I sincerely hope the tsql code helps.

    Like yourself, I haven't dove in to the powershell much yet...

    No better time to learn!

  • HI,

    I am in need of a script. Which have to pick the latest LSN with sequence automatically and restore it "XXXDB" in no recovery mode. can any one help me out.

  • krishna.vijayawada (2/6/2014)


    HI,

    I am in need of a script. Which have to pick the latest LSN with sequence automatically and restore it "XXXDB" in no recovery mode. can any one help me out.

    You'd probably be better off starting a new thread for that question.

    That said, the scripts posted earlier are pretty good. To restore in norecovery, you just need to make the appropriate adjustment.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • while trying those scripts i am getting some error like the below

    Msg 241, Level 16, State 1, Line 49

    Conversion failed when converting date and/or time from character string.

    currently my LSN's are generating in the below format

    xxxdb_backup_2014_02_06_073328_1849272.trn

    can you send me the script by modifying with my date stamp

  • What is the exact script you are using?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I am using the script which was posted early in this link.. the script is below

    I made only 2 changes

    1. SET @DBToRunOn = 'ab1'

    2. SELECT @FilePath = 'D:\SQL SERVER\TEST\AB1BACKUP\'

    apart from is there any changes are required let me know ?

    /* LOADS ALL TRN FILES IN A GIVEN DIRECTORY AND THEN RESTORES THEM TO THE APPROPRIATE DATABASE */

    SET NOCOUNT ON

    -- 1 - Variable declarations

    DECLARE @CMD1 varchar(5000)

    DECLARE @CMD2 varchar(5000)

    DECLARE @FilePath varchar(500)

    DECLARE @SQLCmd nvarchar(2500)

    DECLARE @DBToRunOn nvarchar(15)

    SET @DBToRunOn = 'DBName'

    DECLARE @DBAbbr nvarchar(2)

    SET @DBAbbr = ''

    -- 2 - Create the #OriginalFileList temporary table to support the un-cleansed file list

    CREATE TABLE #OriginalFileList (

    Col1 varchar(1000) NULL

    )

    -- 3 - Create the #ParsedFileList temporary table to suppor the cleansed file list

    CREATE TABLE #ParsedFileList (

    PFLID INT PRIMARY KEY IDENTITY (1,1) NOT NULL,

    DateTimeStamp datetime NOT NULL,

    LSN int,

    FileSize varchar(150) NOT NULL,

    FileName1 varchar (255) NOT NULL

    )

    -- 4 - Initialize the variables

    SELECT @CMD1 = ''

    SELECT @CMD2 = ''

    SELECT @FilePath = '\\backup location'

    -- 5 - Build the string to capture the file names in the restore location

    SELECT @CMD1 = 'master.dbo.xp_cmdshell ' + char(39) + 'dir ' + @FilePath + '\*.trn' + char(39)

    -- 6 - Build the string to populate the #OriginalFileList temporary table

    SELECT @CMD2 = 'INSERT INTO #OriginalFileList(Col1)' + char(13) + 'EXEC ' + @CMD1

    -- 7 - Execute the string to populate the #OriginalFileList table

    EXEC (@CMD2)

    -- 8 - Delete unneeded data from the #OriginalFileList

    DELETE FROM #OriginalFileList WHERE COL1 IS NULL

    DELETE FROM #OriginalFileList WHERE COL1 LIKE '%Volume%'

    DELETE FROM #OriginalFileList WHERE COL1 LIKE '%Directory%'

    DELETE FROM #OriginalFileList WHERE COL1 LIKE '%<DIR>%'

    DELETE FROM #OriginalFileList WHERE COL1 LIKE '%bytes%'

    -- 9 - Populate the #ParsedFileList table with the final data

    INSERT INTO #ParsedFileList (DateTimeStamp, LSN, FileSize, FileName1)

    SELECT LTRIM(SUBSTRING (Col1, 1, 20)) AS 'DateTimeStamp',

    LTRIM(SUBSTRING(Col1, 71, 6)) AS 'LSN',

    LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',

    LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'

    FROM #OriginalFileList

    ORDER BY LSN

    -- ********************************************************************************

    -- INSERT code here to process the data from the #ParsedFileList table

    DECLARE @Count int, @TotalRecs int, @File varchar(75)

    SET @TotalRecs = (SELECT COUNT(1) FROM #ParsedFileList)

    SET @Count = 1

    WHILE @Count <= @TotalRecs

    BEGIN

    SET @File = (SELECT FileName1 FROM #ParsedFileList WHERE PFLID = @Count)

    PRINT('Processing File ' + CAST(@Count as varchar(2)) + '/' + CAST(@TotalRecs as varchar(2)))

    IF @Count = @TotalRecs

    BEGIN

    SET @SQLCmd = '

    RESTORE LOG ['+ RTRIM(@DBToRunOn) + '] FROM DISK = N''\\backup location\' + RTRIM(@File) + '''

    WITH RECOVERY, NOUNLOAD, STATS = 10

    '

    END

    ELSE

    BEGIN

    SET @SQLCmd = '

    RESTORE LOG ['+ RTRIM(@DBToRunOn) + '] FROM DISK = N''\\backup location\' + RTRIM(@File) + '''

    WITH NORECOVERY, NOUNLOAD, STATS = 10

    '

    END

    EXEC sp_executesql @SQLCmd

    SET @count = (@count + 1)

    END

    -- ********************************************************************************

    -- 10 - Drop the temporary tables

    DROP TABLE #OriginalFileList

    DROP TABLE #ParsedFileList

    SET NOCOUNT OFF

    GO

  • Currently that script is looking for the data to be first

    SUBSTRING (Col1, 1, 20)

    Your backup file format has the date much later in the file name. You will want to find the start position that is common in all of your tlog backups and change the substring statement start point to get the correct date.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • xxxdb_backup_2014_02_06_073328_1849272.trn

    this is the format foll all my backups now

    can you let me know what changes need to done in sub string

    currently it is showing SUBSTRING (Col1, 1, 20)

    to what i have to change ??

    sorry to keep on bother I am not good with all these stuff..:unsure:

Viewing 15 posts - 1 through 15 (of 24 total)

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