ลงชื่อเข้าใช้
Microsoft.com
 
Microsoft
 
 
 

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

 > 

  • คำถาม

  • Question
    ลงชื่อเข้าใช้เพื่อโหวต
    0
    ลงชื่อเข้าใช้เพื่อโหวต

    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 

    16 กรกฎาคม 2553 0:25

คำตอบ

  • Question
    ลงชื่อเข้าใช้เพื่อโหวต
    0
    ลงชื่อเข้าใช้เพื่อโหวต

    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
    • ทำเครื่องหมายเป็นคำตอบโดย gk1393 16 กรกฎาคม 2553 16:46
    16 กรกฎาคม 2553 1:24
  • Question
    ลงชื่อเข้าใช้เพื่อโหวต
    0
    ลงชื่อเข้าใช้เพื่อโหวต

    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

    • ทำเครื่องหมายเป็นคำตอบโดย gk1393 16 กรกฎาคม 2553 16:46
    16 กรกฎาคม 2553 1:37

ตอบทั้งหมด

  • Question
    ลงชื่อเข้าใช้เพื่อโหวต
    0
    ลงชื่อเข้าใช้เพื่อโหวต

    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
    16 กรกฎาคม 2553 1:05
  • Question
    ลงชื่อเข้าใช้เพื่อโหวต
    0
    ลงชื่อเข้าใช้เพื่อโหวต

    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

    16 กรกฎาคม 2553 1:15
  • Question
    ลงชื่อเข้าใช้เพื่อโหวต
    0
    ลงชื่อเข้าใช้เพื่อโหวต

    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
    • ทำเครื่องหมายเป็นคำตอบโดย gk1393 16 กรกฎาคม 2553 16:46
    16 กรกฎาคม 2553 1:24
  • Question
    ลงชื่อเข้าใช้เพื่อโหวต
    0
    ลงชื่อเข้าใช้เพื่อโหวต

    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

    • ทำเครื่องหมายเป็นคำตอบโดย gk1393 16 กรกฎาคม 2553 16:46
    16 กรกฎาคม 2553 1:37
  • Question
    ลงชื่อเข้าใช้เพื่อโหวต
    0
    ลงชื่อเข้าใช้เพื่อโหวต

    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.

    11 สิงหาคม 2563 9:42
  • Question
    ลงชื่อเข้าใช้เพื่อโหวต
    0
    ลงชื่อเข้าใช้เพื่อโหวต
    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

    11 สิงหาคม 2563 18:32