How do I setup Log Shipping on SQL 2008?
1. Launch SQL Server 2008 Management Studio and connect to the primary server which stores the database you wish to implement log shipping for.
2. Right-click on the database and select Tasks->Ship Transaction Logs as illustrated here: http://www.flickr.com/photos/41577956@N02/3834682918/. This will bring up the default Log Shipping properties screen as seen here*: http://www.flickr.com/photos/41577956@N02/3834682920/.
3. Select the checkbox to enable this as the primary database in the log shipping configuration. This will enable you to select the Backup Settings button just beneath it. Click this button to configure the primary server’s settings now.
4. While the Backup Settings screen gives you the option of specifying either a network or a local directory in which to place the backup files created, it’s recommended that you utilize a network path instead of a local one, as this is one very easy way to ensure your backup files are kept safe elsewhere in the event of a catastrophe (see sample screenshot here: http://www.flickr.com/photos/41577956@N02/3833909563/). It’s also a good idea to place the ‘$’ symbol at the end of your network path’s share name, as this hides the share from users unless they specifically know the share name first; a good security practice.
5. Configure the schedule you’d like to use for backing up of the primary server, and set the compression level here as well, then click Okay.
6. The main log shipping properties screen now allows you to add the secondary server. Click Add to bring up the following screen: http://www.flickr.com/photos/41577956@N02/3834682934/. Connect to the designated secondary server that will receive the backups sent by the primary, and choose how you wish to have the initial, full backup of the database restored, and configure the scheduling for this activity here as well, within the Initialize Secondary Database tab.
7. Select the Copy Files tab and enter the local directory, as it exists on the secondary server, as the destination directory for all copied files. Set how long you wish to leave these files remaining here via the Delete copied files after setting, and configure the copy routine’s schedule. See example screenshot here: http://www.flickr.com/photos/41577956@N02/3834682938/.
8. Select the Restore Transaction Log tab and specify the database state you wish to have the backup restored in. The default, No recovery mode, will disallow any access to the secondary database file in perpetuity, whereas Standby Mode allows for read only connections to be made. Disconnect… when restoring backups will help ensure the restoration process succeeds as scheduled (recommended). If you wish to delay the restoration of the backups by a few minutes or more that can be specified here as well, as can the restoration schedule. See example screenshot here: http://www.flickr.com/photos/41577956@N02/3834682940/. Click Okay when finished.
9. Lastly, if you wish, it’s a good idea to specify a monitor server as well in order to receive alerts in the event of any failures that may occur. From the main log shipping properties screen, check the Use a monitor server instance checkbox and click Settings. Connect to the monitor server of your choice, which can be either the primary or secondary server as well, choose you logon options and the length to which you wish to have history files age before being deleted, then click Okay. See example screenshot here: http://www.flickr.com/photos/41577956@N02/3834682944/
10. Once done, the main log shipping properties screen should look something like this: http://www.flickr.com/photos/41577956@N02/3833918043/. Click Okay to initiate the changes and you are done! =)
* – Note: the database in question will need to be set with either the Full or Bulk-logged recovery mode, otherwise you will receive an error upon trying to access this screen. Change this setting accordingly by right-clicking the database first, selecting Properties, and then Options.
