SQL Log Backups - "too recent to apply to the database"

Question

I have a rather large database (for our hardware setup) and am trying to get Mirroring going on it. I cannot seem to get the Primary and Mirror databases close enough in sync to allow Mirroring to start.

The full backup takes about 10 minutes, plus another 8 minutes to transfer the BAK to the Mirror server and another 10 minutes to restore it. So, to try to get things as close together as I can I have scripted out the following:

** On the Primary **

SET NOCOUNT ON
USE master


DECLARE @dbFileName     VARCHAR(75)
      , @logFileName    VARCHAR(75)

SELECT @dbFileName     = 'F:\SQL_Backups\RIVFramework_Prod1\Mirror\prod1.mdf.' + CONVERT(VARCHAR(10), GETDATE(), 102) + '.bak'  -- formats date in YYYY.MM.DD --
SELECT @logFileName    = 'F:\SQL_Backups\RIVFramework_Prod1\Mirror\prod1.ldf.' + CONVERT(VARCHAR(10), GETDATE(), 102) + '.bak'  -- formats date in YYYY.MM.DD --

SELECT @dbFileName     
SELECT @logFileName    

/**
 ** 1 -  Make sure your database is in full recovery mode
 **/
ALTER DATABASE RIVFramework_Prod1
SET RECOVERY FULL

/**
 ** 2 - Backup the database
 **/
BACKUP DATABASE RIVFramework_Prod1
TO DISK = @dbFileName

/**
 ** 3 - Backup the database log
 **/
BACKUP LOG RIVFramework_Prod1
TO DISK = @logFileName

SET NOCOUNT OFF

** Copy the files **

---

***NOTE: I altered the main database restore statement to allow the log file to be restored later correctly. Changed

WITH NORECOVERY,

to

WITH REPLACE, NORECOVERY,

---

** On the Mirror **

SET NOCOUNT ON
USE master

/**
 ** Restore backup to Mirror server:
 ** MIRROR SERVER
 ** 4 - Restore to the mirror database with no recovery option
 **/ 
RESTORE DATABASE RIVFramework_Prod1
FROM DISK = 'F:\XFer\prod1.mdf.2011.06.10.bak'
WITH REPLACE, NORECOVERY,   -- You need to have both options in here so the later log file restore works.
 MOVE 'RIVFramework_Prod1' TO 'F:\SQL2008\RIVFramework_Prod1.mdf',
 MOVE 'CampaignAnalytics' TO 'F:\SQL2008\RivFramework_Prod1_CampaignAnalytics.ndf',
 MOVE 'SalesAnalytics' TO 'F:\SQL2008\RivFramework_Prod1_SalesAnalytics.ndf',
 MOVE 'RIVFramework_Prod1_log' TO 'G:\SQL2008\RIVFramework_Prod1_log.ldf'


/**
 ** 5 - Restore the mirror database logs with no recovery
 **/
RESTORE LOG RIVFramework_Prod1
FROM DISK = 'F:\XFer\prod1.ldf.2011.06.10.bak'
WITH NORECOVERY
GO

Now, I am about 30 minutes out of sync. So I do:

** On the Primary **

SET NOCOUNT ON
USE master


DECLARE @logFileName    VARCHAR(75)

SELECT @logFileName    = 'F:\SQL_Backups\RIVFramework_Prod1\Mirror\prod1.ldf.a.' + CONVERT(VARCHAR(10), GETDATE(), 102) + '.bak'    -- formats date in YYYY.MM.DD --

SELECT @logFileName    

/**
 ** 1 -  Make sure your database is in full recovery mode
 **/
ALTER DATABASE RIVFramework_Prod1
SET RECOVERY FULL

/**
 ** 3 - Backup the database log
 **/
BACKUP LOG RIVFramework_Prod1
TO DISK = @logFileName

SET NOCOUNT OFF

** Copy the files **

** On the Mirror **

SET NOCOUNT ON
USE master

/**
 ** 5 - Restore the mirror database logs with no recovery
 **/
RESTORE LOG RIVFramework_Prod1
FROM DISK = 'F:\XFer\prod1.ldf.a.2011.06.10.bak'
WITH NORECOVERY
GO

And I am greeted with the following error:

Msg 4305, Level 16, State 1, Line 53 The log in this backup set begins at LSN 61063000000325800001, which is too recent to apply to the database. An earlier log backup that includes LSN 61063000000117800001 can be restored. Msg 3013, Level 16, State 1, Line 53 RESTORE LOG is terminating abnormally.

Any ideas what I am missing???

TIA

Answer 1

In the first script you will only want to do a full backup. In both scripts do not set the recovery to full every time but check if it is in full and if it is not stop, you will then need to figure out who has been changing the recover mode. At the mirror use the script you showed to restore just the full backup Return to the prinicpal and do a log backup At the mirror restore the log with norecovery.