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.
- 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.
- Local path on secondary (@RestorePath) – This is the folder on secondary where the backups would be restored and MDF/LDF would be kept there.
- Database Name (@database_name) – This is the database name for which we want to configure regular backups and restores.
- 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)
3 Comments. Leave new
Nice one! This should be called Differential shipping ;)
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).
Hi Adam .. Please configure backup with COPY_ONLY option…this will make sure without break the chain of LogShipping