What is log shipping?
SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually.
Issue:
When business need to add NDF (new data file) to database which is participating in log shipping.
If log shipping paths are same on the primary and secondary servers then adding an NDF will be seamless.
I will describe step by step method If the paths are different on primary and secondary servers, log backup restoration will get fail on secondary database if we don’t follow below steps
Steps to follow:
- Disable all jobs first (Log shipping jobs).
- Go to database properties on primary databaseàfilesàclick on addà
Query: ALTER DATABASE DB_name ADD FILE (NAME = Manvendra_test,
FILENAME = ‘E:\Program Files\Microsoft SQL Server\MSSQL10_50.MANVENDRA\MSSQL\DATA\Manvendra_test.ndf’,
SIZE = 1000MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
Go
- 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:
RESTORE log db_name FROM Disk=’path_T-log_from_Above_Step.trn’ with norecovery, MOVE ‘logical_file_name_of_ndf’ TO ‘physical_path\secondary_file_name.ndf’
Note: With no recovery —> Very Important…!!
- Once you manually restored the backup successfully, enable all jobs (Log shipping jobs).
