Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Friday, July 16, 2010 12:25 AM
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 1:24 AM ✅Answered
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
Friday, July 16, 2010 1:37 AM ✅Answered
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
Friday, July 16, 2010 1:05 AM
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:15 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
Tuesday, August 11, 2020 9:42 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 6:32 PM
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