In a log shipping scenario, it is not enough to just assume that files can be processed in the sequence of date created. As log shipping is a mission critical process, you must ensure guarantee that the correct logs are being applied so that your process is reliable. (This article addresses only recording the LSNs — so this is not my entire process)
To ensure this, you should be aware of the last LSN applied and the next logfile to apply.
I used the following tables to achieve this.
-- www: dacosta9.wordpress.com
-- author: john dacosta
IF NOT EXISTS(SELECT 1 FROM dbo.sysobjects where name LIKE 'transactionLogStage')
BEGIN
CREATE TABLE dbo.transactionLogStage(
serverName VARCHAR(100) NOT NULL
,databaseName VARCHAR(100) NOT NULL
,filePath VARCHAR(500) NOT NULL
,DateLastModified DATETIME NULL
)
END
GO
IF NOT EXISTS(SELECT 1 FROM dbo.sysobjects where name LIKE 'transactionLog')
BEGIN
CREATE TABLE dbo.transactionLog(
id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,serverName VARCHAR(100) NOT NULL
,databaseName VARCHAR(100) NOT NULL
,filePath VARCHAR(500) NOT NULL
,DateLastModified DATETIME NULL
,firstLSN NUMERIC(32,0) NULL
,lastLSN NUMERIC(32,0) NULL
,dtStamp DATETIME DEFAULT(getdate()) NOT NULL
)
END
GO
The first step is to copy across files in from the source filesystem so that you have them on your target server.
I used vbscript to achieve this. This can be done a few other ways, but this works in SQL Server 2000+ environments, where you may not have dotnet framework installed as yet.
'www: dacosta9.wordpress.com
'author: john dacosta
'filename: copyMyDBToLocal.vbs
' run this code via the following code from the command line:
' cscript.exe //NoLogo copyMyDBToLocal.vbs
'description: Copy Transaction Log Backups for disaster recovery Purposes.
'Copy Transaction Log Backups
SourceServerName = "myProductionServer"
DestinationServerName = "myDisasterRecoveryServer"
databaseName = "mydb"
srcFolder = "\\" & SourceServerName & "\backup"
destFolder = "\\" & DestinationServerName & "\BACKUP"
archiveFolder = "\\" & DestinationServerName & "\BACKUP\ArchivedTransactionLogs"
Set ofs = CreateObject("Scripting.FileSystemObject")
Set ofolder = ofs.getFolder(srcFolder)
Set ofiles = ofolder.Files
' Regex is to ensure that we only copy transaction log files for the database chosen. modify regex as needed
Set re = new RegExp
re.pattern = ".*" & databaseName & ".*\.log"
re.IgnoreCase = True
for each ofile in ofiles
sf = srcFolder & "\" & ofile.Name
df = destFolder & "\" & ofile.Name
af = archiveFolder & "\" & ofile.Name
exdf = 0
exaf = 0
iswanted = 100
if(re.test(ofile.Name) ) Then
iswanted = 0
End If
' test if file exists in archive folder
if (ofs.FileExists(df)) Then
exdf = 1
'wscript.echo ofile.name & " exists in the " & destFolder
End If
' test if file exists in processing folder
if (ofs.FileExists(af)) Then
exaf = 1
'wscript.echo ofile.name & " exists in the " & archiveFolder
End If
' if file is not in current folder and not in archive folder
if ( (exdf + exaf + iswanted) = 0 ) Then
ofs.copyFile sf,df
'wscript.echo "Copying file " & ofile.name
End If
next
Set ofile = Nothing
Set ofiles = Nothing
Set ofs = Nothing
Set re = Nothing
The second step is to check each file and record the log sequence numbers.
' www: dacosta9.wordpress.com
' author: john dacosta
' Get Dir List in the appropriate folder and insert into table.
' Use filesystem object
SourceServerName = "myProductionServer"
DestinationServerName = "myDisasterRecoveryServer"
databaseName = "mydb"
implementationDatabase = "master"
Dim ofso, ofolder, ofile, ofiles, srcFolder, sql
srcFolder = "\\" & DestinationServerName & "\BACKUP"
insertScriptName = "insertNewTransactionLogs.txt"
bulkInsertDataFileName = "NewTransactionLogs.txt"
Set oRegExpPass = new RegExp
re.pattern = ".*" & databaseName & ".*\.log"
oRegExpPass.IgnoreCase = True
oRegExpPass.Global = False
dtString = CStr(datePart("yyyy",currDate)) + _
Right("00" + CStr(datePart("m",currDate)),2) + _
Right("00" + CStr(datePart("d",currDate)),2) + "T" +_
Right("00" + CStr(datePart("h",currDate)),2) + _
Right("00" + CStr(datePart("n",currDate)),2) + "s"+ _
Right("00" + CStr(datePart("s",currDate)),2)
Set ofso = CreateObject("Scripting.FileSystemObject")
Set ofolder = ofso.getFolder(srcFolder)
Set ofiles = ofolder.Files
Set sh = CreateObject("WScript.Shell")
Set oInsertScript = ofso.CreateTextFile(srcFolder & "\" & insertScriptName , True, False)
Set oDataFile = ofso.CreateTextFile(srcFolder & "\" & bulkInsertDataFileName , True, False)
' log files in staging table
For Each ofile in ofiles
' Insert into Stage Table
sql = "INSERT INTO dbo.transactionLogStage(serverName,databaseName,filePath,DateLastModified) VALUES('" & myProductionServer & "','" & mydb & "', '" & ofile.Path & "','" & CStr(ofile.DateLastModified) & "' )"
'cmd = "osql -S " & DestinationServerName & " -d " & implementationDatabase & " -Q" & Chr(34) & sql & Chr(34) & " -E -n "
data = SourceServerName & "," & databaseName & "," & ofile.Path & "','" & CStr(ofile.DateLastModified) & "'
'sh.run cmd , 7 , TRUE
oInsertScript.WriteLine sql & vbcrlf & "GO" & vbcrlf
oDataFile.WriteLine data
'wscript.echo sql
Next
oInsertScript.close
oDataFile.close
Set oInsertScript = Nothing
Set oDataFile = Nothing
' 2 methods here. individual insert or bulk insert... you choose.
' Insert into Stage Table
cmd = "osql -S " & DestinationServerName & " -d " & implementationDatabase & " -i" & Chr(34) & srcFolder & "\" & insertScriptName & Chr(34) & " -E -n "
sh.run cmd , 7 , TRUE
'bulk insert method.
cmd = "bcp " & Chr(34) & implementationDatabase & ".dbo.transactionLogStage" & Chr(34) &" in " & Chr(34) & srcFolder & "\" & bulkInsertDataFileName & Chr(34) & " -F 0 -w -S " & DestinationServerName & " -T -n "
'sh.run cmd , 7 , TRUE
' Insert into Final Table
sql = "INSERT INTO dbo.transactionLog(serverName,databaseName,filePath,DateLastModified) SELECT s.serverName,s.databaseName,s.filePath,s.DateLastModified FROM dbo.transactionLogStage s LEFT OUTER JOIN dbo.transactionLog p ON (s.serverName=p.serverName AND s.databaseName=p.databaseName AND s.filePath=p.filePath ) WHERE p.id IS NULL"
cmd = "osql -S " & DestinationServerName & " -d " & implementationDatabase & " -Q" & Chr(34) & sql & Chr(34) & " -E -n "
sh.run cmd , 7 , TRUE
' Truncate Stage Table
sql = "TRUNCATE TABLE transactionLogStage"
cmd = "osql -S " & DestinationServerName & " -d " & implementationDatabase & " -Q" & Chr(34) & sql & Chr(34) & " -E -n "
sh.run cmd , 7 , TRUE
Set sh = Nothing
Set oRegExpPass = Nothing
Set ofiles = Nothing
Set ofolder = Nothing
Set ofso = Nothing
Finally having added the files to the log folder, the next step is to update the LSN in the table.
use master
go
DECLARE @id BIGINT
DECLARE @serverName VARCHAR(100)
DECLARE @databaseName VARCHAR(100)
DECLARE @filePath VARCHAR(500)
CREATE TABLE #backupfile_header(
BackupName nvarchar(128), BackupDescription nvarchar(255), BackupType smallint, ExpirationDate datetime, Compressed tinyint, Position smallint, DeviceType tinyint,
UserName nvarchar(128), ServerName nvarchar(128), DatabaseName nvarchar(128), DatabaseVersion int, DatabaseCreationDate datetime, BackupSize numeric(20,0),
FirstLSN numeric(25,0), LastLSN numeric(25,0), CheckpointLSN numeric(25,0), DatabaseBackupLSN numeric(25,0), BackupStartDate datetime, BackupFinishDate datetime,
SortOrder smallint, CodePage smallint, UnicodeLocaleId int, UnicodeComparisonStyle int, CompatibilityLevel tinyint, SoftwareVendorId int, SoftwareVersionMajor int,
SoftwareVersionMinor int, SoftwareVersionBuild int, MachineName nvarchar(128), Flags int, BindingID uniqueidentifier, RecoveryForkID uniqueidentifier,Collation nvarchar(128))
DECLARE c_cursorfiles CURSOR
FOR
SELECT
[id]
,[serverName]
,[databaseName]
,[filePath]
FROM
[master].[dbo].[transactionLog]
WHERE
[firstLSN] IS NULL OR [lastLSN] IS NULL
ORDER BY
[DateLastModified] ASC
FOR READ ONLY
OPEN c_cursorfiles
FETCH NEXT FROM c_cursorfiles INTO @id,@serverName,@databaseName,@filePath
--' Process Each Log file
WHILE(@@FETCH_STATUS = 0)
BEGIN
insert #backupfile_header exec ('restore headeronly from disk = ''' + @filepath + ''' ' )
--PRINT @filepath
--'update real table with the lsn
UPDATE dbo.transactionLog
SET FirstLSN = bh.FirstLSN , LastLSN = bh.LastLSN
FROM #backupfile_header bh
WHERE [id] = @id
--'truncate the temp table for the next load.
TRUNCATE TABLE #backupfile_header
FETCH NEXT FROM c_cursorfiles INTO @id,@serverName,@databaseName,@filePath
END
CLOSE c_cursorfiles
DEALLOCATE c_cursorfiles
DROP TABLE #backupfile_header
--SELECT * from master.dbo.transactionlog WHERE firstLSN IS NOT NULL
The rest is up to you.
Data Analytics & Technology by John DaCosta 