none
Restore DB Error

    Question

  • Dear  All,

     when i restore DB it shows as below Error message, kindly advise, thank you.

    Msg 3117, Level 16, State 4, Line 1
    The log or differential backup cannot be restored because no files are ready to rollforward.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    RESTORE FILELISTONLY
    FROM DISK = 'F:\RestoreDB\CIMProMES01_backup_2013_11_19_080001_8418750.bak'
    GO
    
    
    
    ALTER DATABASE CIMProMES01
    SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE
    
    
    use master 
    
    
    RESTORE DATABASE CIMProMES01
    FROM DISK = 'F:\RestoreDB\CIMProMES01_backup_2013_11_19_080001_8418750.bak' WITH REPLACE
    
    
    
    
    
    ALTER DATABASE CIMProMES01 SET MULTI_USER
    GO
    

    Tuesday, November 19, 2013 1:11 AM

Answers

  • Hi Caulson,

    You cannot restore from the Differential or Log backup on a database which is in Online state. You can restore from a differential or log backup only when the database is in No Recovery Mode.

    If you want to restore a database from differential or Log backup then you are to follow the below sequence of restoration:

    1. Restore the database from the latest available full backup and keep the database is no recovery mode.
    2. Restore the database from the latest differential backup (which was taken after the full backup used in the previous step) and keep the database in no recovery mode if log backups are to be applied else you can bring the database online.
    3. Restore the database from the log backups in the same sequence in which the have been taken after the latest differential backup and bring the database online.

    In your case the sequence should be as below:

    ALTER DATABASE CIMProMES01
    SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE


    use master


    RESTORE DATABASE
    CIMProMES01
    FROM DISK
    = 'F:\RestoreDB\fullbackupfile.bak' WITH REPLACE, Norecovery, stats = 10

    RESTORE DATABASE CIMProMES01
    FROM DISK
    = 'F:\RestoreDB\fullbackupfile.bak' WITH REPLACE, Norecovery, stats = 10

    RESTORE DATABASE CIMProMES01
    FROM DISK
    = 'F:\RestoreDB\CIMProMES01_backup_2013_11_19_080001_8418750.bak' WITH REPLACE, Recovery, stats = 10


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    • Marked as answer by Caulson Wednesday, November 20, 2013 2:45 PM
    Tuesday, November 19, 2013 4:05 AM

All replies

  • Judging from the error message, I would guess that you have a differential backup. In that case you need to restore the full backup first, using WITH NORECOVERY.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, November 19, 2013 2:12 AM
  • Hi Caulson,

    You cannot restore from the Differential or Log backup on a database which is in Online state. You can restore from a differential or log backup only when the database is in No Recovery Mode.

    If you want to restore a database from differential or Log backup then you are to follow the below sequence of restoration:

    1. Restore the database from the latest available full backup and keep the database is no recovery mode.
    2. Restore the database from the latest differential backup (which was taken after the full backup used in the previous step) and keep the database in no recovery mode if log backups are to be applied else you can bring the database online.
    3. Restore the database from the log backups in the same sequence in which the have been taken after the latest differential backup and bring the database online.

    In your case the sequence should be as below:

    ALTER DATABASE CIMProMES01
    SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE


    use master


    RESTORE DATABASE
    CIMProMES01
    FROM DISK
    = 'F:\RestoreDB\fullbackupfile.bak' WITH REPLACE, Norecovery, stats = 10

    RESTORE DATABASE CIMProMES01
    FROM DISK
    = 'F:\RestoreDB\fullbackupfile.bak' WITH REPLACE, Norecovery, stats = 10

    RESTORE DATABASE CIMProMES01
    FROM DISK
    = 'F:\RestoreDB\CIMProMES01_backup_2013_11_19_080001_8418750.bak' WITH REPLACE, Recovery, stats = 10


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    • Marked as answer by Caulson Wednesday, November 20, 2013 2:45 PM
    Tuesday, November 19, 2013 4:05 AM