locked
How to change database back to NORECOVERY MODE FROM RECOVERY MODE RRS feed

  • Question

  • Hi Experts,

    I took full backup of "Practice" DB today at 6 PM EST and stored on D drive on the box(say box A, Practice.BAK), also I took 2 log backup at 6:30 PM & 7 PM EST timings. (Practice1.TRN & Practice2.TRN)

    Now I restore the full backup which I took at 6 PM WITH RECOVERY mode instead of NORECOVERY MODE on boxB.

    So my question is how can I change the restored database "Practice" which I did on boxB from RECOVERY MODE TO NORECOVERY MODE, as I need to also restore 2 log backups and restoring log back need the full back in NORECOVERY MODE. Is their any way or do I need to drop the database on boxB and again I need to restore full backup with NORECOVERY MODE.

     

    Please correct me if I'm wrong.

    Help me.

    Thanks

    Regards,

    Kumar 

    Friday, July 16, 2010 12:25 AM

Answers

  • That would work.

    If you do 4 with RECOVERY, then you don't need 5.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by gk1393 Friday, July 16, 2010 4:46 PM
    Friday, July 16, 2010 1:24 AM
  • The restore of the logs needs to be RESTORE LOG, not RESTORE DATABASE.  Also, while it is fine to drop the database before you restore it, that is not necessary.  And while it is fine to do the last restore with NORECOVERY and then do a restore with RECOVERY, you could just do the last restore with RECOVERY.  So

    1. DROP DATABASE Practice
    2. RESTORE DATABASE Practice FROM DISK = 'D:/Practice.BAK' WITH NORECOVERY
    3. RESTORE LOG Practice FROM DISK = 'D:/Practice1.TRN' WITH NORECOVERY
    4. RESTORE LOG Practice FROM DISK = 'D:/Practice2.TRN' WITH NORECOVERY
    5. RESTORE DATABASE Practice WITH RECOVERY

    or you could just do

    1. RESTORE DATABASE Practice FROM DISK = 'D:/Practice.BAK' WITH NORECOVERY
    2. RESTORE LOG Practice FROM DISK = 'D:/Practice1.TRN' WITH NORECOVERY
    3. RESTORE LOG Practice FROM DISK = 'D:/Practice2.TRN' WITH RECOVERY
    

    If you don't DROP the database before restoring it, then SQL will check that this backup came from the database you are restoring to.  If it didn't, SQL will give you an error message and won't do the restore unless you use the option WITH REPLACE

    Tom

    • Marked as answer by gk1393 Friday, July 16, 2010 4:46 PM
    Friday, July 16, 2010 1:37 AM

All replies

  • Best to start all over with FULL BACKUP restore (NORECOVERY).

    Since you unintentionally restored it with RECOVERY, the likelyhood is high that the database state has changed, so you could not apply the the .trn files.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Friday, July 16, 2010 1:05 AM
  • So it means that I have to perform below steps on boxB:-

    1. DROP DATABASE Practice

    2. RESTORE DATABASE Practice FROM DISK = 'D:/Practice.BAK' WITH NORECOVERY

    3. RESTORE DATABASE Practice FROM DISK = 'D:/Practice1.TRN' WITH NORECOVERY

    4. RESTORE DATABASE Practice FROM DISK = 'D:/Practice2.TRN' WITH NORECOVERY

    5. RESTORE DATABASE Practice WITH RECOVERY

     

    Please let me know if I'm wrong than.

     

    Thanks

    Regards,

    Kumar

    Friday, July 16, 2010 1:15 AM
  • That would work.

    If you do 4 with RECOVERY, then you don't need 5.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by gk1393 Friday, July 16, 2010 4:46 PM
    Friday, July 16, 2010 1:24 AM
  • The restore of the logs needs to be RESTORE LOG, not RESTORE DATABASE.  Also, while it is fine to drop the database before you restore it, that is not necessary.  And while it is fine to do the last restore with NORECOVERY and then do a restore with RECOVERY, you could just do the last restore with RECOVERY.  So

    1. DROP DATABASE Practice
    2. RESTORE DATABASE Practice FROM DISK = 'D:/Practice.BAK' WITH NORECOVERY
    3. RESTORE LOG Practice FROM DISK = 'D:/Practice1.TRN' WITH NORECOVERY
    4. RESTORE LOG Practice FROM DISK = 'D:/Practice2.TRN' WITH NORECOVERY
    5. RESTORE DATABASE Practice WITH RECOVERY

    or you could just do

    1. RESTORE DATABASE Practice FROM DISK = 'D:/Practice.BAK' WITH NORECOVERY
    2. RESTORE LOG Practice FROM DISK = 'D:/Practice1.TRN' WITH NORECOVERY
    3. RESTORE LOG Practice FROM DISK = 'D:/Practice2.TRN' WITH RECOVERY
    

    If you don't DROP the database before restoring it, then SQL will check that this backup came from the database you are restoring to.  If it didn't, SQL will give you an error message and won't do the restore unless you use the option WITH REPLACE

    Tom

    • Marked as answer by gk1393 Friday, July 16, 2010 4:46 PM
    Friday, July 16, 2010 1:37 AM
  • I have a further question:

    After 

    3. RESTORE LOG Practice FROM DISK = 'D:/Practice2.TRN' WITH RECOVERY

    I still need to restore more later incremental backup .trn files, is it possible, or change status from RECOVERY(operational) to NORECOVERY(non-operational)?

    Because we need to sync DBs from Server1 to Server2. DBs on Server1 have incremental backup (transactional log) per day, and need to apply the incremental backup to Server2 every day.

     

    To be yourself.

    Tuesday, August 11, 2020 9:42 AM
  • You may have a better success if you try asking your question in a new thread with all the extra information.

    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, August 11, 2020 6:32 PM