Transaction Log Shipping is a disaster recovery (sometimes called data recovery) option in SQL Server where you generate transaction log backups in the source database, copy them over to a remote SQL Server instance and restore them in read-only, standby mode. This feature is available in Enterprise Edition for SQL Server 2000 while SQL Server 2005 has this feature even in the Standard and Workgroup Editions. But for those who are using editions other than those specified above, they do not have any other options except to do it outside of the “supported” scenarios. It is important to understand what transaction log shipping does so that we can come up with a process which can implement the technology. Notice how I mentioned “process” as this is more important than the technology itself. Transaction log shipping consists of three steps
- Backup the transaction log within a specified interval, say every 15 minutes
- Copy the transaction log backup from the primary server to the standby server
- Restore the copied transaction log backup while setting the database on read-only, standby mode
Understanding these steps will help us create an automated job which involves all of these process. The first thing that you need to do is to create a full backup of the database which you will be configuring for log shipping and restore the backup on the standby SQL Server. Make sure that the restore options for the database should be read-only and standby with no recovery. This makes sure that we can restore additional transaction logs later in the process. After we managed to restore the database backup on the standby server, we are now ready to configure log shipping. Here is a list of what we need for this process:
- ROBOCOPY – this is a command-line file replication tool available in the Windows Resource Kit tools. This copies files from a source to a destination and resumes interrupted replications. It also copies NTFS permissions.
- Shared folder – you should share the folder which will contain the transaction log backups and make sure that you have at least read-only access.
- Domain which will have the primary and standby servers as member servers
- Domain account which has dbo permissions on the database which you will be configuring for log shipping. We will use this account to copy and restore the transaction log backups from the primary server to the standby server.
Now, we’re ready to configure the database for log shipping. On the primary server, the first thing you need to do is create a database maintenance plan that generates transaction log backups. Make sure that you do not have any other transaction log backups running as this will break the log sequence which is necessary to restore the transaction logs. This process is as simple as going through the database maintenance plan wizard to create transaction log backups (unless you have MSDE to configure for log shipping, then, we really need to create a BACKUP LOG script specifically to do this). Next, we share the folder which will contain the transaction log backups. This should be accessible from the standby server, either via IP address or DNS name. On the standby server, copy the robocopy.exe file on a local folder. Then we’ll create a batch file that will call the robocopy.exe utility, passing the corresponding parameters. Let’s call the batch file LogShipping.bat. The batch file will contain the following commands
ROBOCOPY \primary_servershared_folder E:LogShipfolder /COPY:DATSO /MIR
I have discussed ROBOCOPY in more detail in this blog post. This line will be responsible for copying the transaction log backups from the primary server to the standby server and should be run in the standby server. Next, we add another line on the batch file to call a VBScript code which will be responsible for restoring the transaction log backups on the standby server based on time stamps. I have created a VBScript which accepts two parameters; the folder on which the transaction log backups were copied and the name of the database. Here is the command to call the VBScript which we will insert after the ROBOCOPY command.
RESTORE_LOG.vbs E:LogShipFolder databaseName
The contents of the RESTORE_LOG.vbs script is shown below
‘This script does a custom log shipping job using ROBOCOPY and VBScript
‘With FileSystemObject querying the sopecified folder for files created
‘within the past 15 minutes or less and generates a TSQL RESTORE LOG command
‘which is executed after the ROBOCOPY script
‘Syntax: RESTORE_LOG.vbs folder databaseName
On Error Resume Next
Dim fso, folder, files, sFolder, sFolderTarget, strParentFolder, strDatabaseName
Dim objShell
Set fso = CreateObject(“Scripting.FileSystemObject”)
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
strParentFolder=Wscript.Arguments.Item(0)
strDatabaseName=Wscript.Arguments.Item(1)
sFolder = strParentFolder & strDatabaseName
Set folder = fso.GetFolder(sFolder)
Set files = folder.Files
SET objShell = CreateObject(“Wscript.Shell”)
For each itemFiles In files
a=sFolder & “” & itemFiles.Name
‘retrieve file extension
b = fso.GetExtensionName(a)
‘check if the file extension is TRN
If uCase(b)=”TRN” Then
‘check for DateCreated attribute of file and compare with current date/time
If (DateDiff(“N”, itemFiles.DateCreated, Now) <=15) Then ‘Create the file to contain the script If (objFSO.FileExists(“E:LogShipFolderscriptsSQL” & strDatabaseName & “.sql”)) Then objFSO.DeleteFile (“E:LogShipFolderscriptsSQL” & strDatabaseName & “.sql”) End If Set objMyFile = objFSO.CreateTextFile(“E:LogShipFolderscriptsSQL” & strDatabaseName & “.sql”, True) str1=”RESTORE LOG ” & strDatabaseName str2=”FROM DISK='” & a & “‘” str3=”WITH STANDBY=’E:LogShipFolderUNDOUNDO_” & strDatabaseName & “_ARCHIVE.DAT’,” str4=”DBO_ONLY” objMyFile.WriteLine (str1) objMyFile.WriteLine (str2) objMyFile.WriteLine (str3) objMyFile.WriteLine (str4) objMyFile.Close Set objFSO = Nothing Set objMyFile = Nothing ‘Run an OSQL command that uses a RESTORE LOG WITH MOVE, STANDBY objShell.Run(“osql -SinstanceName -E -iE:LogShipFolderscriptsSQL” & strDatabaseName & “.sql -oE:LogShipFolderscriptsSQL” & strDatabaseName & “_results.txt”) End If End If Next
objFile.Close
SET objFile = NOTHING
SET fso = NOTHING
SET folder = NOTHING
SET files = NOTHING
SET objShell = NOTHING
SET objFSO = NOTHING
SET objMyFile = NOTHING
The script is self-explanatory with all the comments. I am passing the folder location where the transaction log backups are being copied into and the database name. Notice that I usually structure my file system in such a way as to keep everything in order:
- E:LogShipFolder – location of the transaction log backups, separated by database name
- E:LogShipFolderUNDO – location of undo files which I specify as part of the RESTORE LOG command. Each UNDO file is identified by the database name as it’s prefix
- E:LogShipFolderscript – location of all the VBScripts, batch files and EXE files I am using for this process. The RESTORE_LOG.vbs is stored in this folder
- E:LogShipFolderscriptSQL – location of the SQL script files which RESTORE_LOG.vbs generates containing the RESTORE LOG command. This SQL script file will be called by a command-line command containing osql.exe (or sqlcmd.exe for SQL Server 2005), the command-line utility for SQL Server. This will also store the results file which I am generating using the osql.exe command-line utility. The results file is simply for records and/or troubleshooting purposes
After creating this VBScript and adding a line in the batch file to call this script, you are now ready to create a Scheduled Task in Windows to automate this process (although you can also do this in SQL Server Agent as a job). Create a Scheduled Task in Windows which will call the LogShipping.bat batch file. Now this will be very tricky. You need to make sure that the batch file will execute after the time it takes to generate the transaction log backup in the primary server but before the new one starts. We do have to monitor this after implementation as the transaction log backup time in the primary server may increase due to increased transaction which means increased file size resulting to increased file transfer time. To illustrate, if we enabled transaction log backup in the primary server to run every 15 minutes starting from 12:00AM, the log backups will be generated in the 15-minute sequence (12:00AM, 12:15AM, 12:30AM, etc.) Now, on the standby server, the scheduled task to call the LogShipping.bat batch file should be scheduled to run every 15 minutes but after 12:00AM. To be safe, it should be in the two-thirds of the time, say 12:10AM. This would be ample time for a medium-sized database with average-to-high number of transactions to generate the log backups. Five minutes to copy the log backups from the primary to the standby and restoring them would probably be a trial-end-error process to find the appropriate timing. So, when the log backup job runs on the primary at 12:00AM, your Scheduled Task on the standby server should be scheduled to execute at 12:10AM. When you create the Scheduled Task, make sure that the domain account that you will use has the appropriate rights to copy from the shared folder and dbo privileges to restore the log backups. You also need to take into account password changes in the acount as this will cause this job to fail.
That’s about it. You have successfully implemented the “unsupported” transaction log shipping which you can do in all editions of SQL Server. But since it is “unsupported,” you do not have the benefits of easy-to-use features like Log Shipping Monitor, Threshold Settings, etc. You also need to worry about the “what-ifs” time constraints, like what if the transaction log backup job for one database took more than 15 minutes to complete due to large transactions (and of course the corresponding copy job will also take longer.) The Log Shipping Monitor can be resolved by using a TSQL query to retrieve the successful restore for a specified database. But, of course, you need to write your own script to do this. A sample script to query the MSDB database for successful restores is given below:
SELECT [Name], Backup_Start_Date, Backup_Finish_Date, [Description], First_LSN, Last_LSN, *
FROM msdb.dbo.backupset
AS a JOIN msdb.dbo.backupmediafamily AS b ON a.media_set_id = b.media_set_id WHERE database_name = ‘databaseName’
ORDER BY 2 DESC
GO
