August 30, 2011 at 12:19 am
Hello Everyone,
Is there a T-SQL script to automatically restore the transaction logs just like what the log shipping is doing?
Thank you.
August 30, 2011 at 2:27 am
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;
}
August 30, 2011 at 6:20 pm
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.
September 1, 2011 at 1:46 am
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
September 1, 2011 at 2:20 am
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.
September 1, 2011 at 5:56 pm
Thanks both for your replies.
September 1, 2011 at 6:03 pm
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
September 2, 2011 at 2:18 am
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!
February 6, 2014 at 9:44 pm
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.
February 6, 2014 at 9:49 pm
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
February 6, 2014 at 9:54 pm
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
February 6, 2014 at 9:55 pm
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
February 6, 2014 at 9:59 pm
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
February 6, 2014 at 10:05 pm
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
February 6, 2014 at 10:12 pm
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