Reputation: 49
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