Setting up Disconnected Log Shipping

— 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!

Both comments and trackbacks are currently closed.

Trackbacks

%d bloggers like this: