— 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.
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.
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.
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
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!
Session expired
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
[…] via Setting up Disconnected Log Shipping — SQLRx – The Daily Dose […]
This is exactly what I am looking for.
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 ******