Sql-server – Shirking of Log file on Primary Server of Transaction Log shipping

If you don't include the transaction log, then it's not really log shipping, is it?

Your full backup is restored to that point in time, and that's where it will stay until you either (a) apply a log or (b) apply a differential or another full backup. Yes, the restore does also bring over the transaction log, but only the state of the transaction log at that same point in time. I guess technically you could argue that you shipped the log once, but the point of log shipping is to keep the secondary server up to date more often than the full backups (which are typically very large in comparison to periodic log backups).

So if your intention is to do this backup / restore once a day, and you don't need to keep the copy up to date throughout the day, just do a simple backup and restore, and stop calling it log shipping.

If you want the copy to stay relatively up to date throughout the day, then yes, it will be mandatory to take periodic log backups on the primary server and restore them on the secondary server. Note that you have to weigh what's more important: keeping a copy of the data up to date, or letting users query stale data uninterrupted. If you restore a log to the secondary server every 15 minutes, you have to kick all the read-only users out every 15 minutes, because you need exclusive access to the database in order to restore the log.

You can read more information about log shipping here:

http://msdn.microsoft.com/en-us/library/ms187103(v=sql.90).aspx

if I create the NDF file in a disk in the primary server that the secondary server does not have: does the log shipping process will fail?

If logshipping paths are same on the primary and secondary servers then adding an ndf will be seamless.

If the paths are different on primary and secondary servers then

• Stop the backup, copy and restore jobs related to the database that you are adding a secondary file.
• Add the ndf file on primary using ALTER DATABASE db_name ADD FILE..
• run the backup job manually on the primary server. This will generate a T-log backup. Make sure that all the previous log backups are applied to the secondary.
• Run the copy job manually so that the T-log with above changes is shipped to secondary server.

• Manually restore the log file (from above step) using the

  RESTORE log db_name FROM      Disk='path_T-log_from_Above_Step.trn'
  with norecovery,  ---> Very Important .. !!
  MOVE 'logical_file_name_of_ndf'
  TO 'physical_path\secondary_file_name.ndf'
  

• Once above steps are successful, enable all the backup, copy and restore jobs for the database. All the subsequent backup, copy and restore jobs should be successful now.