none
Error with "The log in this backup set begins at LSN...."

    Question

  • --set database to full recovery
    USE master;
    ALTER DATABASE NetTrustOSK SET RECOVERY FULL;

    --create new filegroup and file
    USE master;
    GO
    ALTER DATABASE NetTrustOSK
    ADD FILEGROUP Secondary;
    GO

    ALTER DATABASE NetTrustOSK
    ADD FILE
    (
    NAME = Secondary2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Secondary.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
    )
    TO FILEGROUP Secondary;
    GO

    --create table in secondary filegroup
    USE NetTrustOSK;
    CREATE TABLE MyTable
    ( cola int PRIMARY KEY,
    colb char(8) )
    ON Secondary;
    GO

    USE NetTrustOSK;
    insert into MyTable (cola,colb) values ('1','A')
    insert into MyTable (cola,colb) values ('2','B')
    insert into MyTable (cola,colb) values ('3','C')
    insert into MyTable (cola,colb) values ('4','D')
    insert into MyTable (cola,colb) values ('5','E')

    BACKUP DATABASE NetTrustOSK READ_WRITE_FILEGROUPS
    TO DISK = 'C:\Documents and Settings\Lip Kee\My Documents\Backup\NetTrust_2009.bak'
    WITH DESCRIPTION = 'First BackUp Of NetTrust',
    INIT
    GO

    --USE NetTrustOSK
    --select * from usergroup --256 records
    --select * from MyTable --5 records

    USE NetTrustOSK
    delete from usergroup --0 records
    delete from MyTable --0 records

    BACKUP LOG NetTrustOSK TO DISK='C:\Documents and Settings\Lip Kee\My Documents\Backup\NetTrust_2009.bak'
    WITH NOINIT;


    BACKUP DATABASE NetTrustOSK
    FILE = 'Secondary2', FILEGROUP='Secondary'
    TO DISK = 'C:\Documents and Settings\Lip Kee\My Documents\Backup\NetTrust_2009.diff'
    WITH DIFFERENTIAL,
    NOINIT,
    STATS= 50
    GO


    RESTORE DATABASE NetTrustOSK
    FROM DISK = 'C:\Documents and Settings\Lip Kee\My Documents\Backup\NetTrust_2009.bak'
    WITH NORECOVERY, REPLACE
    GO
    RESTORE DATABASE NetTrustOSK
    FROM DISK = 'C:\Documents and Settings\Lip Kee\My Documents\Backup\NetTrust_2009.diff'
    WITH NORECOVERY
    GO
    RESTORE LOG NetTrustOSK
    FROM DISK = 'C:\Documents and Settings\Lip Kee\My Documents\Backup\NetTrust_2009.bak'
    WITH FILE=1,NORECOVERY
    GO
    RESTORE LOG NetTrustOSK
    FROM DISK = 'C:\Documents and Settings\Lip Kee\My Documents\Backup\NetTrust_2009.bak'
    WITH FILE=2,NORECOVERY
    GO
    RESTORE LOG NetTrustOSK
    FROM DISK = 'C:\Documents and Settings\Lip Kee\My Documents\Backup\NetTrust_2009.diff'
    WITH FILE=1,RECOVERY

    Currently, the primary filegroup contains usergroup table with 256 records and my secondary filegroup contains MyTable table with 5 records. After running all the codes above, I get this error.

    Processed 98384 pages for database 'NetTrustOSK', file 'NetTrust_Data' on file 1.
    Processed 16 pages for database 'NetTrustOSK', file 'Secondary2' on file 1.
    Processed 5 pages for database 'NetTrustOSK', file 'NetTrust_Log' on file 1.
    RESTORE DATABASE ... FILE=<name> successfully processed 98405 pages in 86.856 seconds (9.281 MB/sec).
    Processed 16 pages for database 'NetTrustOSK', file 'Secondary2' on file 1.
    Processed 3 pages for database 'NetTrustOSK', file 'NetTrust_Log' on file 1.
    RESTORE DATABASE ... FILE=<name> successfully processed 19 pages in 0.421 seconds (0.353 MB/sec).
    Processed 0 pages for database 'NetTrustOSK', file 'NetTrust_Data' on file 1.
    Processed 0 pages for database 'NetTrustOSK', file 'Secondary2' on file 1.
    Processed 5 pages for database 'NetTrustOSK', file 'NetTrust_Log' on file 1.
    RESTORE LOG successfully processed 5 pages in 0.253 seconds (0.159 MB/sec).
    Processed 0 pages for database 'NetTrustOSK', file 'NetTrust_Data' on file 2.
    Processed 13 pages for database 'NetTrustOSK', file 'NetTrust_Log' on file 2.
    RESTORE LOG successfully processed 13 pages in 0.026 seconds (4.017 MB/sec).
    Msg 4305, Level 16, State 1, Line 1
    The log in this backup set begins at LSN 188485000000009400255, which is too recent to apply to the database. An earlier log backup that includes LSN 188485000000007400001 can be restored.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE LOG is terminating abnormally.


    I will restore the database back to the original where both tables MyTable and usergroup remain 5 and 256 records each. Then by applying the differential backup for Secondary filegroup, MyTable table will have 0 records and usergroup table remain 256 records. Thanks in advance for any reply!
    Friday, November 27, 2009 5:58 PM

Answers

  • >The log in this backup set begins at LSN 188485000000009400255, which is too recent to apply to the database. An >earlier log backup that includes LSN 188485000000007400001 can be restored.

    That means you are not restoring the tran logs in order like 9am, 10am, 11am, etc.

    Here is my suggestion:

    Put only 1 backup into 1 file and name it with timestamp like: YYYYMMDD_HH_MM_SS

    AW20091201_01_20_33.bak  (full backup)
    AW20091202_01_20_43.diff  (differential backup)
    AW20091202_09_00_00.trn   (transaction log backup)
    AW20091202_09_30_00.trn
    AW20091202_10_00_00.trn
    AW20091202_10_30_00.trn

    The advantage of this method that you can tell from the file name how to restore it in order.

    Let us know if helpful.

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by Zongqing Li Friday, December 04, 2009 8:07 AM
    Wednesday, December 02, 2009 2:50 AM
    Moderator

All replies

  • Maybe these hints will be helpful: http://www.sqlservercentral.com/Forums/Topic448890-24-1.aspx


    ---
    Paul Svirin
    StarWind Software developer ( http://www.starwindsoftware.com )
    Friday, November 27, 2009 6:12 PM
  • >The log in this backup set begins at LSN 188485000000009400255, which is too recent to apply to the database. An >earlier log backup that includes LSN 188485000000007400001 can be restored.

    That means you are not restoring the tran logs in order like 9am, 10am, 11am, etc.

    Here is my suggestion:

    Put only 1 backup into 1 file and name it with timestamp like: YYYYMMDD_HH_MM_SS

    AW20091201_01_20_33.bak  (full backup)
    AW20091202_01_20_43.diff  (differential backup)
    AW20091202_09_00_00.trn   (transaction log backup)
    AW20091202_09_30_00.trn
    AW20091202_10_00_00.trn
    AW20091202_10_30_00.trn

    The advantage of this method that you can tell from the file name how to restore it in order.

    Let us know if helpful.

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by Zongqing Li Friday, December 04, 2009 8:07 AM
    Wednesday, December 02, 2009 2:50 AM
    Moderator