O'Reilly logo

Professional Microsoft® SQL Server® 2008 Administration by Steven Wort, Ross LoForte, Wayne Snyder, Ketan Patel, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

19.3. Log Shipping Process

SQL Agent is used on the participating servers to execute the processes that implement log shipping. There are three main processes:

  1. Back up the transaction log on the primary server. A SQL Agent job on the primary server backs up the transaction log at a user-configurable time interval to a file in a backup folder. By default, the file name is time-stamped to provide uniqueness—for example, databasename_yyyymmddhhmmss.trn. By default, the backup job is named LSBackup_databasename, and it executes an operating system command to back up the transaction log:

    "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqllogship.exe"
    -Backup 0E5D9AA6-D054-45C9-9C6B-33301DD934E2 -server SQLServer1
  2. Copy the transaction log to the secondary server. A SQL Agent job on the secondary server uses UNC or a shared drive to access the backup folder on the primary server to copy the transaction-log file to a local folder on the secondary server. By default, the copy job is named LSCopy_servername_databasename, and it executes an operating system command to copy the transaction-log file:

    "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqllogship.exe"
    -Copy F2305BFA-B9E3-4B1C-885D-3069D0D11998 -server SQLServer2
  3. Restore the transaction log on secondary server. A SQL Agent job on the secondary server restores the transaction log on the secondary server. To restore the transaction log, the database must be in either Standby or NORECOVERY mode. The default restore job name ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required