SQL 2005 Logshipping to secondary server

SQL 2005 Logshipping to secondary server

• _JERKER_

  Hi Wenchi,

  If the primary server is down and if you dont have the tran log file available in that case you can bring the secondary server online by performing as below,

  RESTORE DATABASE DBNAME WITH RECOVERY

  This will definitely bring the secondary servers db which was initially in Read-only/ standby mode to online status........

  Incase you have one unapplied tran log and the primary server is down you can bring the secondary server online as shown below,

  RESTORE LOG DBNAME FROM DISK = 'PATH OF THE UNAPPLIED LOG FILE'

  WITH RECOVERY

  I assume that the unapplied log is in sequence........all other logs are applied from primary server and only this log is unapplied in such case you can perform as above..........

  If your secondary server is in use then restoration will definitely terminate hence you need to do as below,

  ALTER DATABASE DBNAME

  SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  and then proceed with restoring the log as I pointed out earlier

  you can refer these links if needed http://sql-articles.com/articles.php pageNum_alltop=4&totalRows_alltop=35

  
  
  

• Sadhvi

  You need to make sure that all your log restores (except for the very last one) leave your database in a state to restore more, basically the database should be in 'norecovery' option until the very last restore, if you don't specify norecovery in your restore statement the default behaviour is recovery so you would not be able to restore anymore transaction log backups.

  I have had an issue with a log file where the primary server had a power outage while a transaction log file was being copied, although the log file looked correct (size-wise at least) it corrupted the DB when I tried to restore it (had to start all over again).

  Here's what should happen all things equal:

  1. Primary server fails.

  2. Restore all the transaction logs on the Secondary.

  3. Bring Secondary server online.

  I have seen the secondary server come online by changing the SQL server connection parameters in the client connection string (if possible), rename Secondary server (a few items to do here), change DNS entries to point from the Primary to Secondary server.

  
  

• ProgramLeaner

  Hi,

  I had the similar question too. We are using log-shipping for our disaster recovery plan. The log-shipping setup is completed and working fine for us. I am just trying to find a way to bring the secondary sever online as primary server in case somehting happend to the primary sever. Most of the instruction assumed that you still have access primary sever so you can backup the last log file and apply to the secondary sever. Well, I am assumed that we won't be able to acess the primary sever; therefore, I take the last unapplied log file (all others are applied by log-shipping job), and I got an error saying the db is in use so it stops. The database is not in use at all so I don't know what to do Other related question is what if the last log file alreday applied to the db on the secondary server, how do I bring those database on line (not in standby/read-only mode) We are using sql2005 sp2 enterprise edition. Any help is appreciated. Thanks!

  wenchi

  
  

• Paul Middlin

  DBornack wrote:

  Say my primary server starts on fire, melts down, etc. How do I bring the database on the secondary server online Do I need to roll in the transaction logs, etc. I'm looking for a step-by-step, as I am very new to SQL. I've seen some articles in the SQL Books Online, but things don't seem to work correctly when I follow the steps.

  Are you using the log shipping feature of SQL Server or using your own processes Either way, log shipping should hopefully be set up to automatically copy and apply the transaction logs. Otherwise, you'll have to apply them manually. All but the last log will be applied in a way that the DB can still load t-logs. The last transaction log will be applied and will allow the DB to be brought online.

  After that, you need to make sure all of your logins, et al., exist and are sync'd up, and anything like jobs or other objects which sit outside the DB itself are there should you need them.

  Hope that helps.

  
  Allan

  
  
  

• Tee

  When you say that the steps in Books Online don't work for you, can you describe what you're doing and what goes wrong That will help us to get you up and running, and also to make the BOL docs better for next time.

  Thanks,

  Kevin Farlee

  
  
  
• SQL 2005 Logshipping to secondary server