SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL script to automatically restore the transaction logs?


T-SQL script to automatically restore the transaction logs?

Author
Message
smile_netz
smile_netz
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 131
Hello Everyone,

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

Thank you.
MysteryJimbo
MysteryJimbo
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12069 Visits: 15346
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;
}


smile_netz
smile_netz
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 131
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.
MyDoggieJessie
MyDoggieJessie
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24510 Visits: 7463
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; They'll drag you down to their level and beat you with experience" ;-)
MysteryJimbo
MysteryJimbo
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12069 Visits: 15346
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.
smile_netz
smile_netz
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 131
Thanks both for your replies.
MyDoggieJessie
MyDoggieJessie
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24510 Visits: 7463
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; They'll drag you down to their level and beat you with experience" ;-)
MysteryJimbo
MysteryJimbo
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12069 Visits: 15346
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!
krishna.vijayawada
krishna.vijayawada
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 57
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.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)

Group: General Forum Members
Points: 132020 Visits: 18634
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search