Log Shipping How To

Guide for Setting Up Log Shipping on SQL Server 2008 R2/2012

Overview: This process will involve backing up the logs to an area and copying them over from the primary to the secondary and applying them via a restore job.
This will create LSBackup job on the primary and LSCopy / LSRestore jobs on the secondary.
LSBackup will back up the log files to a location on the primary server (job runs on the primary server).
LSCopy will copy the log files from the primary to the secondary (job runs on secondary server).
LSRestore will restore the log files from the secondary to the database (job runs on the secondary server).
Note when using log shipping ensure no log backups are made outside of the log shipping process or you will break the log shipping replication.
1. Ensure both servers are set up to be part of the domain. (control panel - system - Computer name, domain, and workgroup settings - change settings. Set domain to <domain name>)
2. Create folders used for sending the logs on the primary server (Server A) named LogShip and receiving the logs (Server B) named LogReceive.
Ensure this folder is located away from the database.
3. Amend security on the 'LogShip' folder and give access to the secondary server so that it can access the log files on the primary server. E.g add <domain>\<machine-name>from within the domain <domain name>.  It will ask for a domain password for which you must enter netr\<full force number> and <network password>.  Give full access to the folder.  Also give full access to NT SERVICE\SQLAgent$<Instance Name> and NT SERVICE\MSSQL$<Instance Name> so that sql server and the agent have access to this.
4. Create share on the 'LogShip' folder on the primary server and ensure <domain>\<secondary server name> has full read/write access.
5. Right click on the database through mssms, left click on "properties", left click on "transaction log shipping".
6. Tick Enable this as primary database in a log shipping configuration and select backup settings.
7. In network path - this is the path where both the logs are stored. Put in the network share path for 'LogShip'.  Include an extra folder for the database.
8. Backup Folder - put in the local path for the LogShip share.  Include an extra folder for the database.
9. Set the retention period to 21 days (may vary on system)/
10. Click ok to return to the properties screen and select "Add", followed by the connection details of the secondary database instance.
11. Under the "copy files" tab enter the local path on the secondary server for the 'LogReceived' folder with an extra folder for the database.
12. Change "Delete copied files after" to the retention period days.
13. Click on "Restore Transaction Log" tab.  Select Standby Mode or No Recovery Mode.  No recovery mode will make the database offline.  Standby mode will make it read only.  If Standby used then be aware that the restore job will not run if there is a connection to the secondary database unless the "disconnect user" check box is selected in which case all users will be disconnected every time the restore runs.
14. Click on "ok" Then "ok" for the job to initialise and the database to be backed up and restored to the secondary instance.
15.  Test replication by running the 3 agent jobs in turn and checking for success.
16. Set up alert notifications on the 3 jobs to email on failure.
17. Copy logins and jobs to secondary server (see Copy users from one instance to another)