Setting up Log Shipping MSSQL 2008

 – First you need to create the backup/log shipping directory at the primary and you need to share this folder and add full permissions for the primary dataserver SQL Server AND SQL Agent service accounts and read permissions for the secondary dataserver’s SQL Server AND SQL Agent service accounts.

– You need to repeat this at the destination server giving read access to the primary server sql server service account and read/write to the secondary dataserver’s SQL Agent AND SQL Server service accounts.

– You’ll next need to create the secondary database, this obviously needs to be sized the same as the primary database and make it simple recovery mode.

– You can now fire up the log shipping wizard from the primary dataserver by ticking the log shipping radio button under properties for the database. NOTE: The wizard is a bit rubbish and should not be used to set up log shipping, but it is useful for generating the various stored procs which need to be run on the primary and secondary dataservers.

– The wizard is fairly self explanatory, You select the Primary dataserver log shipping directory as the backup folder (use network share path). Also and VERY IMPORTANT make sure that you select “No, the secondary database is initialised” in the Initialize Secondary database tab. Also select the secondary logshipping directory(configured in step 2 above) as the “destination folder for copied files” in the Copy Files tab. The final step is to select Standby mode and “disconnect users” from the Restore transaction log tab.

– Click the button “Script Configuration” to output all this information to a new query window and save it, you can then close the wizard.

– First make sure the primary database is set to Full logged mode in properties then back it up into the log shipping directory created earlier.

– Copy this dump across to the secondary server log shipping directory.

– Now from the secondary server load the database previously created from this backup. Make sure you leave this database in RESTORE WITH STANDBY mode! Place the standby file in the secondary server logshipping directory.

– On the primary server run the following commands from the script you generated earlier; sp_add_log_shipping_primary_database to add a primary database. sp_add_schedule to add a schedule for the backup job, sp_attach_schedule and sp_update_job.

– (Optional) On the monitor server, execute sp_add_log_shipping_alert_job to add the alert job.

– On the secondary server, execute the jobs from the script, sp_add_log_shipping_secondary_primary, sp_add_schedule and sp_attach_schedule for the copy and restore jobs, and finally the  sp_add_log_shipping_secondary_database followed by 2 sp_update_job procs for copy and restore

– On the primary server, execute sp_add_log_shipping_alert_job and sp_add_log_shipping_primary_secondary to add the required information about the new secondary database to the primary server and get things up and running.