0

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?

0

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)
  • This is what I need. Thank you. Though I used the RESTORE HEADERONLY function. I just followed your procedure. – JRA Nov 8 at 7:49
2

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.

  • Availability group is not in our option as we are encountering some issues on our Cloud Service provider on this part "Create a cluster between your 2 windows servers" – JRA Nov 7 at 13:56
  • I see, that's unfortunate. Shekars answer is probably what you need then. (Log shipping) – Yannick Liekens Nov 7 at 14:01
2

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 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.