I have used 3 instances to configure log shipping and they are as follows,
The database for which I am configuring Log shipping is named as “sansu” and it is present in primary server.The following are the steps I followed,
Step 1 :Take a full backup of the DB to be log shipped in primary server(i.e sansu) and restore it in secondary server WITH STANDBY option
Step 2 :Right Click the DB to be log shipped in primary server,select Task and Ship Transaction logs option and ensure that you enable the option “enable this as a primary database in log shipping”
Step 3 :Click Backup settings and give the primary server name\tran log older(shared folder) and also mention the local hard drive in the primary server where the shared folder resides in the subsequent box
Step 4 :There are options “Delete files older than” it helps to remove the old transaction log backup files from the shared folder in the primary and “Alert if no backup occurs within” it sends an alert if a tran log backup has not happened for a stipulated time mentioned there.
Step 5 :Click the schedule option for the tran log backup and schedule as per your desire
Step 6 :As mentioned in Step 2,below the enable option select “Add” option to add the secondary server and connect to it via the connect option.
Step 7 :In the “Initialize secondary database” option choose the 3rd option “No, secondary database is initialized” because we have manually taken a full backup and restored as in Step1.If we havent done Step1,we can choose options 1 which will take a fresh full backup and restore it in secondary server or option 2 which will restore from an existing backup rather than a fresh backup.In that case you need to point the location where the backup file resides and also the name of the data and log files in the restore options.
Step 8 :Choose the Copy files tab and specify the path where the tran log is copied and put in the secondary and schedule the job and fill the delete old files option.
Step 9 :In the Restore transaction log tab choose STANDBY mode and “disconnect users in db when restoring backup, because if the users are accessing the db restoration will fail. In “Delay Restoring Backups” if it is 0 minute(s) as soon as copy job completes, restore job will start immediately without any delay. In “Alert if no restore occurs within” option if restore does not happen for a stipulated amount of time mentioned here an alert is issued. Also schedule the restore job as per your desire.
Step 10 :Choose the page as in Step2 and enable the “Use a monitor server instance” and connect to a new sql instance(DeepakTest)which will monitor the log shippings backup,copy and restore jobs and will help in troubleshooting.We can also configure the secondary server instance(Deepaksansu) as monitor.Both are correct it all depends on your requirements.Click Ok and the log shipping is configured with all 3 jobs and status will be shown as success.
If you see the below screenshot which shows the secondary db sansu which is in read only mode, further 3 jobs will created 1 in the primary server which is the backup job and other 2 in secondary server which is the copy and restore job.In the monitor server an alert job will be created to generate alerts if the copy or restore job fails for a stipulated amount of time.You can view the jobs created by navigating to SQL server agent and then beneath it you have a folder called Jobs, which displays the jobs present in that server.