SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled.
- Provides a disaster-recovery solution for a single primary database and one or more secondary databases, each on a separate instance of SQL Server.
- Supports limited read-only access to secondary databases (during the interval between restore jobs).
- Allows a user-specified delay between when the primary server backs up the log of the primary database and when the secondary servers must restore (apply) the log backup. A longer delay can be useful, for example, if data is accidentally changed on the primary database. If the accidental change is noticed quickly, a delay can let you retrieve still unchanged data from a secondary database before the change is reflected there.
Log shipping consists of three operations:
- Back up the transaction log at the primary server instance.
- Copy the transaction log file to the secondary server instance.
- Restore the log backup on the secondary server instance.
The log can be shipped to multiple secondary server instances. In such cases, operations 2 and 3 are duplicated for each secondary server instance.
A log shipping configuration does not automatically fail over from the primary server to the secondary server. If the primary database becomes unavailable, any of the secondary databases can be brought online manually.
You can use a secondary database for reporting purposes.
In addition, you can configure alerts for your log shipping configuration.
Configure Log Shipping
- The primary database must use the full or bulk-logged recovery model; switching the database to simple recovery will cause log shipping to stop functioning.
- Before you configure log shipping, you must create a share to make the transaction log backups available to the secondary server. This is a share of the directory where the transaction log backups will be generated. For example, if you back up your transaction logs to the directory c:\data\tlogs\, you could create the \\primaryserver\tlogs share of that directory.
The log-shipping stored procedures require membership in the sysadmin fixed server role.
-Right click the database you want to use as your primary database in the log shipping configuration, and then click Properties.
-Under Select a page, click Transaction Log Shipping.
-Select the Enable this as a primary database in a log shipping configuration check box.
-Under Transaction log backups, click Backup Settings
-In the Network path to the backup folder box, type the network path to the share you created for the transaction log backup folder.
-Configure the Delete files older than and Alert if no backup occurs within parameters.
-Under Secondary server instances and databases, click Add.
-Click Connect and connect to the instance of SQL Server that you want to use as your secondary server.
-In the Secondary Database box, choose a database from the list or type the name of the database you want to create.
-On the Initialize Secondary database tab, choose the option that you want to use to initialize the secondary database.
-On the Copy Files tab, in the Destination folder for copied files box, type the path of the folder into -which the transaction logs backups should be copied. This folder is often located on the secondary server.
-On the Restore tab, under Database state when restoring backups, choose the No recovery mode or Standby mode option.