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.

SQL SERVER - Alternative of Log Shipping in Simple Recovery Model logshipping

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)

, , , ,
Previous Post
SQL Server – How to Get Column Names From a Specific Table?
Next Post
SQL SERVER – MSDB Database Uncontrolled Growth Due to Queue_messages. How to Clear All Messages From a Queue?

Related Posts

3 Comments. Leave new

  • Nice one! This should be called Differential shipping ;)

    Reply
  • Hello. I have two servers SQL2016 with LogShipping. How can I configure backup of database on Primary Server without break the chain of LogShipping? I need the daily full backup of database and every hour backup of logs (this is important high availability database in my company).

    Reply
    • Hi Adam .. Please configure backup with COPY_ONLY option…this will make sure without break the chain of LogShipping

      Reply

Leave a Reply

Menu