July 7, 2009 at 8:17 pm
I have a backup SQL server that I have set up in case our current one dies. In testing the backup procedures I am able to restore a database from a backup on the old server to the new one using the restore database with move. But I can't find out how to restore the transaction logs from the old server to the new one after restoring the last full backup. I have tried using the restore log commands below:
restore log CCA from disk = 'H:\SQLBackups\CCA_tlog_xxxxxxxxxxxx.trn'
with move 'cca_log' to 'F:\mssql_data_files\cca_log.ldf' ,
with norecovery (if not last transaction log)
with recovery (if last transaction log)
However, I get an error, I think,because of the "with" statements.
Does anyone know how to do this?
July 7, 2009 at 8:47 pm
Since you've already done the database restore, you shouldn't need the move at all. Have you tried the log restore without a move section?
Also, can you post the errors you are getting?
July 7, 2009 at 8:53 pm
I was afraid of doing this in case the transaction log was restored to the original production server database and overwrote data on it. I wasn't sure if information in the transaction log would automatically point it to the original location and restore it there as there is nothing in the code to point it to the new database. If this is not the case Iwill try it.
July 7, 2009 at 9:03 pm
Well at one time you cannot have 2 databases with same name .
So in your case you have restored the database on the different instance of SQLserver .
make sure that you are in the same database context of that new instance and give this command :
restore log from disk ='' with norecovery .
If its the last log then do nothing after giving the path i.e. no need to add "with"
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
July 7, 2009 at 9:09 pm
Since you restore a full backup of your database to another server (let's call it backup-dbserver), when you do a log restore, you must be logged onto backup-dbserver.
I normally set my working database to master (use master), then issue the restore log command like this:
restore log CCA from disk = 'H:\SQLBackups\CCA_tlog_xxxxxxxxxxxx.trn'
with norecovery (if not last transaction log)
with recovery (if last transaction log)
(Since you're on a different server, it will not point back to the production server)
July 7, 2009 at 10:13 pm
Thanks for your help. I will d oas you suggest.
July 7, 2009 at 10:14 pm
Thanks for the help. I will do as you suggest.
July 9, 2009 at 6:09 pm
I have tried restoring the transaction log today with this command from the master database
restore log cca from disk = 'H:\SQLBackups\CCA_tlog_200907070900.TRN'
with norecovery
but got this message
Server: Msg 4306, Level 16, State 1, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I don't understand - I have the norecovery option but its telling me I don't have it???
July 9, 2009 at 7:08 pm
You have to have done the full restore with NORECOVERY (or STANDBY) before you will be able to append any log or differential backups to the restored database. If you leave off the NORECOVERY when you restore the full backup the restore operation will roll back/forward the database, which prevents any log or differential backup being able to be restored to it.
Sorry, but there's no option other than to redo the full backup restore using the NORECOVERY (or STANDBY) option.
You then can restore the log/differential backups to the restored database, again using the NORECOVERY option for all the subsequent restores except the last.
July 9, 2009 at 8:21 pm
Great that now works fine. I should have read more thoroughly to realise this for myself. Thankyou for helping a newbie.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply