Sql-server – Log shipping and new NDF file

log-shippingsql server

I have a log shipped database, and I want to create a new NDF file in another disk for moving historic tables to that NDF file. My goal is to free some space in the MDF file because the disk that the MDF resides is getting full.

I have a doubt and is: 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 yes, how can I avoid this?

Best Answer

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.

Related Question