SQL Server 2016 – How to Automate Database Restore to Another Server

backuprestoresql-server-2016ssmstransaction-log

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?

Best Answer

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)
Related Question