Restoring database backup on different SQL server | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Restoring database backup on different SQL server

Hi All, I am facing problem while restoring my databse backup to another instance of SQL server. I describe the steps what i followed to backup and restore the database. What I want to do is "I want to maintain two SQL server instances, one instance will serve as live server and other will serve as backup server, I will periodically update the backup server database by restoring the backups of live server." I have installed SQL server 2000 on two different machines say A and B. A is my primary/live server and B is my secondry/backup server. 1- I took a complete backup of my databse using Enterprise Manager on machine A.
2- I try to restore the complete backup of my database at machine B (I select ‘from deviice’ and then browse my complete backup file).
3- Restore is succesfull, and a databse is created on machine B (at this stage, restored databse on B is same as the database on machine A)
4- I perform some transactions on my live database on machine A.
5- Now I took the transaction log backup of my database on machine A.
6- I try to to restore that transaction log backup (I select ‘from deviice’ and then browse my transaction log backup file) on machine B (bcz I want to update the databse on machine B with new transaction)
7- I got the following error in restoring the transaction log backup
"The precding restore operation did not specify NO RECOVERY or WITH STANDBY. Restart the restore sequence, specifying with NO RECOVERY or STANDBY for all but the final step.
Restore log terminating abnormally." Please help me to get rid of this problem.
If I am doing something wrong then please tell me the exact sequesnce of steps to perform backup and restore process regularly. Thanx, Regards,
Yasir Khan
With STANDBY means that open transactions will not rolled back. Suppose in the full backup you have the beginning of transaction x, and at the next tlog the end of it. Restoring full back up specifying RECOVERTY will rollback transaction x. With no recovery (standby) you will leave database non-operational and be to restore the next tlog where transaction x ends. The last backup will always be restored with recovery if you want to have an operational database.
But there are other solutions to what you are doing. Bambola.
As specified by Bambola, on the secondary server during full backup restore you should mention either NORECOVERY or STANDBY. During Tlog restore specify WITH RECOVERY to keep the database operational or for the last Tlog. Check this link http://www.sql-server-performance.com/sql_server_log_shipping.asp] for LS tips and tools. _________
Satya SKJ
I see what yoou wanted. YOu want to just apply the transactioon log backup of one database onto other update the second. Satya is heading you to the right direction. U’ll have to restore the database using NORECOVERY or STANDBY mode and then apply the transaction log. It seems to me that this will be a waste of time to do it frequently. In this case i would suggect replication / log shipping as suggected by Satya. Gaurav
]]>