Log Shipping

• sqlstar2011

  SSCertifiable

  Points: 6000

  More actions

  April 18, 2007 at 7:37 am

  #119562

  in books online under role swap topic for log shipping the first step is:

   

  1.                 Bring the secondary database online, backing up the transaction log on the primary server with NORECOVERY.

  isnt transaction log backup by default a 'NORECOVERY' one? if not how do you accomplish this task?

• Greg Charles

  SSC-Forever

  Points: 45403

  More actions

  April 19, 2007 at 10:47 am

  #701508

  I don't currently use log shipping, but I believe that's done so the primary database will be in a recovery state and ready to receive log backups made on the secondary database when you're ready to switch back to the primary database.  Obviously, you can only do this if the primary database isn't damaged.

  Greg

  Greg

• Eric Peterson

  Hall of Fame

  Points: 3192

  More actions

  April 19, 2007 at 11:39 am

  #701531

  In 2005 SQL Server Management Studio, Object Explorer the Log Shipping setup is easy. 

  Click on your databse and 

  • Open database properties
  • Click the Trasaction Log Shipping
  • look for the checkbox Enable this as primary database in a log shipping configuration

  Ok three parts to setting this up. 

  • Getting the servers to talk.
  • Backup and restore
  • Log Shipping Setup  

  Getting them to talk

  • I have both servers (and SQL Server Agent) running under a common ID that has admin opriveleges.
  • Make sure that your backup and logship directories (on both machines) are shared 

  Backup and restore

  • I prefer to do this my self only because I have large databses.  Backup the DB, copy to other machine and restore with NORECOVRY
  • You can use the GUI interface....just click the have SQL Server do the backup and restore for me.

  Log Shipping

  • I have found that the server likes IP addresses over logical names. 
  • your backup, copy and restore should be set to different schedules or it will take 3X as long to get the data over.  Backup minute 1, copy minute two, restore minute three.
  • The completion will setup 5 jobs that run under SQL Server Agent. An aleart job onn each machine and a backup job on primary, a copy and restore job on secondary. 

  I hope this helps.   It has run very well for me across 35 databases to as single machine.