What is Log Shipping in SQL Server 2008?

How to Perform SQL Server Log Shipping: Log shipping is the process of automating the backup ofdatabase and  transaction log files on a production SQL server, and then restoring them onto a standby server. Only Enterprise Editions support log shipping. In log shipping, the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db can be used this as the Disaster Recovery plan. The key feature of log shipping is that is will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.

Log shipping

• Automated process to ship transaction log backups to another server
• Three jobs to perform the required actions: backup, copy and recover
• Involves three servers/instances: primary, secondary and (optional) monitoring server
• Requires full or bulk logged mode
• Can have multiple secondary copies
• Information about log shipping kept in MSDB

Log Shipping Options

• Interval – Default is 15 minutes. Consider the impact before using a smaller interval
• Delayed log restore – option that allows you to have an older copy
• If secondary is far behind. Consider backup/copy to media/restore, consider partitioning the data
• Careful – Backup typically goes to a file server share. Agent account needs access to shared files
• Careful – Weekend maintenance jobs can make you run out of disk space
• Monitoring – MSDB tables, agent history

Problems with Log Shipping

Log shipping is a compromise but it is not the ideal solution, it is a practical solution given real-world budget constraints. Some of the problems with log shipping include:

• Log shipping failover is not automatic. The DBA must be present when the failover occurs.
• The users will experience some downtime. How long depends on how well you implemented log shipping, the nature of the production server failure, your network, the standby server, and the application or applications to be failed over.
• Some data can be lost, although not always. How much data is lost depends on how often you schedule log shipping and whether or not the transaction log on the failed production server is recoverable.
• The database or databases that are being failed over to the standby server cannot be used for anything else. But databases on the standby server not being used for failover can still be used normally.
• When it comes time for the actual failover, you must do one of two things to make your applications work: either rename the standby server the same name as the failed production server (and the IP address), or re-point your user's applications to the new standby server. In some cases, neither of these options is practical.