LDonSOvrfw
LDonSOvrfw

Reputation: 49

Cannot restore SQL Server log due to early LSN of log backup

I want to do a databases backup on master server and afterwards restore of this database on slave server. I have this sequence in T-SQL script:

Backup (run on master server)

DECLARE @backupFileName NVARCHAR(200);

SET @backupFileName = N'/var/opt/mssql/shipping/' + @databaseName + '.bak'
BACKUP DATABASE @databaseName TO DISK = @backupFileName

DECLARE @logFileName NVARCHAR(200);
SET @logFileName = N'/var/opt/mssql/shipping/' + @databaseName + '.trn'

BACKUP LOG @databaseName TO DISK = @logFileName

Restore (run on slave server)

DECLARE @restoreCommand NVARCHAR(200)
SET @restoreCommand  = 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = ''' + @backupFileName + ''' WITH NORECOVERY'
EXECUTE ( @restoreCommand ) AT [172.21.23.37];

DECLARE @restoreLogCommand NVARCHAR(200)
SET @restoreLogCommand  = 'RESTORE LOG ' + @databaseName + ' FROM DISK = ''' + @backupFileName + ''' WITH NORECOVERY'
EXECUTE ( @restoreLogCommand ) AT [172.21.23.37];

DECLARE @restoreFinalCommand NVARCHAR(200)
SET @restoreFinalCommand  = 'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY'
EXECUTE ( @restoreFinalCommand ) AT [172.21.23.37];

But in the end I receive this message:

Processed 336 pages for database 'NewDB1', file 'NewDB1' on file 16.  
Processed 1 pages for database 'NewDB1', file 'NewDB1_log' on file 16.  
BACKUP DATABASE successfully processed 337 pages in 0.040 seconds (65.661 MB/sec).    
Processed 4 pages for database 'NewDB1', file 'NewDB1_log' on file 8.  
BACKUP LOG successfully processed 4 pages in 0.002 seconds (12.695 MB/sec).  
Processed 320 pages for database 'NewDB1', file 'NewDB1' on file 1.  
Processed 1 pages for database 'NewDB1', file 'NewDB1_log' on file  1.  
RESTORE DATABASE successfully processed 321 pages in 0.013 seconds (192.420 MB/sec).

Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 40000000019400001, which is too early to apply to the database. A more recent log backup that includes LSN 40000000020000001 can be restored.

RESTORE DATABASE successfully processed 0 pages in 0.181 seconds (0.000 MB/sec).
Completion time: 2024-11-20T13:30:05.9994647+01:00

Why there is written that LSN of log backup is earlier than LSN of database backup when database backup was run sooner than log backup?

Upvotes: 1

Views: 51

Answers (0)

Related Questions