SQL SERVER – Alternative of Log Shipping in Simple Recovery Model

The blog topic might seem controversial and doesn’t go well with definition of log shipping features. Read it again. The blog has an alternative way to set up something “similar” to log-shipping. If the database is in simple recovery model, then we can’t take transaction log backups and hence no log shipping possible.

In the script, I am taking last full backup and the last differential backup and restoring them on the destination server. To do this, we need to have a linked server created on the secondary server, which would point to the primary server. Here are the things to be replaced with the script.

1. Shared Path on primary (@BackupPath) – This is the share location which has backup and can be accessed by SQL Server on secondary for restoring purpose.
2. Local path on secondary (@RestorePath) – This is the folder on secondary where the backups would be restored and MDF/LDF would be kept there.
3. Database Name (@database_name) – This is the database name for which we want to configure regular backups and restores.
4. Linked server name – This is the linked server created on secondary server. In below script, it’s called as PrimaryLinkedServer.

DECLARE @LatestDifferentialFileName AS NVARCHAR(255)
DECLARE @LatestFullFileName AS NVARCHAR(255)
DECLARE @RestoreFullCommand AS NVARCHAR(255)
DECLARE @RestoreDiffCommand AS NVARCHAR(255)
DECLARE @RestoreWithRecoveryCommand AS NVARCHAR(255)
DECLARE @database_name AS NVARCHAR(255)
DECLARE @BackupPath AS NVARCHAR(255)
DECLARE @RestorePath AS NVARCHAR(255)
-- parameters
SET @BackupPath = 'BackupPath'
SET @RestorePath = 'RestorePath'
SET @database_name = 'SQLAuthority'
-- Selecting last differential backup filename
SELECT @LatestDifferentialFileName = (
SELECT TOP 1 m.physical_device_name
FROM [Primary].[msdb].dbo.backupset AS b
LEFT JOIN [Primary].[msdb].dbo.backupmediafamily AS m ON b.media_set_id = m.media_set_id
WHERE b.database_name = @database_name
AND type = 'I'
ORDER BY b.backup_start_date DESC
)
-- Selecting last full backup filename
SELECT @LatestFullFileName = (
SELECT TOP 1 m.physical_device_name
FROM [Primary].[msdb].dbo.backupset AS b
LEFT JOIN [Primary].[msdb].dbo.backupmediafamily AS m ON b.media_set_id = m.media_set_id
WHERE b.database_name = @database_name
AND type = 'D'
ORDER BY b.backup_start_date DESC
)
SET @LatestFullFileName = REPLACE(@LatestFullFileName, @BackupPath, @RestorePath)
--PRINT @LatestFullFileName
SET @LatestDifferentialFileName = REPLACE(@LatestDifferentialFileName, @BackupPath, @RestorePath)
--PRINT @LatestDifferentialFileName
SET @RestoreFullCommand = 'RESTORE DATABASE [' + @database_name + '] FROM DISK=''' + @LatestFullFileName + ''' WITH NORECOVERY,REPLACE'
SET @RestoreDiffCommand = 'RESTORE DATABASE [' + @database_name + '] FROM DISK=''' + @LatestDifferentialFileName + ''' WITH NORECOVERY'
SET @RestoreWithRecoveryCommand = 'RESTORE DATABASE [' + @database_name + '] WITH RECOVERY;'
SELECT @RestoreDiffCommand
SELECT @RestoreFullCommand
EXEC (@RestoreFullCommand)
EXEC (@RestoreDiffCommand)
WAITFOR DELAY '00:02';
EXEC (@RestoreWithRecoveryCommand)
GO

Please comment if you find it useful.

Reference: Pinal Dave (https://blog.sqlauthority.com)