All we need is an easy explanation of the problem, so here it is.
The plan is to have 1 backup set that will contain full and transactional log backup. (Server1)
There is already a scheduled job that will append any transactional log to the file after 30 minutes. (Server1)
There is a shared network drive between Server1 and Server2. But the problem is automating it via T-SQL. Here is the script:
RESTORE LOG [db_test_rep] FROM DISK = N'Z:\db_rep.bak' WITH FILE = 19, NORECOVERY, NOUNLOAD, STATS = 5
The issue with this is that everytime the scheduled job executes, it will update the backup file numbering + 1. So in this case on the next transaction log backup, when I script into action the restoration using SSMS, it will generate FILE = 20
Can you guys give me a simple workaround for this?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Maybe something like this:
Get the latest FILE number by using RESTORE FILELISTONLY:
USE master GO IF OBJECT_ID('tempdb..#BackupMetadata') IS NOT NULL DROP TABLE #BackupMetadata CREATE TABLE #BackupMetadata (LogicalName NVARCHAR(500) ,PhysicalName NVARCHAR(500) ,Type NVARCHAR(500) ,FileGroupName NVARCHAR(500) ,Size NVARCHAR(500) ,MaxSize NVARCHAR(500) ,FileId NVARCHAR(500) ,CreateLSN NVARCHAR(500) ,DropLSN NVARCHAR(500) ,UniqueId NVARCHAR(500) ,ReadOnlyLSN NVARCHAR(500) ,ReadWriteLSN NVARCHAR(500) ,BackupSizeInBytes NVARCHAR(500) ,SourceBlockSize NVARCHAR(500) ,FileGroupID NVARCHAR(500) ,LogGroupGUID NVARCHAR(500) ,DifferentialBaseLSN NVARCHAR(500) ,DifferentialBaseGUID NVARCHAR(500) ,IsReadOnly NVARCHAR(500) ,IsPresent NVARCHAR(500) ,TDEThumbprint NVARCHAR(500) ) INSERT INTO #BackupMetadata EXEC('RESTORE FILELISTONLY FROM DISK = N''Z:\db_rep.bak''') DECLARE @ID INT SELECT @ID = Max(FileId) FROM #BackupMetadata WHERE Type = 'L'
Then do your restore using dynamic SQL:
DECLARE @CMD NVARCHAR(500) SET @CMD = 'RESTORE LOG [db_test_rep] FROM DISK = N''Z:\db_rep.bak'' WITH FILE = ' + @ID + ', NORECOVERY, NOUNLOAD, STATS = 5' EXEC (@CMD)
The plan is to have 1 backup set
If you’re looking to use the server just as a backup for high availabilty, you should consider looking at Availability groups
It’s fairly easy to setup, and has useful features like automated failovers incase something goes wrong on your primary.
(very very) Short :
– Create a cluster between your 2 windows servers
– Create an Availability group between them
– Add databases to your group
This link gives a pretty good overview of how to set it up.
If you’re using a standard licence, you’ll be stuck using Basic Availability Groups, which has the limitation of only being able to put one database in each AG.
More info in this link.
The best suitable solution in your scenario is LOG SHIPPING which is available in Web, Standard and Enterprise editions. I assume that your SQL server is not express as you mentioned There is already a scheduled job
If you want work with T-SQL, i don’t think appending backup files is good idea, so you may want to change schedule job to create separate file for each backup. Then you can start with following query to get dynamic restore command based on backup history:
SELECT [b].[database_name] , [b].[backup_start_date] , [b].[backup_finish_date] , f.physical_device_name, ((b.compressed_backup_size / 1024) / 1024) as CompressedSize_MB, CASE WHEN Type = 'L' Then 'RESTORE LOG '+ QUOTENAME([database_name]) +' FROM DISK=N''' + f.physical_device_name + ''' WITH NORECOVERY, REPLACE, STATS; ' WHEN Type = 'D' Then 'RESTORE DATABASE '+ QUOTENAME([database_name]) +' FROM DISK=N''' + f.physical_device_name + ''' WITH NORECOVERY, REPLACE, STATS; ' END as Script, b.is_single_user, b.user_name FROM [msdb].[dbo].[backupset] AS [b] LEFT JOIN msdb.dbo.backupmediafamily f ON b.media_set_id = f.media_set_id where b.type in ('D', 'L') and b.database_name = 'TestDAG' --- add your database name here order by b.backup_finish_date desc
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂