Setting up Disconnected Log Shipping

By Lori Brown | Expert

Sep 15

— by Lori Brown @SQLSupahStah

I have a few clients who have set up SQL instances to hold copies of databases from either vendors or other companies with whom they are actively sharing data with. In these cases, since the source databases are on servers that are part of another company’s domain, the best way to make sure that data is kept up to date is to set up what I call disconnected log shipping. It is disconnected because the primary and secondary SQL instances have no access to each other so setting up regular log shipping will not work.

The owners of the primary instance, in each of the times I have worked on something like this, have set up regular uploads of transaction log backups to an FTP site. My task is to download the .trn files and apply them to the database on our secondary instance. So far, I have had good luck using WinSCP (https://winscp.net/eng/index.php ) and setting up some custom code to create my own log shipping complete with a monitoring job that will alert if logs have not been applied with a specified time. WinSCP is free (but you really should consider donating) and has a very good knowledge base that will show you how to use it fully.

For the record, I know that I can use SQL’s log shipping stored procedures to set up things too but each time I tried, something has gone wrong forcing me to come up with a workaround. For instance, in my latest attempt, the restore job would constantly fail saying that something was NULL that was expecting a date. I don’t have the error anymore so I am doing that one from memory. No matter how I changed things in the log shipping tables, I could not get past the error. After several days of no luck with the SQL log shipping procedures, I took a big step back and decided to just go around it.

After doing some research to see if anyone had posted a better way of setting up and monitoring disconnected log shipping, I found that almost everyone talks about using SQL’s functionality and there is just not much documented that covers how to set up your own solution. So, here is what I did.

WinSCP

First of all, I need to start with a disclaimer that I am NOT a WinSCP scripting expert. I was fortunate at one project where something like this had already been created so I used that for a template. For assistance on WinSCP, please look through their forums and online help.

Download and install WinSCP. I used all defaults and things are working great. Open WinSCP and use it to connect to the FTP site that contains the tlogs and full backups that need to be restored on your SQL instance.

blog_20160915_1

Make sure to put the host key in the cache because you will need it later. You can download your full database backups then so that you will be ready to restore and get log shipping set up.

You will need some folders set up to hold your .trn files, WinSCP scripts and other things. I set up a FTPImport folder and subfolders to hold scripts, archives, the downloaded tlogs and some WinSCP logs too.

blog_20160915_2

You can see that I have two scripts in the Scripts folder. Call_WinSCP.cmd is set up with the path to the WinSCP executable and provides paths to scripts and logs for troubleshooting. In mine I am only giving the path the scripts. I tried to provide comments on each command so that you will know what each line is going to do, so read through things carefully before trying to same in your server. I will eventually set up a job that will call the batch file and tell the batch file to use Get_SQLRXDB_TlogBackups.scp.

Call_WinSCP.cmd

@Echo Off

REM Script to call WinSCP from Current Folder

REM Parameter %1 is name of WinSCP script file to run

REM Parameter %2 is uniquifier for log file names (e.g. date string YYYYMMDDHHmm)

 

Echo Running WinSCP with command Script

 

Date /t

Time /t

 

Echo WinSCP Command /script=”E:\FTPImport\Scripts\%1.scp”.

Echo WinSCP Command /Log=”E:\FTPImport\WinSCPLogs\%2.log”

 

C:\”Program Files (x86)\WinSCP\winscp.com” /script=”E:\FTPImport\Scripts\%1.scp”

 

Date /t

Time /t

 

Get_SQLRXDB_TlogBackups.scp

# WINSCP script file to connect to FTP site and download current T Log Backups

 

# Set Script Options for this transfer

Option echo on

Option batch on

Option confirm off

Option transfer binary

 

# Import Folder for T-Log Backups = E:\FTPImport\TLogs

# Source Folder for T-Log Backups = company.ftpsite.com /LogShipping/SQLRXDB

# Account Name = MyFTPAccount

# Password = $tr0ngPwdH3r3

 

#echo Connect to server

Open sftp://MyFTPAccount:$tr0ngPwdH3r3@company.ftpsite.com -hostkey=”ssh-rsa 2048 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx”

 

#echo change remote working directory to folder with the log backups and list contents

cd LogShipping/SQLRXDB

pwd

 

#echo Change Local working directory to folder for log backups and list contents

lcd E:\FTPImport\TLogs\SQLRXDB

lpwd

 

#echo copy and then delete the T-Log files from remote location

get -delete SQLRXDB_backup_*.trn

 

#echo Close Connection and exit

Close

Exit

Set up Log Shipping

Now that I have WinSCP ready, I just need to do some normal set up for Log Shipping. That means that I have to restore my database, set up a job that will download tlog backup files from the FTP site, set up a job that will restore the downloaded tlog backup files and finally, set up a job that will let someone know if log shipping is out of sync.

First, restore the downloaded backup of the database and leave it in norecovery so that the transaction log backups can be applied to it later.

— Restore database with norecovery

USE [master]

GO

RESTORE DATABASE [SQLRXDB]

FROM DISK = N’E:\FTPImport\FullBackups\SQLRXDB_full_backup.bak’ WITH FILE = 1,

MOVE N’SQLRXDB_Data’ TO N’E:\MSSQL\DATA\SQLRXDB.mdf’,

MOVE N’SQLRXDB_Log’ TO N’F:\MSSQL\LOGS\SQLRXDB_log.ldf’,

NORECOVERY, NOUNLOAD, REPLACE, STATS = 5

GO

Next, set up a “copy” job that is very similar to the one created by Log Shipping stored procedures. The copy job calls WinSCP using a CmdExec step and tells is to use the Get_SQLRXDB_TlogBackups.scp script. The command to call WinSCP is pretty straightforward.

Call E:\FTPImport\Scripts\Call_WinSCP.cmd Get_SQLRXDB_TLogBackups Get_TLogs_WinSCPSession > E:\FTPImport\WinSCPLogs\Call_WinSCP_JobStep.log

And, here is the job that I created. At this client, they want .trn files downloaded every hour.

— Create job to download tlogs from FTP site

USE [msdb]

GO

 

/****** Object: Job [SQLRXDB_GetTLogs]   ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

 

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’SQLRXDB_GetTLogs’,

@enabled=0,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N’Download SQLRXDB tlogs from FTP site’,

@category_name=N’Log Shipping’,

@owner_login_name=N’sa’, @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Get SQLRXDB Tlog Files]   ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Get SQLRXDB Tlog Files’,

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N’CmdExec’,

@command=N’Call E:\FTPImport\Scripts\Call_WinSCP.cmd Get_SQLRXDB_TLogBackups Get_TLogs_WinSCPSession > E:\FTPImport\WinSCPLogs\Call_WinSCP_JobStep.log’,

@output_file_name=N’E:\FTPImport\SQLRXDB_GetTLogs.log’,

@flags=32,

@proxy_name=N’RunCmdExec’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’SQLRXDB_GetTlogs’,

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=8,

@freq_subday_interval=1,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20160813,

@active_end_date=99991231,

@active_start_time=500,

@active_end_time=235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

   IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

 

GO

I have some code that I have been using for a long time to read the contents of a folder and do things with the files it finds. So, in this case I am using the code below to read the files that were downloaded from FTP and make a list so that some dynamic SQL can try to restore those files to the database. I put in some error handling code that will hopefully tell me if a specific file cannot be applied for troubleshooting purposes. In my case, the client wants to use the database for reporting so I am restoring the files and then placing the database in STANDBY. Then as a final thing so that only newer files will be worked on, I move the restored files to an archive folder.

SET NOCOUNT ON

 

— Variable declarations

DECLARE @CMD1 VARCHAR(5000)

DECLARE @CMD2 VARCHAR(5000)

DECLARE @CMD3 NVARCHAR(1000)

DECLARE @FilePath VARCHAR(200)

DECLARE @ArchivePath VARCHAR(200)

DECLARE @UndoFilePath VARCHAR(200)

DECLARE @File2Restore VARCHAR(128)

DECLARE @File2Move VARCHAR(128)

DECLARE @RestoreStmt NVARCHAR(1000)

DECLARE @error_msg VARCHAR(500)

DECLARE @ErrMsg VARCHAR(500))

 

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

CREATE TABLE #OriginalFileList (

Col1 VARCHAR(1000) NULL

)

 

— Create the #ParsedFileList temporary table to support the cleansed file list

CREATE TABLE #ParsedFileList (

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

DateTimeStamp DATETIME NOT NULL,

FileSize VARCHAR(50) NOT NULL,

FileName1 VARCHAR (255) NOT NULL

)

 

— Initialize the variables

SELECT @CMD1 =

SELECT @CMD2 =

SELECT @CMD3 =

SELECT @FilePath = ‘E:\FTPImport\TLogs\SQLRXDB\’

SELECT @ArchivePath = ‘E:\FTPImport\Archives\SQLRXDB\’

SELECT @UndoFilePath = ‘E:\MSSQL\Backup\ROLLBACK_UNDO_SQLRXDB.BAK’

 

— Build the string to capture the file names in the restore location

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

 

— Build the string to populate the #OriginalFileList temporary table

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

 

— Execute the string to populate the #OriginalFileList table

EXEC (@CMD2)

 

— 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%’

 

— Populate the #ParsedFileList table with the final data

INSERT INTO #ParsedFileList (DateTimeStamp, FileSize, FileName1)

SELECT LTRIM(SUBSTRING (Col1, 1, 20)) AS ‘DateTimeStamp’,

LTRIM(SUBSTRING (Col1, 21, 18)) AS ‘FileSize’,

LTRIM(SUBSTRING (Col1, 40, 1000)) AS ‘FileName1’

FROM #OriginalFileList

 

 

DECLARE TLogs2Restore CURSOR FOR

SELECT FileName1 FROM #ParsedFileList ORDER BY FileName1 ASC

 

— Open the cursor

OPEN TLogs2Restore

 

FETCH NEXT FROM TLogs2Restore INTO @File2Restore

 

— Loop through

WHILE (@@FETCH_STATUS = 0) BEGIN

BEGIN TRY

SET @RestoreStmt = ‘RESTORE LOG [SQLRXDB] FROM DISK = N”’+@FilePath+@File2Restore+”‘ WITH STANDBY = ”’+@UndoFilePath+””

–print @RestoreStmt

EXEC sp_executesql @RestoreStmt

END TRY

BEGIN CATCH

SET @error_msg = error_message()

SET @ErrMsg = ‘Log file ‘+@File2Restore+‘ cannot be restored due to error: ‘+@error_msg

PRINT @ErrMsg

END CATCH

FETCH NEXT FROM TLogs2Restore INTO @File2Restore

 

END

 

— Close and deallocate the cursor

CLOSE TLogs2Restore

DEALLOCATE TLogs2Restore

 

 

— Move restored tlogs to archive folder

DECLARE TLogs2Archive CURSOR FOR

SELECT FileName1 FROM #ParsedFileList ORDER BY FileName1 ASC

 

— Open the cursor

OPEN TLogs2Archive

 

— Loop through

WHILE (1=1)

BEGIN

FETCH NEXT FROM TLogs2Archive INTO @File2Move

IF @@FETCH_STATUS < 0 BREAK

 

SET @CMD3 = ‘EXEC master.dbo.xp_cmdshell ”ROBOCOPY.EXE ‘+@FilePath+ ‘+@ArchivePath+’ ‘+@File2Move+‘ /MOV /XO /NP”’

–print @CMD3

EXEC sp_executesql @CMD3

 

END

 

— Close and deallocate the cursor

CLOSE TLogs2Archive

DEALLOCATE TLogs2Archive

 

— Drop the temporary tables

DROP TABLE #OriginalFileList

DROP TABLE #ParsedFileList

 

SET NOCOUNT OFF

GO

And, here is the above code put into a job….

— create job to restore downloaded tlogs and archive the tlogs when done

USE [msdb]

GO

 

/****** Object: Job [SQLRXDB_RestoreTlogs]   ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

 

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’SQLRXDB_RestoreTlogs’,

@enabled=0,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N’No description available.’,

@category_name=N’Log Shipping’,

@owner_login_name=N’sa’, @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Restore]   ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Restore’,

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N’TSQL’,

@command=N’SET NOCOUNT ON

 

— Variable declarations

DECLARE @CMD1 VARCHAR(5000)

DECLARE @CMD2 VARCHAR(5000)

DECLARE @CMD3 NVARCHAR(1000)

DECLARE @FilePath VARCHAR(200)

DECLARE @ArchivePath VARCHAR(200)

DECLARE @UndoFilePath VARCHAR(200)

DECLARE @File2Restore VARCHAR(128)

DECLARE @File2Move VARCHAR(128)

DECLARE @RestoreStmt NVARCHAR(1000)

DECLARE @error_msg VARCHAR(500)

DECLARE @ErrMsg VARCHAR(500))

 

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

CREATE TABLE #OriginalFileList (

Col1 VARCHAR(1000) NULL

)

 

— Create the #ParsedFileList temporary table to support the cleansed file list

CREATE TABLE #ParsedFileList (

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

DateTimeStamp DATETIME NOT NULL,

FileSize VARCHAR(50) NOT NULL,

FileName1 VARCHAR (255) NOT NULL

)

 

— Initialize the variables

SELECT @CMD1 = ””

SELECT @CMD2 = ””

SELECT @CMD3 = ””

SELECT @FilePath = ”E:\FTPImport\TLogs\SQLRXDB\”

SELECT @ArchivePath = ”E:\FTPImport\Archives\SQLRXDB\”

SELECT @UndoFilePath = ”E:\MSSQL\Backup\ROLLBACK_UNDO_SQLRXDB.BAK”

 

— Build the string to capture the file names in the restore location

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

 

— Build the string to populate the #OriginalFileList temporary table

SELECT @CMD2 = ”INSERT INTO #OriginalFileList(Col1)” + char(13) +

”EXEC ” + @CMD1

 

— Execute the string to populate the #OriginalFileList table

EXEC (@CMD2)

 

— 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%”

 

— Populate the #ParsedFileList table with the final data

INSERT INTO #ParsedFileList (DateTimeStamp, FileSize, FileName1)

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

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

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

FROM #OriginalFileList

 

 

DECLARE TLogs2Restore CURSOR FOR

SELECT FileName1 FROM #ParsedFileList ORDER BY FileName1 ASC

 

— Open the cursor

OPEN TLogs2Restore

 

FETCH NEXT FROM TLogs2Restore INTO @File2Restore

 

— Loop through

WHILE (@@FETCH_STATUS = 0) BEGIN

BEGIN TRY

       SET @RestoreStmt = ”RESTORE LOG [SQLRXDB] FROM DISK = N”””+@FilePath+@File2Restore+””” WITH STANDBY = ”””+@UndoFilePath+””””

       –print @RestoreStmt

       EXEC sp_executesql @RestoreStmt

END TRY

BEGIN CATCH

       SET @error_msg = error_message()

       SET @ErrMsg = ”Log file ”+@File2Restore+” cannot be restored due to error: ”+@error_msg

       PRINT @ErrMsg

END CATCH

FETCH NEXT FROM TLogs2Restore INTO @File2Restore

 

END

 

— Close and deallocate the cursor

CLOSE TLogs2Restore

DEALLOCATE TLogs2Restore

 

 

— Move restored tlogs to archive folder

DECLARE TLogs2Archive CURSOR FOR

SELECT FileName1 FROM #ParsedFileList ORDER BY FileName1 ASC

 

— Open the cursor

OPEN TLogs2Archive

 

— Loop through

WHILE (1=1)

BEGIN

       FETCH NEXT FROM TLogs2Archive INTO @File2Move

       IF @@FETCH_STATUS < 0 BREAK

 

       SET @CMD3 = ”EXEC master.dbo.xp_cmdshell ””ROBOCOPY.EXE ”+@FilePath+” ”+@ArchivePath+” ”+@File2Move+” /MOV /XO /NP”””

       –print @CMD3

       EXEC sp_executesql @CMD3

 

END

 

— Close and deallocate the cursor

CLOSE TLogs2Archive

DEALLOCATE TLogs2Archive

 

— Drop the temporary tables

DROP TABLE #OriginalFileList

DROP TABLE #ParsedFileList

 

SET NOCOUNT OFF

GO

 

,

@database_name=N’master’,

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’RestoreSched’,

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=8,

@freq_subday_interval=1,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20160824,

@active_end_date=99991231,

@active_start_time=1500,

@active_end_time=235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

 

GO

The last thing I need is something that will tell me that transaction logs have not been restored. This is supposed to resemble the LS_Alert job that is normally created for Log Shipping. In my client’s case, they wanted to know if data was out of synch by more than 2 hours. Since regular log shipping is not set up and nothing is automatically being logged, I instead am using xp_readerrorlog to find messages that logs have been restored for the target database with a 2 hour window. If it does not find those messages in the logs then an e-mail notification is sent.

— create a homegrown log shipping monitoring job to let you know when it is out of synch

USE [msdb]

GO

 

/****** Object: Job [SQLRXDB LogShipping Monitor]   ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

 

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’SQLRXDB LogShipping Monitor’,

@enabled=0,

@notify_level_eventlog=0,

@notify_level_email=2,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N’Scrapes the SQL log looking for log restored statements. If none found in the past N hours email alert will be sent.’,

@category_name=N’Log Shipping’,

@owner_login_name=N’sa’,

@job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Monitor Log Shipping]   ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Monitor SQLRXDB Log Shipping’,

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N’TSQL’,

@command=N’DECLARE @TimeStart DATETIME;

DECLARE @TimeEnd DATETIME;

DECLARE @CntLogRestoredMsgs INT;

DECLARE @msgstr VARCHAR(500);

SET @TimeStart = DATEADD(hh, -2, GETDATE()); — 2 hours

SET @TimeEnd = GETDATE();

 

CREATE TABLE #sqlerrlog

       (PK_Col int IDENTITY(1,1) PRIMARY KEY

       , LogDate datetime

       , ProcessInfo varchar(15)

       , [Text] varchar(max) );

— read the current sql server log

INSERT INTO #sqlerrlog

       EXEC xp_readerrorlog 0, 1, N”Log was restored”, N”SQLRXDB”, @TimeStart,@TimeEnd;

— read archive #1 sql server log in case of recent restart

INSERT INTO #sqlerrlog

       EXEC xp_readerrorlog 1, 1, N”Log was restored”, N”SQLRXDB”, @TimeStart,@TimeEnd;

 

SELECT @CntLogRestoredMsgs = COUNT(*) FROM #sqlerrlog

 

IF @CntLogRestoredMsgs = 0

BEGIN

       SET @msgstr = ”No transaction logs have been restored for the SQLRXDB database on OurServerName in the past N hours.”

                   + CHAR(10) + ”Investigate as soon as possible!”

 

       EXEC msdb.dbo.sp_send_dbmail

             @profile_name = ”DBMail”,

      @recipients = ”Admins@company.com”,

      @subject =”OurServerName Log Shipping Alert”,

      @body = @msgstr

 

END

 

— cleanup

DROP TABLE #sqlerrlog

 

,

@database_name=N’master’,

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’LSMonitorSchedule’,

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=8,

@freq_subday_interval=1,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20160710,

@active_end_date=99991231,

@active_start_time=80000,

@active_end_time=20001

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

 

GO

And…BADABING!! You now have disconnected log shipping set up. Set off the jobs and work out any path or naming issues. Once I had all pieces going, it has continued to work like a charm! I hope that someone finds this helpful since I could not find anywhere that had all parts of this process documented.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Follow

About the Author

Lori is an avid runner, cross fitter and SQL enthusiast. She has been working for SQLRX for 15 years and has been working with SQL in general for 25 years. Yup...she is an old hand at this stuff.

  • […] via Setting up Disconnected Log Shipping — SQLRx – The Daily Dose […]

  • Andrew Weckermann says:

    This is exactly what I am looking for.

  • Andrew Weckermann says:

    Use the following that makes this a whole lot easier, it can be used to set up log shipping standard and Disconnected log shipping but for the purpose of Disconnected log shipping, I have modified it slightly.

    I recommend that you restore a full copy of the database to the secondary server prior, this job you can define where the full backup is located and it will create a copy job but the best results manually take the full backup of the database you want to implement log shipping for and restore it in Standby mode (This is a SQL Server Enterprise feature) which will be required if you want to access the data in read-only mode. As the copy Job is not required I have modified the script to notify you that the job can be deleted.

    Once the database has been restored in standby mode you can run this script on the secondary server. once you have copied this script into SQL adjust the variables in red, I have commented out what you don’t need to concern yourself with. The script will create a restore job, that utilises the sqllogship.exe located C:Program FilesMicrosoft SQL Server110ToolsBinn. It works like a charm without having to specify the order in which to apply the logs. The script will create 2 other jobs one is a copy job that can be deleted and the second is a job that will alert if the log shipping fails. Schedules will also be created in line with my requirements so feel free to modify the schedules in the script or after they have been created if you want to use SSMS UI.

    DECLARE @LS_Secondary__CopyJobId AS uniqueidentifier
    DECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier
    DECLARE @LS_Secondary__SecondaryId AS uniqueidentifier
    DECLARE @LS_Add_RetCode As int

    EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary
    @primary_server = N’serverinstance’ –If implementing disconnected TLOG shipping Leave as is serverinstance
    ,@primary_database = N’DBNAME’ –Would recomend Full backup is restored in Standby mode before running this script.
    ,@backup_source_directory = N’NOT_REQUIRED_COPYJOB_CAN_BE_DELETEDna’ –Not Required
    ,@backup_destination_directory = N’SERVERSHARE’ –Create a share that points to where the TRN files will be deposited.
    ,@copy_job_name = N’NOT_REQUIRED-COPYJOB_CAN_BE_DELETED’ –This will create a copy job but the job can be deleted after.
    ,@restore_job_name = N’TLOG_RESTORE_JOB_DBNAME’ — This will create the job that will restore the tlog files.
    ,@file_retention_period = 4320
    ,@overwrite = 1 –This sets to Restore in Standby Mode.
    ,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT
    ,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT
    ,@secondary_id = @LS_Secondary__SecondaryId OUTPUT

    IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
    BEGIN

    DECLARE @LS_SecondaryCopyJobScheduleUID As uniqueidentifier
    DECLARE @LS_SecondaryCopyJobScheduleID AS int

    EXEC msdb.dbo.sp_add_schedule
    @schedule_name =N’DefaultCopyJobSchedule’
    ,@enabled = 1
    ,@freq_type = 4
    ,@freq_interval = 1
    ,@freq_subday_type = 4
    ,@freq_subday_interval = 15
    ,@freq_recurrence_factor = 0
    ,@active_start_date = 20191014
    ,@active_end_date = 99991231
    ,@active_start_time = 0
    ,@active_end_time = 235900
    ,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT
    ,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT

    EXEC msdb.dbo.sp_attach_schedule
    @job_id = @LS_Secondary__CopyJobId
    ,@schedule_id = @LS_SecondaryCopyJobScheduleID

    DECLARE @LS_SecondaryRestoreJobScheduleUID As uniqueidentifier
    DECLARE @LS_SecondaryRestoreJobScheduleID AS int

    EXEC msdb.dbo.sp_add_schedule
    @schedule_name =N’DefaultRestoreJobSchedule’
    ,@enabled = 1
    ,@freq_type = 4
    ,@freq_interval = 1
    ,@freq_subday_type = 4
    ,@freq_subday_interval = 15
    ,@freq_recurrence_factor = 0
    ,@active_start_date = 20191014
    ,@active_end_date = 99991231
    ,@active_start_time = 0
    ,@active_end_time = 235900
    ,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT
    ,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT

    EXEC msdb.dbo.sp_attach_schedule
    @job_id = @LS_Secondary__RestoreJobId
    ,@schedule_id = @LS_SecondaryRestoreJobScheduleID

    END

    DECLARE @LS_Add_RetCode2 As int

    IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
    BEGIN

    EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database
    @secondary_database = N’DBNAME’
    ,@primary_server = N’serverinstance’ –Leave as default if configuring Disconnected Tlog Shipping
    ,@primary_database = N’DBNAME’
    ,@restore_delay = 0
    ,@restore_mode = 1
    ,@disconnect_users = 1
    ,@restore_threshold = 45
    ,@threshold_alert_enabled = 1
    ,@history_retention_period = 5760
    ,@overwrite = 1

    END

    IF (@@error = 0 AND @LS_Add_RetCode = 0)
    BEGIN

    EXEC msdb.dbo.sp_update_job
    @job_id = @LS_Secondary__CopyJobId
    ,@enabled = 1

    EXEC msdb.dbo.sp_update_job
    @job_id = @LS_Secondary__RestoreJobId
    ,@enabled = 1

    END

    — ****** End: Script to be run at Secondary ******

  • >